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

Bug? Non Delegable Formula NOT marked with blue dot (Azure SQL DB)

I'm trying to do various data-manipulation 'gymnastics' to try to work around the fact that PowerApps cannot currently use SQL Views and my data is normalised.

 

After much trial and error, I came up with the following which was not marked as non-delegable (no blue dot):

 

GroupBy(Filter(AddColumns(AddColumns('[Order].[OrderDetail]',"MyMenuCategoryID", LookUp(LocalMenuItem, MenuItemID = '[Order].[OrderDetail]'[@MenuItemID],MenuCategoryID)),"MyPreparedInKitchen",LookUp(LocalMenuCategory, CategoryID = MyMenuCategoryID,PreparedInKitchen)),MyPreparedInKitchen = true && OrderDetailTimeInt >= varTodayTimeInt )   ,"OrderHeaderID","GrpOrderByHeader")

(for anyone interested, the OrderDetail table contains a MenuItemID which appears in the MenuItem table, this has a MenuCategoryID which appears in the MenuCategory table and has a column 'PreparedInKitchen' which I need to filter my results by, in addition to limiting them to orders placed today).

 

This was working great until I hit the 500 record limit at which it has become clear that only the first 500 records in OrderDetail are being considered.

 

We really need a reliable way of checking whether a formula is delegable or not without having to generate lots of dummy data to test against.

 

If anyone knows of a good way to check the delegable state of a formula, please let me know...

 

Cheers

Paul

4 REPLIES 4
Super User
Super User

Re: Bug? Non Delegable Formula NOT marked with blue dot (Azure SQL DB)

May have blue writing instead of blue dot.

Best workarounds I can suggest is filtering the tables into collections, or, as I've had to do:

Create a new table in SQL manually using a create table query for your formula with all the columns you need.

create a stored procedure in SQL to carry out your query each night (say midnight) and either append the table or truncate it and run the insert statement again.

set up a flow to execute the stored procedure on a schedule at midnight

this way you can just use the new table in PowerApps.

 

Super User
Super User

Re: Bug? Non Delegable Formula NOT marked with blue dot (Azure SQL DB)

Hi @Delid4ve

 

There was no blue underlining, text or dot. I've been double-checking every formula as I create it to ensure that it is delegable.

 

Filtering tables into local collections is the route I've taken. Fortunately, I can filter into local collections as the filters on the individual tables are delegable and I do not expect more than 500 results from any filter.

 

Stored Proc won't work for me as my data is 'live' (this is part of a system for a restaurant - servers take orders in one app and those orders then show up in another app in the kitchen so the chef knows what orders to prepare). I have a timer which refreshes my collections every minute - seems to be working so far in testing... 

Microsoft Employee

Re: Bug? Non Delegable Formula NOT marked with blue dot (Azure SQL DB)

A blue dot should usually show up if you are using a query which is not delegatable (when the actual data source does support delegation)

 

Also, here is the link which has the delegatable calls listed

 

https://docs.microsoft.com/en-us/powerapps/delegation-list  

Super User
Super User

Re: Bug? Non Delegable Formula NOT marked with blue dot (Azure SQL DB)

Thanks @mayuro 

 

>>A blue dot should usually show up if you are using a query which is not delegatable (when the actual data source does support delegation)

 

This is my point - the blue dot is not consistent/reliable. In the example given, there was no blue dot but only the first 500 records were considered by the filter. When I modified my application to filter first into a collection and then perform the AddColumns, LookUps and GroupBy on the collection, all was well.

 

I suspect the issue is filtering by a column added to a data source via a LookUp (even though both Filter and LookUp support delegation for SQL Server).

 

Until PowerApps supports SQL Server Views (please be soon!) we have to do a lot of data manipulation in PowerApps. If we cannot rely on the non-delegable warnings, we have to ensure we test with 500+ records to check if formulas are being delegated and that is a lot of extra work/hassle.