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

Re: Between times Filter not working

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

 




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

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 👌

kamikaze4416
Level 8

Re: Between times Filter not working

@iAm_ManCat 

It gets rid of apparently every delegation error.

Here's the timer:

Capture2.PNG

Highlighted
Super User
Super User

Re: Between times Filter not working

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:

 

 




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

Re: Between times Filter not working

see below post:




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

Re: Between times Filter not working

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




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

Re: Between times Filter not working

@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




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

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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

Top Solution Authors
Top Kudoed Authors
Users online (5,463)