cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver II
Resolver II

Work around for non-delegable limit for LookUp in SharePoint

Hi all,

 

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?

 

Thanks

 

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

 

4 REPLIES 4
Anonymous
Not applicable

Hello,

 

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 believe lookup is delegatable in SharePoint. The limit shouldn’t apply. A substitute for lookup is first(filter(.

Hi @Drrickryp

 

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: 

 

If(IsBlank(
   LookUp(MyBigListColl,Title=ApplicantNameDCV.Text And AssessorID=AssessorIDVar)),
Defaults(MyBigList),
LookUp(MyBigList,(Title=ApplicantNameDCV.Text And AssessorID=AssessorIDVar))),


{Title:ApplicantNameDCV.Text},{JobTitle:JobTitleDCV.Text},{Email:EmailDCV.Text},{Item:Item},{Section:Section},{AssessorName:UserVar},{AssessorID:AssessorIDVar},{Notes:Notes},{Score:Score},{ApplicationID:ApplicationIDDCV.Text}))

 

I also changed the non-delegable query limit to 2000, just in case

 

Thanks for the help anyway!

Hi K,

 

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. 

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (35,707)