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