How to Search on SharePoint lookups columns in gallery items view?

[Anonymous]

hello all,

i'm creating a powerapp from SharePoint list that contains some lookups.

by default the search function in the app working with  text fields only, so, i'm trying to update the search formula to working with another columns type like lookups.

i scenarios that i have:

- PlanList contains text and calculated and lookups columns(products, Activities)

-i want to search on product or activity

the current formula is SortByColumns(Search(PlanList, TextSearchBox1.Text, "Title"), "Title", If(SortDescending1, Descending, Ascending))

thank you.

[Meneghino]

hi @Anonymous

Please see here:

https://powerusers.microsoft.com/t5/PowerApps-Forum/Help-creating-a-filter-based-upon-a-choice-field/m-p/23598

A similar approach should work for lookups

[Anonymous]

i tryied to fillter as you mentioned Filter(PlanList, Product.Value= TextSearchBox1.Text) but i got nothing !

what i need is to do is this  Search(PlanList, TextSearchBox1.Text,"Product") but this not working ! coze product is lookup, 

thanks,

[Meneghino · Accepted solution]

Hi again @Anonymous

I would suggest to change the approach: first use the Search function to filter the products from the products list (i.e. the list that is being looked up to by the Product column), and then filter PlanList by the products that are returned from the first filter.

Something like this:

Filter(PlanList, Product.Value in Search(ProductList, TextSearchBox1.Text, "Title").Title)

Performance of the above will be very slow if you have many items in the lists, but it will be dramatically quicker if you cache ProductList in a collection.  If ProductList has <=500 items this will be very easy, but even if up to 5,000 I have seen it works ok.

Alternatively you could split the process into two pieces: a screen where the user can search and select one (or more?) products and then navigate to a second screen where the filtered PlanList is shown. 

Please let me know how you get on.

[Anonymous]

Great ! it's worked.

can you tell me how i can cache ProductList in a collection?

thanks

[Meneghino]

Yes, just have a button anywhere with the OnSelect property set to this:

ClearCollect(CachedProductList, ProductList)

What I normally do is to have this button in a welcome screen and add ;Navigate(MyNextScreen, None) so that the user does not need to remember to cache.

Then you substitute CachedProductList for ProductList in any of the expressions.

Please let me know if this improves performance

[Anonymous]

Thank you @Meneghino

so now when i adding this to button it will caching the product list in collection (up to 500)? or after using "CachedProductList"

cause when you say "Then you substitute CachedProductList for ProductList in any of the expressions."

i understanding that the CachedProductList become an object and i can use it anywhere in the app to caching data.

thank you for continuous support!

[Meneghino · Accepted solution]

Yes, up to 500 rows (but in other posts I and others show how to do this for >500 rows)

So by subsitute I mean that instead of this:

Filter(PlanList, Product.Value in Search(ProductList, TextSearchBox1.Text, "Title").Title)

Use this:

Filter(PlanList, Product.Value in Search(CachedProductList, TextSearchBox1.Text, "Title").Title)

[Anonymous]

Great,

i'll use this technique in large size list and i hope to enhance the performance there.

thanks a lot

[CindyZ]

@Meneghino  I want to filter a gallery that has a lookup column. 

My Sharepoint list (Requests) has a lookup column in which I choose an account name "AcctName" from Sharepoint list named (Accounts). 

I have a text input control I'd like to enter my search terms in.  

Below is the simplest version I've tried to search my gallery items to but it does not work -- I've actually tried lots of way but everything is a no-go.  I'm sure I'm missing something obvious or I've used before! 

Search(Requests, TextInput1.Text, "AcctName").  AcctName is the lookup column. 

Any ideas?