cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RRC
Level: Powered On

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
Thomas_B
Level: Powered On

Re: Problem with FIlter/IsToday

Hi @RRC ,

 

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.

Super User
Super User

Re: Problem with FIlter/IsToday

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.

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: Problem with FIlter/IsToday

@RRC

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

@TopShelf-MSFT 

 

Note: I've renamed this topic to "Filtering with IsToday is not returning results" for better searchability.

ZePowerDiver
Level 8

Re: Problem with FIlter/IsToday

 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 ?

RRC
Level: Powered On

Re: Filtering with IsToday is not returning results

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

RRC
Level: Powered On

Re: Filtering with IsToday is not returning results

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
Level 8

Re: Filtering with IsToday is not returning results

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 327 members 5,608 guests
Please welcome our newest community members: