Unanswered

Filtering with IsToday is not returning results

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello everyone,

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?

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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.
wyotim 2,545 on at

Like
a
(2)

Report
Copy link

Link copied!
While I haven't tested this, it could be similar to how
IsBlank(thisThing)
won't delegate but
thisThing = Blank()
will delegate.

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.
Mr-Dang-MSFT Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous,

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

+ @Anonymous

Note: I've renamed this topic to "Filtering with IsToday is not returning results" for better searchability.
ZePowerDiver 279 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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 ?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello,
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).
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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?
ZePowerDiver 279 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.