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

[Meneghino]

Hi @PaulD1

The issue is that Today() is not delegable, if that makes any sense.

I hate putting values in hidden labels to refer to them, so my suggestion would be to use this in the OnStart property of the first screen:

Set(MyTodayVar, Today())

Then use MyTodayVar anywhere you would use Today and it will be delegated.

Of course this value will not update if the app is kept open overnight, but you can have a timer refresh this variable every few hours if you really want, although I would not recommend that.

Don't forget that you will need to close and open the app for the variable to be set in Studio or web designer, as the OnStart will only fire once.

[PaulD1]

Awesome - thanks very much indeed Meneghino. Assigning the formula result to a variable and then using that variable in the filter seems to do the trick!

[DaveShepherd]

I too wanted to Filter based on today's date against a SharePoint list date field, within PowerApps

I dont know if this is the best way, but seems to be working for my scenario.(PowerApp will be the only application adding new records to the SP list)

My hack to get around the blue dot and delegation was as follows:

In the SharePoint list create "Single line of text" column e.g DateText - NOT a Calculated column.

In the PowerApp OnStart, create a new variable  -

Set(TodaysDate, Today())

In the PowerApp, when creating a new record, format Default property of the DataCardValue of DateText field using

•Text(TodaysDate, "[$-en-US]yyyymmdd")

After saving the record away, to retrieve into the gallery I used.

Filter('Operators Feedback', DateText = Text(TodaysDate, "[$-en-US]yyyymmdd"))

There a now no blue dot warnings and returns the correct items from the list.

Hope this makes sense

[Emilio]

This solution saves me.

Thanks 

[Anonymous]

Nice solution ! Worked for me. Thanks !

[MatthewMcDMVP]

I took a slightly different approach since I was using the standard SharePoint Created column for comparison. Also, I needed a toggle to go between "Filter Today" and "Show All".

In the App I have a filter toggle button OnSelect defined:

Set(FilterAllDate, DateAdd( Today(), 1 ));
Set(FilterTodayDate, DateAdd( Today(), 0 ));
UpdateContext({FilterToday: !FilterToday})

In my Data Items I have:

SortByColumns(Filter(CheckIn, StartsWith(Title, TextSearchBox1.Text) && If(FilterToday,Created >= FilterTodayDate,Created<=FilterAllDate)), "Created", If(SortDescending1, Descending, Ascending))

[SergiP]

hi,

I don't know if anything has been changed since your answers but nowadays as soon as I include any date field in the filter it is not delegable anymore.

Even when I use the trick of asigning the today() to a variable.

[Anonymous]

hi @DaveShepherd,

thank you for your solution, i do the same thing, i create a new column in the sharepoint list and i set the TodayDate variable but instead of puting = operator in the filter function i tried to use >= operator but it does not accept it. any ideas ?!

[CindyZ]

@DaveShepherd This turned out to be a tremendous help to me.  So glad I found it!!  Was just about to give up!  Thank you! 

[kdunk]

@DaveShepherd

I know it'd been a long while since your post, but was wondering if you had any thoughts on how I could achieve filtering for the last 3 days (including today's date) (or even the last week would be OK) rather than just today's date, while avoiding the delegation issue.