Solved: Re: Regex working for integers but not strings

Dusty_Gregs · ‎07-22-2021

Hello,

I had some really great help in https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-Gallery-based-on-partial-match/m-p/88... on using variables and filtering a gallery based on a partial match. I have made progress building the SharePoint lists to encompass the data I need and have run into a challenge that the initial solution is working for integers but will not work for strings or a combination of letters, numbers, and special characters.

I have modified the original formula slightly to include more in the regex for MatchAll.

Filter(
    DataSource2,
    TrimEnds(
        Substitute(
            Substitute(
                Substitute(
                    Substitute(
                        Substitute(
                            Substitute(
                                Substitute(
                                    Substitute(
                                    DataSource2[@Error],
                                    "XX/XX/XXXX",
                                    ""
                                ),
                                "XXXX ",
                                ""
                            ),
                            "XXXX",
                            ""
                            ),
                            "XXX.XXX",
                            ""
                        ),
                        "XX:XX",
                        ""
                    ),
                    ":",
                    ""
                ),
                "XX",
                ""
            ),
            ".",
            ""
        )
    ) in ForAll(
        DataSource1,
        With(
            {
                thevalue: Concat(
                    MatchAll(
                        Error.Text,
                        "[a-zA-Z= ,\[\]\-_'/]+"
                    ).FullMatch,
                    FullMatch
                )
            },
            TrimEnds(
                If(
                    Len(
                        Match(
                            thevalue,
                            "(?<= Item ).*?(?=must)"
                        ).FullMatch
                    ) > 0,
                    Replace(
                        thevalue,
                        Match(
                            thevalue,
                            "(?<=Item).*?(?=must)"
                        ).StartMatch,
                        Len(
                            Match(
                                thevalue,
                                "(?<= Item ).*?(?=must)"
                            ).FullMatch
                        ),
                        ""
                    ),
                    thevalue
                )
            )
        )
    )
)

I tried adding 0-9 there as well but that breaks it. Likewise, if I remove a-z or A-Z, it no longer works for the integer ones that are currently working. I'm wondering if the (?=must) is causing the challenge but have gone through these forums and many google searches and cannot seem to find a way to make it work.

Here are some examples of what is working and what is not.

ClearCollect(
   DataSource1,
   {ID:1,Error:{Text:"Exception in TransmitOrder: Item only valid for sale from 11/23/2018 to 03/27/2021 per Item restriction."}},
   {ID:2,Error:{Text:"Exception in TransmitOrder: Item only valid for sale from 11/23/2018 to 03/30/2021 per Item restriction."}},
   {ID:3,Error:{Text:"Exception in TransmitOrder: Guest failed age limit check: Age = 1.764, Min = 11.000, Max = 99.000"}},
   {ID:4,Error:{Text:"Exception in TransmitOrder: Guest failed age limit check: Age = 10.082, Min = 8.000, Max = 9.999"}},
   {ID:5,Error:{Text:"Exception in TransmitOrder: Item can only be sold between 00:00 and 11:45 per Item restriction"}},
   {ID:6,Error:{Text:"Exception in TransmitOrder: Item OMNI000014COMBOTIMES1030 must have between 1 and 1 modifiers, and 0 are specified"}}
   {ID:7,Error:{Text:"Exception in TransmitOrder: Item cannot be sold on Tuesday per Item restriction."}}
   {ID:8,Error:{Text:"Exception in TransmitOrder: Failed to create guest via season pass for ticket[1905097]"}}
);
ClearCollect(
    DataSource2,
    {Error:"Exception in TransmitOrder: Item only valid for sale from XX/XX/XXXX to XX/XX/XXXX per Item restriction."},
    {Error:"Exception in TransmitOrder: Guest failed age limit check: Age = XXX.XXX, Min = XXX.XXX, Max = XXX.XXX"},
    {Error:"Exception in TransmitOrder: Item can only be sold between XX:XX and XX:XX per Item restriction."},
    {Error:"Exception in TransmitOrder: Item XXXX must have between XX and XX modifiers, and XX are specified"}
    {Error:"Exception in TransmitOrder: Item cannot be sold on XXXX per Item restriction."}
    {Error:"Exception in TransmitOrder: Failed to create guest via season pass for ticket[XXXX]"}
)

Assuming the information from the data sources above, for DataSource1, ID's 1, 2, 6, and 7 do not work. For ID 7, if I change the word Tuesday which is the variable to a number only such as 1234, it will then work. It seems to me that the dates with slashes in them and any case where there is a string, it does not work.

ID's 3, 4, 5, and 8 all work perfectly.

Any help in trying to figure out how to adjust my formula to work for strings as well would be greatly appreciated.

Thank you!

v-bofeng-msft · ‎07-23-2021

Hi @Dusty_Gregs :

I re-organized the logic and used the similarity algorithm(greater than 75%) to get the most similar results.

Distinct(
        ForAll(
            DataSource1,
            Last(
                Filter(
                    SortByColumns(
                        ForAll(
                            RenameColumns(
                                DataSource2,
                                "Error",
                                "TheError"
                            ),
                            {
                                Rate: Sum(
                                    ForAll(
                                        Split(
                                            Error.Text,
                                            " "
                                        ),
                                        {
                                            CountValue: If(
                                                Result in Split(
                                                    TheError,
                                                    " "
                                                ),
                                                1,
                                                0
                                            )
                                        }
                                    ),
                                    CountValue
                                ) / CountRows(
                                    (Split(
                                        Error.Text,
                                        " "
                                    ))
                                ),
                                TheError: TheError
                            }
                        ),
                        "Rate"
                    ),
                    Rate >= 0.75
                )
            )
        ),
        TheError
    )

Best Regards,

Bof

View solution in original post

v-bofeng-msft · ‎07-23-2021

Hi @Dusty_Gregs :

I re-organized the logic and used the similarity algorithm(greater than 75%) to get the most similar results.

Distinct(
        ForAll(
            DataSource1,
            Last(
                Filter(
                    SortByColumns(
                        ForAll(
                            RenameColumns(
                                DataSource2,
                                "Error",
                                "TheError"
                            ),
                            {
                                Rate: Sum(
                                    ForAll(
                                        Split(
                                            Error.Text,
                                            " "
                                        ),
                                        {
                                            CountValue: If(
                                                Result in Split(
                                                    TheError,
                                                    " "
                                                ),
                                                1,
                                                0
                                            )
                                        }
                                    ),
                                    CountValue
                                ) / CountRows(
                                    (Split(
                                        Error.Text,
                                        " "
                                    ))
                                ),
                                TheError: TheError
                            }
                        ),
                        "Rate"
                    ),
                    Rate >= 0.75
                )
            )
        ),
        TheError
    )

Best Regards,

Bof

Dusty_Gregs · ‎07-23-2021

That worked beautifully! I truly appreciate the help once again!

Dusty_Gregs · ‎07-23-2021

Hi @v-bofeng-msft,

I may have spoken too soon. While this new formula does work like I need it to, I have noticed a significant increase in latency that is causing large delays in loading and scrolling through the gallery. I never experienced this latency issue with the first formula you helped me create. Do you know of a way to help with latency for the new formula or make the previous formula work for strings as well?

Thank you!

Dusty_Gregs · ‎08-05-2021

Hi @v-bofeng-msft,

Sorry to bother again, but the new formula causes my app to load incredibly slow as I try to scroll through the gallery. Do you know of an alternate way to accomplish this that would let my app stay efficient?

I did try playing around with different values in the Rate >= 0.75 but both very high and low rates still caused latency. I also tried switching back to the previous formula and it started working with normal speeds again but the examples with strings stopped working as originally stated.

I appreciate the assistance.

Thank you!

v-bofeng-msft · ‎08-05-2021

Hi @Dusty_Gregs :

Sorry for not replying to you in time, because I still haven't found a solution that is both efficient and scientific.

The first solution I provided to you can only solve specific problems in a specific condition, so once your data source\matching rules\matched objects change the slightest, the formula will stop working, as you have encountered.In this case, the robustness of the second solution I provided is relatively much better.

Regarding the performance issue you mentioned, I suggest that you can create a collection and save the results you need to this collection when the app is loaded. Then use this collection as the data source of your gallery. This should work in to some extent to improve the performance of this gallery.

Best Regards,

Bof

Dusty_Gregs · ‎08-06-2021

Hi @v-bofeng-msft,

Definitely no need to apologize. I appreciate the help! I wasn't sure since I had accepted a solution if the notification had sent that I replied again.

I tried creating collections on app load but still had the same latency issues. I had a thought as a hopeful workaround but I'm not as familiar with Power Automate to try and translate what you wrote as the solution in this post. Considering the following collections:

ClearCollect(
   DataSource1,
   {ID:1,Error:{Text:"Exception in TransmitOrder: Item only valid for sale from 11/23/2018 to 03/27/2021 per Item restriction."}},
   {ID:2,Error:{Text:"Exception in TransmitOrder: Item only valid for sale from 11/23/2018 to 03/30/2021 per Item restriction."}},
   {ID:3,Error:{Text:"Exception in TransmitOrder: Guest failed age limit check: Age = 1.764, Min = 11.000, Max = 99.000"}},
   {ID:4,Error:{Text:"Exception in TransmitOrder: Guest failed age limit check: Age = 10.082, Min = 8.000, Max = 9.999"}},
   {ID:5,Error:{Text:"Exception in TransmitOrder: Item can only be sold between 00:00 and 11:45 per Item restriction"}},
   {ID:6,Error:{Text:"Exception in TransmitOrder: Item OMNI000014COMBOTIMES1030 must have between 1 and 1 modifiers, and 0 are specified"}}
   {ID:7,Error:{Text:"Exception in TransmitOrder: Item cannot be sold on Tuesday per Item restriction."}}
   {ID:8,Error:{Text:"Exception in TransmitOrder: Failed to create guest via season pass for ticket[1905097]"}}
);
ClearCollect(
    DataSource2,
    {Error:"Exception in TransmitOrder: Item only valid for sale from XX/XX/XXXX to XX/XX/XXXX per Item restriction."},
    {Error:"Exception in TransmitOrder: Guest failed age limit check: Age = XXX.XXX, Min = XXX.XXX, Max = XXX.XXX"},
    {Error:"Exception in TransmitOrder: Item can only be sold between XX:XX and XX:XX per Item restriction."},
    {Error:"Exception in TransmitOrder: Item XXXX must have between XX and XX modifiers, and XX are specified"}
    {Error:"Exception in TransmitOrder: Item cannot be sold on XXXX per Item restriction."}
    {Error:"Exception in TransmitOrder: Failed to create guest via season pass for ticket[XXXX]"}
)

DataSource1 is being written into a SharePoint list via Power Automate at the moment. It writes a new line every time I receive an email based on certain filters. I already have a Compose step to get the text mentioned above. I thought it would be helpful to incorporate a similar formula that you wrote for Power Apps into Power Automate so the check happens in the automation and not in the app. Each item from DataSource2 has a unique ID column that I was hoping I could pull and write into a new column for DataSource1. That way in the app, I could just use a simple Lookup formula that if ID for DataSource1 = 14, lookup the same item from DataSource2 with an ID of 14.

Currently what I would think would be needed for Power Automate steps would be the trigger email, my Compose step to get the text needed for DataSource1, SharePoint Get Items to pull the DataSource2 items, and then another compose that would be the formula to get the ID of the corresponding item from DataSource2 based on what was filtered from DataSource1 in the Flow. I would then use that compose to write into the SharePoint list for DataSource1 that could be referenced via Lookup in the app.

Please let me know if I should direct my question to the Power Automate specific forums or if you might be able to assist with this. I've used some expressions but something this complicated based on what you wrote is a bit out of my grasp.

Thank you!

v-bofeng-msft · ‎08-08-2021

Hi @Dusty_Gregs :

The syntax and functions of Power Automate are different from those of PowerApps. If the formulas in PowerApps are directly applied in Power Automate, it may cause errors. I suggest you can go to the Power Automate forum for help.

Best Regards,

Bof

Dusty_Gregs · ‎08-09-2021

Thanks @v-bofeng-msft! I truly appreciate all the help in figuring this out. I opened https://powerusers.microsoft.com/t5/Building-Flows/Lookup-partial-match-based-on-variables/m-p/12222...to see if we can accomplish the same thing we Automate.

Dusty_Gregs · ‎09-26-2021

Hi @v-bofeng-msft,

I have not had luck unfortunately with Power Automate and this functionality. I have found however that if I setup the same formula you provided on a button and patched a Unique ID number to a collection, my app runs much faster (normal speed) while using it and only has the latency issues when I press the button to Patch.

I am currently using the following which seems like it's lengthy for my new use case and I was wondering if you could help me think of a way to simply check the record on DataSource1 versus Datasource2 without using the ForAll to go through all the gallery items (since it will be a 1 for 1 now). I am hoping that just checking the 1 value for the best match from DataSource2 will help my latency even more. The below formula patches an ID column from DataSource2 into a new column called SolutionID in DataSource1 correctly. I am just hoping that there is an easier solution as it currently takes 2-3 minutes to patch the 1 value.

If(
    IsBlank(TrackingGallery.Selected.SolutionID), 
    Patch(
        DataSource1,
        ThisItem,
        {
        SolutionID: Value(
            LookUp(
                Netop_Solutions,
                Unique = First(
                    Distinct(
                        ForAll(
                             DataSource1,
                                 Last(
                                    Filter(
                                        SortByColumns(
                                                ForAll(
                                                    RenameColumns(
                                                    DataSource2,
                                                    "Error",
                                                    "TheError"
                                                    ),
                                            {
                                                Rate: Sum(
                                                    ForAll(
                                                        Split(
                                                        Error.Text,
                                                        " "
                                                        ),
                                            {
                                                CountValue: If(
                                                    Result in Split(
                                                    TheError,
                                                    " "
                                                ),
                                                1,
                                                0
                                                )
                                            }
                                            ),
                                        CountValue
                                        ) / CountRows(
                                                (Split(
                                                Error.Text,
                                                " "
                                            )
                                        )
                                    ),
                                TheError: TheError
                            }
                        ),
                        "Rate"
                    ),
                    Rate >= 0.75
                )
            )
        ),
        TheError
    )
                ).Result,
                Title)
        )
    }
),
false
)

Please let me know if you have any questions and thanks again for all your help and time in this!