cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MQN
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
WarrenBelz
Super User
Super User

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

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,074)