cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
C_Crow2692
New Member

Search Across Multiple Columns in Gallery

I've searched everywhere for this answer and have tried various different options for coding the search. None seem to work exactly how I want them to.

 

I have a gallery that shows parts within our stores. I want the text search box (StoresSearch.Text) to search across the columns "Title", "ItemSubDescription" and "PartNumber". 

 

I have tried doing it via Search with the below formula;

SortByColumns(Search(Filter('Materials Catalogue', StoresItem = 1),StoresSearch.Text,"Title","ItemSubDescription","PartNumber"),"Title",If(SortDescending1,Descending,Ascending))

 

And also via Filter with the below;

SortByColumns(Filter('Materials Catalogue', StoresItem = 1, StoresSearch.Text in Title || StoresSearch.Text in ItemSubDescription || StoresSearch.Text in PartNumber),"Title",If(SortDescending1,Descending,Ascending))

 

The problem I have with both of these options is that the user has to be very accurate with what they're searching (i.e. enter it exactly as it shows and in the same word order) and pretty much know in advance how the item is listed.

 

As an example, the attached screenshot shows the search term 'hose', which is fine as it brings up items with hose in the Title or ItemSubDescription. However, if I was to type 'hose clip' (like the second screenshot). It now only brings up items that follow this exact search term (including the spaces). I want it to pull through all items that have these two words at any point within the columns listed originally. So looking at the screenshots, this should also pull in the "WL Hose Jubilee Clip".

 

I cannot figure this one out..!!  

4 REPLIES 4
leyburn19
Memorable Member
Memorable Member

Try this

 

Filter(Sort('Materials Catalogue',Title, Ascending), StoresSearch.Text in Title || StoresSearch.Text in ItemSubDescription || StoresSearch.Text in PartNumber)

 

Some notes:

 

1. The 'in' is not deleagable so if you have 0ver 500 records (can be 2000 in setting) it will not find anything outside that range

2. The in operator automatically removes and fields if the are blank so you will only ever see the list with those records.  Using in across columns means it removes blanks base first,  then second then third .   Using StartsWith does not do this and is deleagable

3. If the gallery is a collection then using startswith has the same problem with blanks (it is not known why)

 

So you couls also try

 

Filter(Sort('Materials Catalogue',Title, Ascending), StartsWith(Title ,StoresSearch.Text) || StartsWith(ItemSubDescription ,StoresSearch.Text) || StartsWith(PartNumber,StoresSearch.Text))

 

Thanks for the reply

 

So just a bit of background on the gallery;

It's driven from a SharePoint list currently and we're confident that the items will never go over 2000, so I have set the delegation limit to 2000 in settings which should cover this requirement. 

 

I've tried both the codes and have found the following;

With the below, it behaves in the same way as the previous. So using the example I gave, it still only pulls through the 2 results when I enter "Hose Clip".

Filter(Sort('Materials Catalogue',Title, Ascending), StoresSearch.Text in Title || StoresSearch.Text in ItemSubDescription || StoresSearch.Text in PartNumber)

 

With the below, the problem I find is that if I was to just search "Clip", because nothing starts with Clip, it brings up 0 results.

Filter(Sort('Materials Catalogue',Title, Ascending), StartsWith(Title ,StoresSearch.Text) || StartsWith(ItemSubDescription ,StoresSearch.Text) || StartsWith(PartNumber,StoresSearch.Text))

You need a wild card solution.  I don't believe one exists yet.  In is the closest you can get

Thanks

I believe you're right. I actually stumbled across the thought of a wildcard solution when using a separate system at work today. I've now spent my evening reading through several threads on workarounds for wildcards but as you say, it doesn't appear to exist.

 

Real shame as this would make the searching on a gallery really powerful.

 

Thanks for your suggestions. Appreciated. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,571)