Using gallery to search through 5000+ items in share point list

[alex5p]

I have a list that contains over 5000+ items in a sharepoint list. I currently have it setup to use a gallery that will search through the sharepoint list for a user to edit one of the items in the sharepoint list. The issue now is that when a users start using this application I created they will only be able to find 1000 items within the gallery search. Is there any workarounds to this issue? I have seen that gallery only supports 2000 items to be found. Not sure if any one can think of a work around that lets the user be able to search for any item in the sharepoint list. 

[iAm_ManCat]

If the query you write to search is delegable, then the 2000 item limit does not apply. Only if your filter/lookup has a non-delegable function or method will you be limited to 2000 rows. Non-delegable queries will be highlighted by a blue-squiggly-line.

Could you share the formula you are using for your Items property of the Gallery so that we can take a look at why it might not be delegable?

Thanks,

ManCat

[v-xida-msft]

Hi @alex5p ,

Could you please share more details about the formula you used within the Items property of the Gallery?

If the formula typed within the Items property of the Gallery is delegable, then you could search any item through your whole SP list, and not be restricted to the 2000 limit (delegate the data process to your SP list data source).

If the formula you typed within the Items property of the Gallery is non-delegable, you could only process data locally in your app. In default, you could only process 500 records locally, you could change this limit, so you could process 2000 records locally.

More details about the Delegation in PowerApps, please check the following article:

Delegation

On your side, please consider type delegable formula within the Items property of the Gallery, then you would not be restricted to the 2000 records limit.

Best regards,

[alex5p]

@iAm_ManCat , @v-xida-msft 

Search(
          ReferralSystem,
                
          TextInput1.Text,
          "Title",
          "DateofReferralText",
          "CustomerPhoneNumber",
          "CustomerName",
          "BackupName",
          "ReferredBy",
          "AreaOfInterest",
          "Department",
          "ReferredToName"
          

           )

Right now i have a a search done through a text input. The user is given the option to search using 10 different fields. I do get that delgation blue line under my search options. Maybe I can adjust the search?

[iAm_ManCat · Accepted solution]

Hi @alex5p 

Yes, Search is not a delegable function, so you should be able to change this to Filter:

Filter(
       ReferralSystem,
       StartsWith("Title", TextInput1.Text)
       || 
       StartsWith("DateofReferralText", TextInput1.Text)
       ||
       StartsWith("CustomerPhoneNumber", TextInput1.Text)
       ||
       StartsWith("CustomerName", TextInput1.Text)
       ||
       StartsWith("BackupName", TextInput1.Text)
       ||
       StartsWith("ReferredBy", TextInput1.Text)
       ||
       StartsWith("AreaOfInterest", TextInput1.Text)
       ||
       StartsWith("Department", TextInput1.Text)
       ||
       StartsWith("ReferredToName", TextInput1.Text)
)

The documentation for delegation states Search is delegable however, we can see from the datasource documentation that Search is not listed as a delegable function for SharePoint

Could you try that and let me know whether that works for you?

Thanks,

Sancho

[alex5p]

@iAm_ManCat 

Thank you that worked! Just a heads up it needed single quotes ' instead of double quote. It was looking for that word instead of the value.

[iAm_ManCat]

Thanks for sharing about the quotes!

I think you can probably get rid of the quotes altogether if you don't need explicit column names - single quotes are usually used for 'display name' whereas double quotes are usually "staticColumnName" - I thought yours needed static(original) names based on your initial formula  😄