cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RPNZ
Level: Powered On

Filter on more than one Column with large Sharepoint List

20000 Item list with basic client data table in a Sharepoint list including columns GivenName and FamilyName

 

My BrowseGallery1 Data string is 

 

SortByColumns(Filter([@CLIENTS], StartsWith(GivenName, TextSearchBox1.Text)), "GivenName", If(SortDescending1, Descending, Ascending))

 

And works well to return a search with FirstName - e.g. Morgan Smith is returned and so is Morgana Smith, but it will not return a Dave Morgan or Dave Morganson

 

How do I modify the data string to Filter by FirstName OR FamilyName and keep StartsWith

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Filter on more than one Column with large Sharepoint List

is FamilyName a separate column? If not then there really is no way to use a delegable filter on SharePoint to get match the second word in a column.  If it is a seperate column then you can use the following:

SortByColumns(Filter([@CLIENTS], StartsWith(GivenName, TextSearchBox1.Text) || StartsWith(FamilyName, TextSearchBox1.Text)), "GivenName", If(SortDescending1, Descending, Ascending))


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

3 REPLIES 3
Dual Super User
Dual Super User

Re: Filter on more than one Column with large Sharepoint List

is FamilyName a separate column? If not then there really is no way to use a delegable filter on SharePoint to get match the second word in a column.  If it is a seperate column then you can use the following:

SortByColumns(Filter([@CLIENTS], StartsWith(GivenName, TextSearchBox1.Text) || StartsWith(FamilyName, TextSearchBox1.Text)), "GivenName", If(SortDescending1, Descending, Ascending))


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Super User
Super User

Re: Filter on more than one Column with large Sharepoint List

@RPNZ 

To look for the string "Morgan" in the middle of a text string such as "Dave Morganson" you would need to use the SEARCH function.  However, the SEARCH function cannot be delegated within SharePoint.  Lack of SEARCH is one of the major limitations of using it as a datasource right now.

 

If you must use the SEARCH feature in your app the datasource must be the Common Data Source or SQL.  This is a non-starter for must people though because both have additional licensing costs.

 

I know its not the answer you were looking for but its the most direct answer I can give.

 

The workaround provided by @Pstork1  is the best way to go.  You will still only be able to SEARCH at the start of either FirstName or Last Name though.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

 

RPNZ
Level: Powered On

Re: Filter on more than one Column with large Sharepoint List

 It was a separate column so this string worked perfectly - I was close with my experiments but had the incorrect syntax

 

Thank you very much!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,372)