Answered

Using a Text variable in a Filter search

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I might not know how to ask the question or there is no answer out there so let me see if I can explain.

I have a list in SharePoint that has a people field (Owner). My users want to filter the list I give them in the PowerApps canvas app by an indeterminate set of user names.

So I tried to create a context text variable like this:

UpdateContext({OwnersToSearch: " Owner.DisplayName = """ &
 Left(Concat(OwnerSearchDataCard.SelectedItems, DisplayName & """ ||
 Owner.DisplayName = """), 
 Len(Concat(OwnerSearchDataCard.SelectedItems, DisplayName & " ||
 Owner.DisplayName = ")) - 22) })

With three selected Owners in the default combo box I get this output when I show the "OwnersToSearch" variable in a label.

Owner.DisplayName = "User1 Smith" || Owner.DisplayName = "User2 Smyth" || Owner.DisplayName = "User3 Jones"

And I have tried to use the variable OnwersToSearch in my very simple collection filter:

ClearCollect(collectionFiltered, Filter(collectionAllActive,OwnersToSearch)

as you can see I am filtering a collection of All Active rows in SharePoint to just get the Active rows with one of the three users as the Owner.

Since I do not know how many User Names will be selected and PowerApps doesn't have an index into combo box table of results. I figured the Concat with Len and Left should get me the user names and then inserting the Quoting and || (or) separator I thought that would solve my problem.

Buy the filter returns 0 records from the All Active list when I am sure that there are at least 5 per user which I verified in SharePoint.

Is there a way to use my variable or another variable in a Filter refinement? Or is there another creative solution to my need?

Thanks,

Dave

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (2)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

Please consider changing your Formula to the following:

ClearCollect(collectionFiltered, Filter(collectionAllActive,Owner.DisplayName in OwnerSarchDataCard.SelectedItems.DisplayName) )

That should give you what you are looking for.

I hope this is helpful for you.

Was this reply helpful? Yes No
Verified answer

PaulD1 2,914 on at

Like
a
(0)

Report
Copy link

Link copied!
To make this a delegable search you could attempt something like the following:

Filter( collectionAllActive, item_no = First(OwnerSearchDataCard.DisplayName) || item_no = Coalesce(Last(FirstN(OwnerSearchDataCard.SelectedItems.DisplayName,2)),"zzz") || item_no = Coalesce(Last(FirstN(OwnerSearchDataCard.SelectedItems.DisplayName,3)),"zzz") || item_no = Coalesce(Last(FirstN(OwnerSearchDataCard.SelectedItems.DisplayName,4)),"zzz") ... )

The issue is that you have an indeterminate number of selections to test against. You will need one 'Or' condition for each selection. It does not matter if you have fewer selections that 'Or' conditions as you can use coalesce to replace a 'non-existent selection' with a value you would never expect to find in your data (e.g. zzz).

You could continue the pattern up to the maximum number of likely suggests (say 10) and then put a formula in the OnChange of the OwnerSearchDataCard combo to check if there are more than 10 selections and if so, use Notify to put a message on the screen informing the user they have selected more options that the App can handle.

Was this reply helpful? Yes No