Gallery Filter by a date (SharePoint List) - 'Part of this filter formula cannot be evaluated remote

[PaulD1]

I'm just getting started with PowerApps and have created an App for taking menu orders in a restaurant.

The backend is a set of SharePoint lists (Order Header, Order, Menu Item).

I want only the current and prior days' Order Headers to appear in the Gallery - I have the following filter:

SortByColumns(Filter(
Filter(OrderHeaders,DateDiff(OrderHeaderTime, Now())<1)
, StartsWith(Title, TextSearchBox1.Text)), "ID", If(SortDescending1, Descending, Ascending))

This is working as desired but I am getting the blue warning circle that states that parts of the filter cannot be evaluated remotely. I have tried many variations on the above but as far as I can see, any filter based on dates gives the same warning.

If the formula cannot be evaluated remotely, presumably all the data from the list must be downloaded to the client. Assuming 50 orders per day for one year, that is 18250 rows that have to be loaded to the client for evaluation.

Question 1: Is it possible with a SharePoint list as the data source for filter by date and have the filter processed by (delegated to) SharePoint? 

Question 2: At what point (number of rows) will performance become an issue?

Question 3: Would Azure SQL DB be a better data source be a better back-end for this application? I'm about to do some testing of my own, but feedback from anyone with experience would be greatly appreciated.

Thanks

Paul

[charafRef]

Hi ,

according to this link you have the answer for your Question 1 & 2 , understand more about delegation and 500 limits rows.

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/#blue-dot-suggestions 

[PaulD1]

Thanks charafRef

I had come across that link. It claims that a filter on a SharePoint list is delegable with operators such as < and > so I tried the following syntax (stripping out the sorting and searching options for clarity):

Filter(OrderHeaders,OrderHeaderTime>Now()-10)

Unfortunately, I still get the blue dot telling me that the formula is not delegable. As far as I can tell from the link, this should be delegable as it is a simple less-than comparison against a SharePoint list with the data type of the predicate matching the data type of the column.

There is nothing at the link to suggest dates are not delegable (in fact dates are not mentioned at all), but as soon as I add a date column to the formula, the date column is immediately underlined in blue with a pop-up warning about the formula not being evaluated remotely. I can only conclude that, despite there appearing to be nothing documented, filters on date fields in SharePoint lists cannot be delegated no matter how simple the expression.

I'm going to try with an Azure DB back-end (unfortunately there is some bug preventing me signing up for a trial account right now) to see if dates can be delegated on that platform. If not, at least I can create a 'working table' and a 'history table' and a scheduled job to copy data from the working table to the history table every night and clear out the working table.

Interested to hear how other people are dealing with the non-delegable problem and the 500 list item limit when working with SharePoint lists.

[Meneghino]

hi @PaulD1

Azure SQL Database is great, but unfortunately this will not solve your issue.

For a date type column only the = operator can be used (not > or <)

The work-around (which works both in Azure SQL DB and Sharepoint) is to create a calculated column of integer type with value YYYYMMDD based off your date type column.  Then you can filter all you want with the integer type column and being delegated.

Hope this helps.

[PaulD1]

Thanks Menghino 

I have created a new calculated column in SharePoint of type Number with 0 decimal places. I have named it OrderHeaderTimeInt and set the following formula to ensure YYYYMMDD 'format'.

=INT(YEAR(OrderHeaderTime)*10000+MONTH(OrderHeaderTime)*100+DAY(OrderHeaderTime))

I have checked the column in SharePoint and all appears well - the dates actually appear as 20,170,918 (i.e. the commas confirm the data is a numeric data type).

I have refreshed my PowerApp and displayed the OrderHeaderTimeInt value in the gallery. The values appear as 20170918.0000000.

I have then tried to use the new column as a filter for the gallery, e.g.

Filter(OrderHeaders,OrderHeaderTimeInt = 20170918)

However, not only is this still displaying the blue dot (non-delegable) but it is not returning any values. If I use > 0, I still get nothing, but oddly if I put <>0 all the records appear. I have experimented with putting the criteria inside Value() and including the decimal places displayed in the gallery - but none of these remove the blue dot or return the correct data.

I also tried making the calculated column a text field and setting the formula as follows:

=YEAR([OrderHeaderTime])&MONTH([OrderHeaderTime])&DAY([OrderHeaderTime]) 

And then attempting filter in the PowerApp as so:

Filter(OrderHeaders,OrderHeaderTimeInt = "2017918")

This does yield the correct result in the gallery (yay!) but it is still non-delegable (booh!)

Any suggestions for what I am doing wrong?

Many thanks

Paul

[Meneghino]

Hi @PaulD1

1) Let me try to reproduce this now in SharePoint, I will post back in 20-30 min.

2) This solution works for sure in SQL Server / Azure SQL Database, and these data sources have other advantages, please see here if interested:

http://baizini-it.com/blog/index.php/2017/09/26/powerapps-and-azure-sql-database-101/

[Meneghino · Accepted solution]

Hi again @PaulD1

Indeed it seems the solution does not work in SP currently.

I have just confirmed that it works with Azure SQL DB.  If you are going to have thousands of records I would urge you to switch.

[PaulD1]

Thanks Meneghino

I'm going to switch backend to Azure SQL DB as suggested as I hope to have thousands of rows (assuming the business takes off!). I'm quite proficient in SQL Server so I can try to bypass limitations in PowerApps by moving more logic/processing to the DB.

[Meneghino]

Hi again @PaulD1

Please see this post if you will be working with Azure:

http://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues

I would really appreciate any comments on my blog posts.

[PaulD1]

OK, so I have ported my back-end over to Azure SQL DB and added a calculated column that turns the date into an integer representation in the form yyyymmdd (e.g. 20170929).

If I now run a filter and explicitly specify a numeric value such as 20170929 I see that the filter is delegable - Yay!

However, I'm struggling with making the 'date' in the filter dynamic. E.g. if I only want to show formulas for today. Once I put a calculated value into the formula it becomes non-delegable again - see below for an example of one of the methods I tried:

Filter('[Order].[OrderHeader]',OrderHeaderDateInt >= 
	Value( Year(Today())*10000 + Month(Today())*1000+Day(Today()))  )

I see that if I specify a numeric value in a label I can then use Value(Label.Text) and that will give a delegable filter.

Question: How can I populate a label with the value representing the current date rather than a formula that returns the value (short of making the user manually enter the date which would be very clunky)?

Thanks

Paul