cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tono_Analyst2
Helper I
Helper I

Nested lookup - delegation

Hi All,

 

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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Super User
Super User

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

 

StalinPonnusamy_0-1633749231670.png

StalinPonnusamy_1-1633749251526.png

StalinPonnusamy_2-1633749269407.png

 

Let me know if any questions

 

 

View solution in original post

7 REPLIES 7
StalinPonnusamy
Super User
Super User

Hi @Tono_Analyst2 

 

To get Company Info 

With({_item:LookUp(SPList2, ID2=ThisItem.ID2)},
LookUp(SPList1, ID1=_item.ID1).Company)

 

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
Super User

Nope. This is the right way to implement.

StalinPonnusamy
Super User
Super User

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

 

StalinPonnusamy_0-1633749231670.png

StalinPonnusamy_1-1633749251526.png

StalinPonnusamy_2-1633749269407.png

 

Let me know if any questions

 

 

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
Super User

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
Helper I
Helper I

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)

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,880)