Avoiding delegation while filtering on Created column

[MQN]

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? 

[WarrenBelz · Accepted solution]

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. 

[WarrenBelz]

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.