cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

View solution in original post

4 REPLIES 4
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.

View solution in original post

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.

Highlighted
mseklec
Level: Powered On

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

@PaulD1, can you share or link to the solution you found for this issue? I am trying to overcome the same delegation issue.

Super User
Super User

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

Hi @mseklec 

Although the documentation seems to suggest that using any non-delegable function within a formula makes the whole formula non-delegable this is not always the case.

For example, if I have a formula in SharePoint (where Search is non-delegable) such as:

Search(Filter(MyTable,MyField = "X"),txtMySearchText.Text,MySearchField))

Then in my experience, the Filter will run server side and then the Search will act on the records returned by the filter within the client. The innermost part of the formula is the first to run, so structure your formulas with the delegable functions inner-most.

It is worth testing by varying the Delegation limit (usually 500) in App Settings with some test data to check the behaviour when the delegation limit is exceeded.

Some things I have found:

  • When a formula becomes complex enough, it often won't report whether it is delegable or not - you'll need to test.
  • If your filter predicate is a claculation e.g. X<= Value(DateDiff(Today(),Now(),Hours)) that will sometimes force delegation - put the calculation into a label (or variable) and reference that (the result of the calculation) in the predicate instead and sometimes it will become delegable.
  • Different data sources have different delegation capabilities (Azure SQL has the best capabilities but is moving to premium pricing which will put it out of reach for many solutions Smiley Sad) - there used to be a really useful matrix comparing the delegation capabilities of the most popular data sources at (or linked from) https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview but I can no longer find it (<cynic> perhaps it has been memory-holed to enourage uptake of CDS over Azure SQL </cynic>).
  • SQL does not delegate filters on dates - use a calculated column in the SQL table (or a view) to convert the date into an integer in YYYYMMDD format (20191092) and then use that column as your filter predicate to overcome this.

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 (6,223)