cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Anonymous
Not applicable

Re: Work around for non-delegable limit for LookUp in SharePoint

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!

 

Highlighted
Super User
Super User

Re: Work around for non-delegable limit for LookUp in SharePoint

I believe lookup is delegatable in SharePoint. The limit shouldn’t apply. A substitute for lookup is first(filter(.

Highlighted
Resolver II
Resolver II

Re: Work around for non-delegable limit for LookUp in SharePoint

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!

Highlighted
Super User
Super User

Re: Work around for non-delegable limit for LookUp in SharePoint

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,655)