Delegation warning for Nested LookUp (SQL Server)

[Anonymous]

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

[v-xida-msft]

Hi @Anonymous ,

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:

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,

[Anonymous]

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.

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

[KurtSchron]

Has anyone else been able to solve this issue another way? 

I'm trying to solve a similar issue however I am trying to do it in a Gallery. So I am not able to make variable for all of the gallery rows that I have. 

Any thoughts on how to solve that?