cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

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
Super User III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (5,845)