cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Memorable Member
Memorable Member

Filter by date issue

I have a simple filter that I want to use that filters a Gallery with an SP list,  but no matter what I do I get the error pictured below.

The Filter is Filter(Source,DateTo=Dat1)

Where DateTo is a date field in the SP list and Dat1 is a Date variable.  

 

It should be delegable and I believe should work. The error is nothing I have encountered before.  Any ideas?

 

error.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks @v-yutliu-msft for the alternative.

I posted a solution for this recently@leyburn19 this may also be worth a look.

 

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

7 REPLIES 7
Super User III
Super User III

Hi @leyburn19 ,
I am assuming Date is not the actual name of your field

Filter(
   Source,
   Text(Date,ShortDate)=Text(Dat1,ShortDate)
)

or

Filter(
   Source,
   DateDiff(
      Date,
      Dat1,
      Days
   )=0
)

 

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.

Thanks Warren, but that makes the solution non delegable.  This needs to work on 7000 plus records.  I thought a simple = would have been delegable,  but the error threw me as it is not relative.  Do I assume dates are never delegable?

Hi @leyburn19 ,

Yes you have that right - no matter how you "package" a direct date query, it is not Delegable.

However one workaround (a bit of work for you with 7000 records) is to have a "shadow" numeric field updated each time you add or or update field data set to "yyyymmdd" format

Value(Text(YourDateField,"yyyymmdd"))

You can then convert you query field to the same format (a Variable is a good way) and all operators will be 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.

Interesting solution.  I will give that a test.  This would be a significant win for other things like booking apps as well

Hi @leyburn19 ,

The error that you met is because Dat1 is not date type value.

It does not mean it is date type value even one data looks like date format.

Since DateTo and  Dat1 is not the same data type, you can not compare them together directly.

However, date type is not delegate for sharepoint list.

So the solution provided by @WarrenBelz is ok. When you update the DateTo field, you could update a Number or text type field with the same data. Then use this field to compare with Dat1. As text and number is delegate for sharepoint list.

 

However, since your data source record number is very large. Inserting another column is not an easy thing.

So I suggest you try another solution: use collection to avoid delegation.

In collection, there's no delegation limit. One collection could save 10K records.

You just need to save your data to collection, then filter based on that collection. You will not worry about only using delegate function.

For example: 

set the app's OnStart:

ClearCollect(collection1,Source)

the filter function:

Filter(collection,DateTo=DateValue(Dat1))
//convert Dat1 to date type

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yutliu-msft for the alternative.

I posted a solution for this recently@leyburn19 this may also be worth a look.

 

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

Dat1 is created by an onchange event of a DatePIcker Control which creates a variable date

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 Solution Authors
Top Kudoed Authors
Users online (68,726)