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

Display Gallery Items using Multiple Search Fields

I have a gallery where a user can enter values in any combination of 3 search fields (the user wants the search to be 'OR' based):

 

Name

Department

City

 

If 'John' was entered in Name, 'Accounting' entered in Department, and nothing entered in City, the Gallery should return all sharepoint list records that contain Name=John OR Department=Accounting. Since City is blank (no value was entered), there is no filter done on City.

 

Do I have to have separate IF statements for each of the search combinations (to check for IsBlank) or is there a more efficient algorithm?

 

Name   Dept    City

Yes        Blank   Blank

Yes        Yes      Blank

Yes        Yes      Yes

Blank     Yes      Blank

Blank     Yes      Yes

Blank     Blank   Yes

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @luvagoldenk9 ,

 

If so, change the parallel relation of the criteria to Or.

Filter('SP list',
If(IsBlank(searchbox1.Text),true,searchbox1.Text in Name) ||
If(IsBlank(searchbox2.Text),true,searchbox2.Text in Dept) ||
If(IsBlank(searchbox3.Text),true,searchbox3.Text in City)
)

Hope this helps.

Sik

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Hi @luvagoldenk9 ,

 

The following filter formula is more efficient, but with a delegation issue. If your SharePoint list has fewer than 2000 records, it works fine.

Filter('SP list',
If(IsBlank(searchbox1.Text),true,Name=searchbox1.Text),
If(IsBlank(searchbox2.Text),true,Dept=searchbox2.Text),
If(IsBlank(searchbox3.Text),true,City=searchbox3.Text)
)

If the list has more than 2000 records, here is a workaround that collect all list records to local collection. Due to the Delegation Limit , we need to collect multiple times, and each time can only save 2000 pieces of data.

ClearCollect(col1, Filter('SP list', indexcolumn<=2000)); 
ClearCollect(col2, Filter('SP list', indexcolumn>2000&&indexcolumn<=4000)); 
ClearCollect(colAll,col1,col2) 

 Note: The indexcolumn can't be ID column since SharePoint only supports the equal ('=') operation for delegation on an ID column. It must be a custom Number column.

Hope this helps.

Sik 

Highlighted

Hi Sik!

 

Thank you for the helpful reply. The share point list will only have max 100 rows so no delegation issues. The solution works if search criteria is entered into only one field like Name only. Plus I changed your suggestion from 'Name=searchbox1.Text' to 'searchbox1.Text in Name'.

 

However the solution did not work if criteria was entered in two of the search fields like Name and Department.

 

If a user enters 'John' in the Name field and 'Accounting' in the Department field, the user wants an OR condition applied. The filter should return all records with 'John' in the Name OR 'Accounting' in the Department. Thoughts?

Highlighted

Hi @luvagoldenk9 ,

 

If so, change the parallel relation of the criteria to Or.

Filter('SP list',
If(IsBlank(searchbox1.Text),true,searchbox1.Text in Name) ||
If(IsBlank(searchbox2.Text),true,searchbox2.Text in Dept) ||
If(IsBlank(searchbox3.Text),true,searchbox3.Text in City)
)

Hope this helps.

Sik

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 (8,619)