cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

 


@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
Resolver II
Resolver II

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 👌

Highlighted
Resolver II
Resolver II

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:

 

 


@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
Super User
Super User

Re: Between times Filter not working

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


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


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

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (10,788)