Nested lookup - delegation

(2) Share

Report

Posted on by Tono_Analyst2

127

Hi All,

I have 3 SharePoint lists that are related via their IDs, as an example I have created a sample dataset below.

Based on an item from SP List 3 (in a gallery), I would like find the corresponding company from SP List 1.

A nested lookup, such as the one below, results in a delegation warning.

Lookup('SP List 1', ID1 =Lookup('SP List 2', ID2 = ThisItem.ID2).ID1).Company

Is it viable to breakup the lookup into two parts to avoid the nested lookup? For example, store the inner lookup result as a variable and then use that variable within the outer lookup.

eg:

Step 1: Updatecontext({var_LookUpResult:Lookup('SP List 2', ID2 = ThisItem.ID2).ID1})

Step 2:Lookup('SP List 1', ID1 =var_LookUpResult).Company

Based on my testing in Power Apps, this appears to work, but I am wondering if I have missed any potential issues/complications that could arise from doing this loophole.

Thank you in advance for your help.

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(4)

Report
Copy link

Link copied!
Hi @Tono_Analyst2

To get Company Info
With({_item:LookUp(SPList2, ID2=ThisItem.ID2)}, LookUp(SPList1, ID1=_item.ID1).Company)
Tono_Analyst2 127 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your response @StalinPonnusamy .

Your solution does look more elegant and I will take that onboard to implement.

Out of curiosity, would my method of storing a lookup variable for another lookup have any negative effects?

Thank you
StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Nope. This is the right way to implement.
Verified answer

StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Tono_Analyst2

I just made a quick test and here is the same for reference

With( { _item: LookUp( EventIItinerary, ID = ThisItem.SingleEvent.Id ) }, With( {_item2: Addresses}, LookUp( _item2, ID = _item.Address.Id ).Address1 ) )
SPList 1: IssueTracker (Gallery)
SPList 2: EventIItinerary
SPList 3: Address

IssueTracker has Lookup to EventIItinerary (field: SingleEvent)
EventIItinerary has a lookup to Address
In this example retrieving Address1 from SPList 3

Let me know if any questions
Tono_Analyst2 127 on at

Like
a
(0)

Report
Copy link

Link copied!

I found that the delegation warning appeared depending on how I set up the with statement:

This Caused Delegation warning (in blue):
With({record: Lookup('SP List 2', ID2 = ThisItem.ID2)}, Lookup('SP List 1', ID1 =record.ID1).Company)

This Did not cause delegation warning:
With({record: Lookup('SP List 2', ID2 = ThisItem.ID2).ID1}, Lookup('SP List 1', ID1 =record).Company)

I am not sure if the second set up is still not delegable, but power apps is just not detecting it...
StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
I'm sure you noticed, I'm using 2 with statement to avoid delegation
With( { _item: LookUp( EventIItinerary, ID = ThisItem.SingleEvent.Id ) }, With( {_item2: Addresses}, LookUp( _item2, ID = _item.Address.Id ).Address1 ) )
Tono_Analyst2 127 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, I did notice and that appears to work for me - thank you for your help.

I noticed that if I specified the field within the with statement, it also did not cause a delegation warning
With({record: Lookup('SP List 2', ID2 = ThisItem.ID2).ID1}, Lookup('SP List 1', ID1 =record).Company)