cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Between times Filter not working

In these kinds of cases I would use label debugging as a start. 

Create labels in your gallery that are each part of your formula, ie one for IsToday('Start Time') etc. although its pretty clear in this case that the issue is delegation - as shown by the blue lines:

image.png

 

Also, just wanted to double-check, you only want items that BOTH start and end today? Or do you want items their EITHER Start Today or End Today? I would think the latter, in which case your formula needs to use the OR function || instead of &&.

 

...and If I had to think about it, I'd say you probably wanted only items where Today falls within the range of Start Time and End Time yeah?

 

I would also not use the IsToday function - this is not a delegable function with Dates - Date fields are delegable to some degree now, but not with Time unless the item's time is exactly the same as only the = function currently works.

 

Here's how I would solve this:

 

Create a new number column for each date that uses the format yyyymmddhhmm - that way you can compare them as number fields using either < or > or =

 

So I created a new SharePoint list, created two date fields with times, then created two new columns (one for each date, and had them set as above, then altered the formula to find any entries where Now() exists between these two dates:
(EDIT: used Now() instead of Today() and made the range include all items in the 24h period of Today)

image.png

 

Confirmed this only showed items that had a range that included today by adding entries with dates after today and at midnight tonight:

image.png

 

 

Here's the formula, you may want to choose another column to SortByColumns:

 

 

 

SortByColumns(
    Filter(DateDelegationTest,

           // Start time is before end of today

           StartTimeNumber <= Value(Text(Now(), "[$-en-GB]yyyymmdd" & "2359"))
           &&

           // End Time is either after today or Is Today

           EndTimeNumber >= Value(Text(Now(), "[$-en-GB]yyyymmdd" & "0000"))
    ),
    "ID", Descending
)

 

 

 


 

Then when creating any new items, we'd have it fill the number value of Start Time or End Time into the field automatically:

Value(Text(StartTimeControlName.Text, "[$-en-GB]yyyymmddhhmm"))

 

 

 

Let me know if that works out for you and if there's any other limitations or specifics you want around the dates,

 

 

Cheers!

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
kamikaze4416
Level 8

Re: Between times Filter not working

@Nix23 

Today() is a fixed time, not a range as far as i know.

Im 100% certain there's a shorter way to do this but....

Sort(Filter('PTO Calendar', Date(Year('Start Time'),Month('Start Time'),Day('Start Time')) = Today() && DateDiff('End Time', Date(Year(Now()),Month(Now()),Day(Now())+1)), SortOrder.Descending)

It basically checks if it started today and ends before tomorrow

Super User
Super User

Re: Between times Filter not working

@kamikaze4416  Today is the fixed time yes, but start and end time then define a range

 

Unfortunately, the formula you gave also suffers from the issue of delegation as DateTime are not delegable with Filter so it will stop working once there are more than 500 (or 2000 if maxed) records:

image.png

 

 




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
kamikaze4416
Level 8

Re: Between times Filter not working

@Nix23 @iAm_ManCat 

This should work then:

SortByColumns(Filter('PTO Calendar', Day(DateDiff(Today(), 'Start Time')) > -1 && Day(DateDiff('End Time', Today())) > 0,"ID", SortOrder.Descending))

It covers the logical error @iAm_ManCat accidentally had.

Super User
Super User

Re: Between times Filter not working

Hey @kamikaze4416 

 

Apart from using Now() instead of Today() for my current value, I'm not really seeing what logical error I had. Could you explain please? Will update my post above.

 

Your formula is still not delegable though, and this one actually returns no results:

image.png

 

Thanks!

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
kamikaze4416
Level 8

Re: Between times Filter not working

@iAm_ManCat 

Untitled.png

Since Today() gives the first second of today it fails to allow starttime to be 11am today for example since its not = to the first second and it's not before today either. Also why are you getting the value in date format of today()? Seems unnecessary since dates can be directly compared. Or are you storing it as a number?

kamikaze4416
Level 8

Re: Between times Filter not working

@iAm_ManCat 

it was meant to be

SortByColumns(Filter('PTO Calendar', Day(DateDiff(Today(), 'Start Time')) < 1 && Day(DateDiff('End Time', Today())) > 0,"ID", SortOrder.Descending))

Highlighted
Super User
Super User

Re: Between times Filter not working

Hi,

 

 @kamikaze4416 I'm not sure you're understanding the issue with delegation - @Nix23 is using a SharePoint list, and datetime is not a delegable item when using Filter.

 

Please could you check here regarding date delegation:

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

As while these can be compared directly, they are not delegable, which is why I suggested to create number values of them in order to be a delegable query.

 

I have updated my original post above with the modified formula and screenshots,

 

Cheers,

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
kamikaze4416
Level 8

Re: Between times Filter not working

@iAm_ManCat 

I was solving it based on the original question instead of causing the hassle of changing sharepoint datatypes cause that has been an annoyance for me personally.

You can solve delegation issues with dates by setting a variable to be equal to the sharepoint and that allows filters. At least that worked in the past 🤔

kamikaze4416
Level 8

Re: Between times Filter not working

@iAm_ManCat 

Yep, just double checked, it works completely fine if u set a variable to be the dataset 👌

That way, you dont need the added stress of converting between types constantly or changing datatype in sharepoint

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,604)