cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SJoosten
Frequent Visitor

Delegation error on using a Sharepoint LookUp Column (not the ID) to filter a large dataset

Dear All,

 

In filtering (a large amount of) data-rows out of a (large) SharePoint list, I'm successfully avoiding delegation errors when using "normal" data rows, however not on the LookUp colum.

I'm combining two large data sets (20.000 rows each) for filtering, thus appended the row needed from one to the other by means of a LookUp column, and refer to it in the filter. The delegation error then occurs, and also prohibits all desired rows to come out of the filter (probably the 2000 entry limit). Is there a work-around for this?

 

Example code:

 

 

Filter(
    PN_DIF_Status,
    Status.Value = "Approved",
    DIF_x003a_DepartmentCopy.Value = "Avionics"
)

 

 

Where the Status.Value = "Approved"  does not give a delegation error, since the status column was already present in "PN_DIF_STATUS". The DIF_x003a_DepartmentCopy column is the LookUp column as used from another list.  This one gives the delegation error and prohibits the correct use of the program.

 

Kind regards,

 

Sjoerd

10 REPLIES 10
mdevaney
Super User
Super User

@SJoosten 
Can you please show the error message?  LookUp columns can be delegated so I'm not sure why this has happened.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Thanks for your reaction! I Appended a screenshot of the error the ".Value"  makes after DepartmentCopy. The = sign just gives the standard delegation warning, "The filter part of this formula might not work correctly on large data sets" . The original "Department"  column from the other list was of a choice type which does not support the use of a LookUp column, thus I made a copy (DepartmentCopy) which "mathematically" refers to that column within the same list, and used a LookUp to that column. Might that be the problem?

SJoosten_1-1599485209124.png

 

 

@SJoosten 

Yes, that is definitely your problem. DepartmentCopy must be single-line text for this to work.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

OK thanks! I'll see if I can convert the choice column to a single line of text column and then refer to that one with the lookup function in the other list

@SJoosten 
Converting from Choices to Single-line text fortunately will not cause any data-loss.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up." 

The Conversion was indeed successful without any data loss, I however still get the same error.. as if a LookUp column filtering can't be delegated.
Are there any other settings I have to take a look at?

@SJoosten 
I tried to replicate your issue but I am not having any problems.  Did you remember to Refresh the datasource in PowerApps after making changes to the SharePoint list?  Until this is done no effects of the changes will be seen in your app.

Here's what I did:

I created a new list called Departments.  The department name is found in the column 'Title' (single-line text).

 

depts.JPG

Then I created a list called DepartmentTasks.  The Title column holds some random text.  DepartmentName is a Lookup column to Departments.

depttasks.JPG

 

The DepartmentName column is setup like this is SharePoint.

 

Capture3.JPG

 

Then I was able to simply use this code to FILTER my gallery.  No delegation warning was received.

Filter(DepartmentTasks, DepartmentName.Value="Avionics")

 

 

Maybe there's something funny going on with the column you are filtering on in your department's list because it was previously another type.  Suggest you make a new single-line text column and try my method again.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

 

Thanks for your effort in recreating the problem!

A new single-line department column did unfortunately not fix the problem, It can't even find the department then when selecting that column as "in this column" in the lookup columns setting. 

Storing the department info in the "title" column of the list and referring to that one did work however. It seems that only the ID and Title column give no delegation error. Storing the info there however is not preferred 🙂

 

 

I Think I'm now struggling with the list view threshold, since I can't even make a lookup column with relationship behaviour enymore without getting "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,785)