Between times Filter not working

[Nix23]

I am trying to only show items  within today  if the start time and end time is today since this is being displayed on Sharepoint. 

the gallery does not change no matter if i use Now as well which i think should work. 

Sort(Filter('PTO Calendar', 'Start Time' <=Today() && 'End Time' >=Today()), SortOrder.Descending)

[iAm_ManCat]

Hi @Anonymous 

Could you explain how you "set a variable to be the dataset" please?

Delegation limitations (up to a max of 2000 records) should still limit how many values can be collected and stored, so in @Nix23 's case it would not include any items above the delegation limit and the formula would still return blank results as the more recent items will not be accessible.

If you are somehow able to get around that, I would be VERY interested in that and will credit you directly when I write a blog post about it.

Cheers,

Sancho

[Anonymous]

@iAm_ManCat 

This is a snapshot of some of my code.

Status has a delegation error when filtered from the original sharepoint link '1. Created'

But not from 'Copy'

To create copy I just used Set(Copy, '1. Created')

Since i had a global refresh on my data on a timer - Refresh('1. Created')

I just changed it to:

Refresh('1. Created')

Set(Copy, 1. Created)

I don't get any delegation errors while filtering Copy and data shows up perfectly 👌

[Anonymous]

@iAm_ManCat 

It gets rid of apparently every delegation error.

Here's the timer:

[iAm_ManCat]

Hey @Anonymous 

Ok, that's what I thought - so you may not have known this but when you use Set() the delegation limit applies - so you won't be able to Set() more than the delegable limit for your App. The maximum amount of records you can set as delegable at present is 2000 entries. So for people like us who have SharePoint lists in the Tens of Thousands, we HAVE to make sure that every query is delegable, this is why I've set the Number field to represent the Date values.

So even if you set it to a variable, it will only store 2000 records

So yes, while you don't see any delegation warnings anymore, that is because the delegation is now being performed against the collection/variable before your formula is applied, so the collection/variable has already been limited to 2000 records.

So I'll give you an example - if I create over 2000 records (2300+):

Then set your App to the Max delegable of 2000:

Then Set the datasource to a variable:

And try use Countrows on your Set variable:

[iAm_ManCat]

see below post:

[iAm_ManCat]

...(Continued)

Then check CountRows for that variable:

You'll see that it's already limited to 2000 rows.

This is why your formula does not show any delegation issues, it is performing that query against your variable/collection that is already limited to 2000 rows via the delegable limit.

If you use a delegable query, as I suggested in my initial post, then you will be able to search through any number of rows without limit:

If you have any further queries about delegation feel free to ask, I use only SharePoint as a data source at work so I have to be mindful of these queries every single day, especially once your lists start hitting 20k+ items 😺

Cheers,

Sancho

[iAm_ManCat]

@Anonymous - if your '1. Created' list gets larger than 2000 items, it will stop producing any new results via your formula as the Set() function will stop collecting/storing at 2000 items - You can follow the delegation steps I mentioned above to create new columns and then use Power Automate to fill those columns with values extracted from your rows - that will cover any existing data, then you will need to make sure that your App uses the dateTime to number conversion and stores those values so that you can filter without issue.

@Nix23 if you follow the advice in my first reply (create new columns, get them filled using Power Automate, then set your filter to what I suggested and make sure any new items are creating numeric datetime values) then you will be able to resolve your issue with showing dates where either the start, finish or in-between fall on today - you will also resolve your issue with delegation (no items showing)

Cheers,

Sancho