cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Between times Filter not working

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)

 

 

26 REPLIES 26

Hi @kamikaze4416 

 

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

 


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted

example.PNG

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

Highlighted

@iAm_ManCat 

It gets rid of apparently every delegation error.

Here's the timer:

Capture2.PNG

Highlighted

Hey @kamikaze4416 

 

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+):

image.png

 

Then set your App to the Max delegable of 2000:
image.png

 

 

Then Set the datasource to a variable:

image.png

 

And try use Countrows on your Set variable:

 

 


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted

see below post:


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted

...(Continued)

 

Then check CountRows for that variable:
image.png

 

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:


image.png

 

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

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,820)