cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Avoiding delegation while filtering on Created column

Hello, 

 

I'd like to filter a sharepoint list by the date created. Specifically, I want to be able to set a date range(using two datepickers for this right now) and show all the items between those dates. However, doing something like Filter(sourceList, Created < dtMax.SelectedDate, Created > dtMin.SelectedDate) gets me delegation warnings. I also tried a suggestion I found earlier, which is something like Created < Date(Year{dtMax.SelectedDate),Month(dtMax.SelectedDate),Day(dtMax.SelectedDate) but that doesn't seem to avoid delegation either. 

 

I tried to create a Calculated column of the form Year(created)*10000+Month(created)*100+Day(created) to get around this, but it doesn't seem to be treating my  calculated column as a plain number column. Unfortunately, I've got a bunch of historical data here, so I  can't just add a new text column that holds my created dates to filter on. Is there a way around the delegation warnings? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Hi @MQN ,

No matter how you "wrap" or present Date fields, they are not Delegable filters, neither are Calculated fields in the data source.

The most common workaround is to create a numeric field in your data source that is updated each time a date is added or changed. The formula for this numeric field would be 

Value(Text(YourDateControlName.SelectedDate,"yyyymmdd"))

This can be on the Default of a hidden control and Reset at the OnChange event of the date picker. You could also Patch this.

You can then filter with the formula above relating to the numeric field as numeric filters are delegable.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up

View solution in original post

2 REPLIES 2
Super User III
Super User III

Hi @MQN ,

No matter how you "wrap" or present Date fields, they are not Delegable filters, neither are Calculated fields in the data source.

The most common workaround is to create a numeric field in your data source that is updated each time a date is added or changed. The formula for this numeric field would be 

Value(Text(YourDateControlName.SelectedDate,"yyyymmdd"))

This can be on the Default of a hidden control and Reset at the OnChange event of the date picker. You could also Patch this.

You can then filter with the formula above relating to the numeric field as numeric filters are delegable.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up

View solution in original post

Hi @MQN ,

I forgot to mention now looking at your requirements that you can do a ForAll update on your current data. If you have less than 2000 items or can collect in filtered groups of less than this with a delegable query, you can update a numeric field with something like the below. Be prepared for this to take some time to execute.

ClearCollect(
    colWhatever,
    RenameColumns(
        YourListName,
        "ID",
        "IDRef"
    )
);
ForAll(
    colWhatever,
    UpdateIf(
        YourListName,
        ID = IDRef,
        {YourNumericFieldName: Value(Text(YourDateFieldName,"yyyymmdd"))}
    )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (83,791)