Checking for Entire String Match in SharePoint Multi Choice Column

(1) Share

Report

Posted on by pp365

439

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Ami K 15,679 Super User 2024 Season 1 on at

Like (2)

Report
@pp365 - like this you mean?

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

Was this reply helpful? Yes No
pp365 439 on at

Like (1)

Report

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

Was this reply helpful? Yes No
cwebb365 3,294 Most Valuable Professional on at

Like (2)

Report

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

Was this reply helpful? Yes No
pp365 439 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (2)

Report
@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/

Was this reply helpful? Yes No
pp365 439 on at

Like (1)

Report

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!

Was this reply helpful? Yes No