we have developed a PowerApp in which the presented recordsets oa a browser gallery are filtered to only show entries that have been made today:
SortByColumns( Search( Filter('[dbo].[Sicherheitsanalysen]'; IsToday(Datum)); TextSearchBox1.Text; "Bauleiter"; "Bemerkungen" ); "Bauleiter"; If( SortDescending1; Descending; Ascending ) )
Unfortunately the app stopped working today (No recordsets are displayed at all). The problem seems to have something todo with a delegation warning we're getting for IsToday but we weren't able to figure out a better approach on filtering the recordsets without using the IsToday method.
Has anyone here encountered this issue and/or has a solution?
Hi @Anonymous ,
Delegation don't allow you to search by date in a table of more than 500 rows (2000 if you change it in your PowerApps options)
You have to find a way to reduce the number of row that you will filter by date.
Something that i did for myself is to create a View in my Database that already filter by a more close date, like all result of the last week and directly link the view to my Apps. What's important is to be sure that the result send by your database don't send more row than the delegation limit.
Hope this will help you.
While I haven't tested this, it could be similar to how
won't delegate but
thisThing = Blank()
You could try this if you haven't already:
SortByColumns( Search( Filter('[dbo].[Sicherheitsanalysen]'; Datum = Today()); TextSearchBox1.Text; "Bauleiter"; "Bemerkungen" ); "Bauleiter"; If( SortDescending1; Descending; Ascending ) )
*edit* And it should be noted that working with dates does get a bit funky sometimes. I have started keeping dates in integer format (yyyymmdd) as well as regular date format in all tables with dates to make this easier. It's pretty simple to auto generate this from a date column in SQL.
As @wyotim mentioned, the IsToday function is non-delegable in your formula. We have made improvements recently so that Today() is not evaluated for each row in a filter and is therefore delegable depending on your condition.
@wyotim's solution should work here because it's a simple equals condition.
If this works for you, can you mark it as a solution?
Link to additional information: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview
+ @LRVinNC who is another expert in formulas
Note: I've renamed this topic to "Filtering with IsToday is not returning results" for better searchability.
A bit more work to implement, but could a Flow have been considered as an option for situations where we need to work with non delegatable filters ?
i've tried your solution. Unfortunately this statement returns no results even though Datum and Today() should match (they both have the correct format AND the very same value).
It is actualy getting stranger: When i add the expression Datum = Today() it gives me the proper true and false results for every entry but when using it in the filter i get no results at all...could this be another PowerApps Bug?
and I'm sure you've isolated the Filter function to make sure that this is where the issue is !?
Something like ClearCollect(validCollection;Filter('[dbo].[Sicherheitsanalysen]'; Datum = Today()))
I'm (still quite) surprised that it didn't resolve your issue with the Flow, if that's the case, it's no a PowerApps specific bug, more like date conversion issue.. Sometimes to help out with those, I do a DateDiff function instead.. also if your column is date time, then a different timezone could position the date prior to what you expect. a few things to look into.
Check out the on demand sessions that are available now!
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Features releasing from October 2020 through March 2021
Check out the Power Platform Community Highlights