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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Join us for the next call on June 15, 2022 at 8am PDT.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.