I am collecting some information in a SharePoint list that is likly to have about 5000 items.
In my PowerApp I am able to edit those records and then I am using a Patch function to update them.
The Patch uses a LookUp function to look for the item I want to change and and update the information.
The code works, but I know LookUp function is non-delegable in SharePoint, so is not going to look for items after the first 500 (at least that's why I understand).
Does anybody knows a workaround to be able to achieve this?
I understand collections don't have that restriction, and I can collect on start to bring all the list, and look for the item I want to change. But then, how do I look for the item to pacth in the datasource?
P.S: 1. I have to use SharePOint as my data source
2. Changing the limit to non-delegable queries from 500 is not going to change because the maximum is 2000
I have not tried this myself but somebody told me the following idea, perhaps worth a try:
Use an index search to reduce the number of rows PowerApps needs to load...
You can turn the SharePoint list columns - those you want to use for filtering - into an index column to enable an index search
(go to SP list > list settings > indexed columns > create a new index > chose column).
This kind of search does not run through a list from top to bottom (limit at 500) but instead finds the resulting rows through a different mechanism. Someone else can surely explain it in some more detail..
Curious if that would work for you!
I think you are right...lookup is delegable but the IsBlank predicate on lookUp is not, which was my case.
I managed a work around making a smaller collection with a filter based on the user (which is going to reduce the number of items to about 500) (MyBigListColl) and then I LookUp the item on the big list (MyBigList) with the with the LookUp, without the IsBlank predicate this time, so should be delegable.
I'm not sure if I'm being clear but here is the code just in case:
LookUp(MyBigListColl,Title=ApplicantNameDCV.Text And AssessorID=AssessorIDVar)),
LookUp(MyBigList,(Title=ApplicantNameDCV.Text And AssessorID=AssessorIDVar))),
I also changed the non-delegable query limit to 2000, just in case
Thanks for the help anyway!
I don't think you need to worry about the limitation at all. Powerapps executes functions from the inside out, so the Lookup() executes first, then the IsBlank() tests the result which either contains 0 or 1 item.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.
We are excited for the next Super User season.
FIll out a quick form to claim your community user group member badge today!
Features releasing from October 2020 through March 2021