Checking for Entire String Match in SharePoint Multi Choice Column

[pp365]

Hi all,

I have a SharePoint list which contains a Multi-Select Choice Column.

In a Canvas Power App I want to filter a Gallery when a string, entered by a user in a Text Box on the page, precisely/fully matches one of the potential choices in the source data.

For example:

Choices in the SharePoint record, with the column named 'Colours': Black, Blue, Green.

When a user enters the phrase 'Black', the record is returned. When the user enters the phrase 'Blac' the record is not returned as it's not a complete match of any of the choices.

I have wasted a deal of time on this so far attempting to use IsMatch, but that will not accept a 'dynamic' input (such as a text input, variable, etc). So, this worked in testing but does not work for my use case as I need the input value to be dynamic:

Filter(SharePointListName, IsMatch(Concat(Colours,Value), "Black",MatchOptions.Contains))

I then succeeded in using a Filter but this will return the record when part of the string matches, not the entire string.

Filter(SharePointListName, TextInput1.Text in Concat(Colours,Value))

As always, any help very much appreciated. Thank you!

[Amik · Accepted solution]

@pp365 - like this you mean?

Filter(
    SharePointListName,
    "Black" exactin Colours.Value
)

[pp365]

Hi @Amik , many thanks for your help, that solved it!

[cwebb365]

Don’t forget to keep delegation in mind exactin cannot be delegated so you’re limited to your first 500 up to 2000 records in a list. If you can prefilter that using with() filter down with a delegable query first then search you can be good but just keep that in mind if the list will grow large 

[pp365]

Many thanks @cwebb365  for that very helpful pointer. I was planning to bring the data in via a single, prefiltering call, to be held in a collection in the App as the source data will be used in a few places during the App journey, but specific to the relevant user. I will then use 'Exactin' on the Collection.

For the benefit of others reading this thread, this resource is useful on the subject of Delegation: Understand delegation in a canvas app - Power Apps | Microsoft Learn.

Refer (on the link above) to the data source you plan to use.

[Amik]

@pp365 - the scenario you just described does not necessarily warrant a Collection if you can also leverage Named Formulas.

For example, on the Formulas property under the App object, create a named formula which will create an output you can be certain will return a volume of rows less than your delegation limit, e.g.:

named_fx_myData = Filter(
    SharePointListName,
    Column1 = "Text1",
    Column2 = "Text2"
);

And then use:

Filter(
    named_fx_myData,
    "Black" exactin Colours.Value
)

The obvious benefit is that unlike a Collection, the formula is always up to date.

Further reading:

https://www.microsoft.com/en-us/power-platform/blog/power-apps/power-fx-introducing-named-formulas/ 

[pp365]

Hi @Amik , great suggestion, thank you. I'll read up on the article you've suggested. It appears Named Formulas are in GA so it should be feasible for me to use.

Many thanks!