cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

LookUp Function and Delegation (Azure SQL DB datasource)

I have the following formula as the datasource for a Gallery:

 

AddColumns(GrpOrderByHeader, "MyOrderHeaderID", LookUp('[Order].[OrderDetail]',GrpOrderByHeader[@OrderDetailID] = OrderDetailID, OrderHeaderID))

The following part (in the LookUp) is showing underlined in blue as non-delegable:

[@OrderDetailID] = 

For reference, GrpByOrderHeader is defined as follows:

SortByColumns(GroupBy(Filter('[Order].[OrderDetail]',
OrderDetailTimeInt >= (varTodayInt-(10000*TextInput1))) ,
"OrderHeaderID","GrpOrderByHeader"),"OrderHeaderID",Descending)

 

 

I only actually need the data for the last few days. If I change the references in my 'AddColumns' formula for [Order].[OrderDetail] to filter the data (as below) will PowerApps delegate the filter to SQL Server and then process the (non-delegable) LookUp internally using just the filtered data?

Filter('[Order].[OrderDetail]',OrderDetailTimeInt >= varTodayInt)

I guess the crux of my question is: When one part of a formula is non-delegable, does that mean every part of the formula will not be delegated? I.e. Will PowerApps locally cache the entire [Order].[OrderDetails] table to evaluate the formula regardless of the filters I put in?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: LookUp Function and Delegation (Azure SQL DB datasource)

Hi @PaulD1,

 

Formulas that cannot be delegated will be processed locally. All the data will brought to the device first, and has a limit on the amount of data that can be processed locally: 500 records. Since the [Order].[OrderDetails] part will be processed locally, then only the first 500 records of the million records will be scanned. If the desired record is record 501, or 500,001, it will not be considered or returned by Filter

 

For your reference:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: LookUp Function and Delegation (Azure SQL DB datasource)

Hi @PaulD1,

 

Formulas that cannot be delegated will be processed locally. All the data will brought to the device first, and has a limit on the amount of data that can be processed locally: 500 records. Since the [Order].[OrderDetails] part will be processed locally, then only the first 500 records of the million records will be scanned. If the desired record is record 501, or 500,001, it will not be considered or returned by Filter

 

For your reference:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: LookUp Function and Delegation (Azure SQL DB datasource)

Thanks @v-monli-msft

 

Fortunately, with the help of the fine people who frequent these forums, I was able to rewrite the formula so it is delegable. But it is good to know that any single element that cannot be delegated causes every part of a formula to be processed locally with the problems that causes for performance and limits to record caching.