cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering with IsToday is not returning results

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?

7 REPLIES 7
Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

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.

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

 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 ?

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (4,916)