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

Powerapps search filter with 50k items

Good day,

I am building a powerapps from a SharePoint list that contains 50k items. My goal is to make a searchable powerapp so that when the user searches for a name it will pull all the names matching the text. First, i was having issues with the SharePoint list since it was 50k items. I indexed the column I will b referencing in powerapps. This is the search formula i am using: 

 

Filter(AddColumns('MG Customer Database (Sends/Receives)',"present",Sum(ForAll(Split(Lower(TextSearchBox1.Text)," "),If(IsBlank(Find(Result,Lower(Receiver_x2019_sFirstName))) And IsBlank(Find(Result,Lower(Receiver_x2019_sFirstName))),0,1)),Value)),present=CountRows(Split(TextSearchBox1.Text," ")))

 

This however is only searching items in the 2,000 limit. Is there a way I can modify this formula to help me achieve what I need. You help will be very much appreciated. see my SharePoint list below.2019-07-24_14-03-36.jpg

 

2 REPLIES 2
leyburn19
Level 10

Re: Powerapps search filter with 50k items

I have a search on a gallery where the SP list is over 40k and performs brilliantly as there are no delegation issues.

 

My search is done on three dropdowns and three textinputs.  What I do is with the dropdowns have the value popolate hidden labels and with the text boxes just use them directly. Two of the textboxes are based on Numeric,  where the data entry is unique so they are searched only on their values. The Dropdowns and last text box allow for multiple column searches. My Gallery Filter is simple:

 

If(isNumeric(TextInput1.Text),

Filter(Source,ID=Value(TextInput1.Text)),

If(isNumeric(TextInput2.Text),

Filter(Source,ID2=Value(TextInput2.Text)),

Filter(Source,StartsWith(ColumnInSource,Label1.Text) And StartsWith(ColumnInSource,Label2.Text) And StartsWith(ColumnInSource,Label3.Text) And StartsWith(ColumnInSource.TextInput3.Text)))

 

So the above,  If one of the textinputs are populated wth a number,  does them first then looks at the dropdowns.

 

I make the dropdowns more efficient by having an "ALL" and the first selection and then I make the text for the label to be:  If(Dropdown?.Selected.Value="All","",Dropdown?.Selected.Value)  This makes the Label blank so the StartsWith in teh filter shows all.  It also means I can filter based on one or all of the dropdowns.  Hope that makes sense

 

Note the third textinput is used in the search with the dropdowns.  If that was a surname search,   it will find only surnames etc if the the dropdowns are all set to "ALL"

 

My search results and nearly instant,  

 

Highlighted
llisbey
Level: Powered On

Re: Powerapps search filter with 50k items

@leyburn19 thank you very much for sharing your example. I will definitely look into this and see how I can modify it to suit my needs. Will let you know of the outcome. 

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 299 members 5,797 guests
Please welcome our newest community members: