Unanswered

Delegation Warning on Lookup

(0) Share

Report

Posted on by hwing23

Hi there,

I'm new to powerapps and running into a delegation warning with on the Lookup portion of my formula when trying to sort the results. To provide some context, I have 2 Galleries:

Gallery1 is connected to a "Locations" list on SharePoint with a ID and LocationName
Gallery2 is connected to a "LocationsAndContacts" list which contains an ID, LocationID and ContactID. This gallery is set to filter based on the selected item ID of Gallery1.
I have a 3rd list which is a "Contacts" list, which contains an ID and ContactName

I'm trying to get Gallery2 to Lookup the ContactName from the ContactsList and then sort Gallery2 based on the ContactName.

My Formula:

Sort(Filter(Table1, ID = Gallery1.Selected.ID), Lookup(Table2, Value(ID) = Table1_ID2, LastName))

The formula works but I receive a delegation warning on the Lookup portion.

Any suggestions on how I can rewrite the Lookup portion.

Thanks,

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (0)

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

The delegation warning is probably because you are using a function around the field being looked up. This secondary function can't be delegated. But ID should already be an integer so there is no reason to surround it with Value(). Try removing the value() function and just use ID.

Was this reply helpful? Yes No
hwing23 4 on at

Like (0)

Report

Thanks @Pstork1 for the suggestion.
I've removed the value() function but still receive the delegation warning on the Lookup function. Is there another method for handling this?

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Its not going to be possible to do it without a delegation warning. The Lookup function is creating a complex data type. And complex Data types are not delegable when sorting. Here's the chart from the documentation. Your only real choice is to duplicate the Column you are sorting on into the second list. Or do an AddColumn() to do it inline.

Was this reply helpful? Yes No
hwing23 4 on at

Like (0)

Report

Thanks for the AddColumns() suggestion. I found it resolved the delegation warning, however, I noticed it introduced performance issues with returning the results. I think you put me on the right track so will continue working through this.

Was this reply helpful? Yes No
westerdaled 636 on at

Like (0)

Report
I am also finding delegation a pain with a similar issue:

Here I want to display a value from a lookup list in a custom form field

LookUp('Request Approval',Request.Id = ThisItem.ID && !StartsWith('Request Status'.Value , "Closed")).'Decision Date'

However, I am getting delegation warnings on the first expression, even though the ID is numeric: so should be delegable and the "&&" . Request Approval list does have 5000+ items ( so over the list view threshold) and does have an index on the Request Column. Would the AddColumns function make a difference here?

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report
I would re-write this to do it as a nested Lookup. Something like this

LookUp(Filter('Request Approval',Request.Id = ThisItem.ID), !StartsWith('Request Status'.Value , "Closed")).'Decision Date'

Assuming that the Filter returns less than you Data Row limit it shouldn't matter if the other half is delegable or not.

Was this reply helpful? Yes No
westerdaled 636 on at

Like (0)

Report
Hi @Pstork1

Thanks for your reply. I think I can see where you going with your approach but I still am hitting that lovely yellow triangle. Note, I copied your statement exactly. I guess the issue is I am not able to use the lookup column in the way I am trying to use it. Do you think it is worth trying any of the steps below, excluding the last one! Please also suggest any alternatives.

Replace the Id with another unique field , say a text field and amend the lookup statement
Detach the Request Approval look up list from the master list - then repeat your statement
Delete and recreate the Request index column
Say to my client , "You remember I told you a SharePoint list aren't the equivalent of a relational database table!"

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

The function I gave you won't get rid of the delegation warning. But it is a warning, not an error. The code I gave you will retrieve all the records where the ID equals the Request id. Since that should be less than 2,000 records the delegation warning won't matter. The code I gave you essentially does your second bullet.

But I think I might see the issue. Is the Request index column a lookup column?

Was this reply helpful? Yes No
westerdaled 636 on at

Like (0)

Report

@Pstork1 wrote:

But I think I might see the issue. Is the Request index column a lookup column?
Yes it is . The request Id I am looking for must be way outside the delegation threshold in the Power App.. Are you saying if this was a simply integer column it would be delegable but because it is a complex Request.Id lookup column on a list of 5000+ items, I am doomed to fail?

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Yes, a simple integer column would be delegable. It should also be delegable if you save the request ID you want before using it in the function. Thats assuming its not something like ThisItem.RequestID in a gallery or something.

Was this reply helpful? Yes No