cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DDucolonSBUX
Frequent Visitor

Using a Text variable in a Filter search

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@DDucolonSBUX 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

PaulD1
Super User
Super User

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.

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

@DDucolonSBUX 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

PaulD1
Super User
Super User

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.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,068)