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 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
WarrenBelz
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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (67,943)