Answered

Filtering a Dataverse table with date

(0) Share

Report

Posted on by randomsky

110

I have a Dataverse table with a date only field called entryDate that is used in a canvas app gallery. In the gallery items property I use this:

Filter(tblLedgers,entryDate=Text(DatePickerCanvas2.SelectedDate,"yyyy-mm-dd"))
 
To my surprise this works but gives a warning saying: Incompatible types for comparison. These types can't be compared: DateTime, Text.
 
I was expecting this to work but it doesn't:
 
Filter(tblLedgers,entryDate=DatePickerCanvas2.SelectedDate)
 
and neither does:
 
Filter(tblLedgers,entryDate=DateValue(DatePickerCanvas2.SelectedDate))
 
the last two filter expressions don't give any warnings but simply don't return any records.
 
I even tried using another filter with entryDate >= DatePickerCanvas2.SelectedDate, entryDate < DateAdd( DatePickerCanvas2.SelectedDate, 1) with this showing some records but is not consistent.
 
I'm very confused. Anyone else experience this?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

WarrenBelz 154,797 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @randomsky,

My understanding is that Dataverse stores dates in UTC time, so your second exercise with adding a day is on the right track. You need to find the start of the day - try this OnChange of the Date Picker
UpdateContext( { varStart: DateAdd( Self.SelectedDate, TimeZoneOffset(Now()), TimeUnit.Minutes ) } ); UpdateContext( { varEnd: DateAdd( varStart, 24, TimeUnit.Hours ) } )

then test for the date being between these.

Please ✅ Does this answer your question 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 a Like♥.
Visit my blog Practical Power Apps LinkedIn

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Inogic 1,265 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

You can try the following formula:

Filter(
tblLedgers,
DateValue(entryDate) = DatePickerCanvas2.SelectedDate
)

Using DateValue(entryDate) removes the time portion from the Dataverse DateTime value, ensuring that the comparison is done correctly as date vs. date.

Hope this helps!

Thanks!

Inogic

Was this reply helpful? Yes No
randomsky 110 on at

Like (0)

Report
Copy link

Link copied!

Thanks @WarrenBelz, that fixed it and is consistent.

Thanks @Inogic, this seems to work but gives a delegation warning so @WarrenBelz 's solution is preferred.

Was this reply helpful? Yes No