Answered

Avoiding delegation while filtering on Created column

(0) Share

Report

Posted on by 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?

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,528 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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 156,528 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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.

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(









 
                              
                            
                                
                                    
                                        Under review
                                        
                                    
                                    
                                         
                                            
                                            
                                                Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.


                    
                    
                    
                         
                                                                                       
                        
                    
                    
                     
                        
                          Join the conversation