cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vverb
Level: Powered On

Delegation warning for Nested LookUp (SQL Server)

Quick question:

I'm getting a delegation warning for the following code where I have a nested LookUp function.

    If(!IsBlank(LookUp('TableA', id = globalvariable.main_id)),
        LookUp('TableA',id = LookUp('TableA',id = globalvariable.main_id, sub_id))
    )

In this scenario I have a TableA that CAN contain a reference to another record in the same table via the sub_id column. I'm trying to do a lookup for that sub-record through the main-record.

This however gives me a Delegation warning for the INNER nested LookUp. The table is a SQL Server table.

 

In a normal programming paradigm I'd just use a local temporary variable to store the result of the LookUp for the sub_id (LookUp('TableA',id = globalvariable.main_id, sub_id)) but I'd prefer to not use UpdateContext to do something similar in PowerApps for a variable of such a temporary nature.

 

Are there better ways to code such a nested LookUp? If so, please educate me.

Is the Delegation warning correct and justified? aka does it truly limit the resultset for the inner lookup even though a non-nested LookUp is supposedly completely Delegable? If so, can an employee shed some light on why this is? Because a LookUp like that should only ever return a single value or Blank(); I don't see why this could not be cached after which the outer LookUp resolves as normal, with Delegation etc.

 

Thanks in advance,

 

Vince

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Delegation warning for Nested LookUp (SQL Server)

Hi @vverb ,

Could you please share a screenshot about the Delegation warning message within your formula?

How many records do you store within your SQL Table?

 

Based on the issue that you mentioned, I have made a test, the issue is confirmed on my side. Currently, the nested LookUp formula which applied to same data source as the outer one could not be delegated within PowerApps.

As an alternative solution, you could consider save the corresponding Sub Id value into a global variable firstly, and then use the variable within your LookUp formula:

1.JPG

On your side, you should type the following formula:

 

Set(SubIdVariable, LookUp('TableA', id = globalvariable.main_id, sub_id));  /* <-- Store the Sub Id value into a variable firstly */
If(
!IsBlank(LookUp('TableA', id = globalvariable.main_id)), LookUp('TableA',id = SubIdVariable) )

 

In addition, if the amount of records stored in your SQL Table is not more than 2000 (please change the "Data row limit for non-delegable queries" option to maxikum value -- 2000, please check here for mode details), please consider take a try with the following workaorund:

Set the OnStart property of the App control or OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, 'TableA')

Then modify your formula as below:

If(
!IsBlank(LookUp('TableA', id = globalvariable.main_id)), LookUp(RecordsCollection, id = LookUp('TableA', id = globalvariable.main_id, sub_id)) )

Please take a try with above solution, check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vverb
Level: Powered On

Re: Delegation warning for Nested LookUp (SQL Server)

 

Dear @v-xida-msft ,

Thank you for your thorough reply. However I was already considering using a variable to store the inner LookUp's result, as you suggested, but I was more curious to whether this was actually considered the 'best practice' in this situation and whether the Delegation warning is justified here or whether the nested LookUp situation does actually delegate properly DESPITE the warning. I have not run any tests myself yet, though I might do if I find the time, but was wondering if anyone else has tested this before.

As for the amount of rows in the table, the amount is currently limited but will grow significantly over time once the app hits production and gets used. So I'm not going to use a collection in this case. 

Here is a screenshot of the warning as you requested if it helps.
image.png

 

 

 

Conclusion:

I'll use a Throwaway variable with UpdateContext if I have to but I'll probably run some tests first to make sure it actually doesn't delegate; I'd loathe to clutter up my code by adding temporary variables in every screen I need to do such a nested lookup (I like clean code and memory ^^). If I ran the tests I'll post an update here with my findings. Meanwhile, any developer insight is welcome.

 

Greets

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,096)