Answered

Can not filter SharePoint list on date using equals but greater/less than works

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Earlier I was trying to build a feature to prevent a user from entering more than one item on the same date. To do this, I was using a filter against my SharePoint data source date field where the date indicated in the form is compared in order to give them access to the submit button. The equation will resolve to a true/false.

For some reason, I could not use the equals operator like so:

Does not work

CountRows(
 Filter(TimeStudy,
 EntryDate = DataCardValue10.SelectedDate
 )
) <> 0

It shows the error:

The requested operation is invalid. Server Response: The expression "Date eq 2019-10-24" is not valid.

With a bit of creativity, I tried doing a comparison instead and achieved the desired result using something much uglier.

Works fine

CountRows(
 Filter(TimeStudy,
 EntryDate < DateAdd(DataCardValue10.SelectedDate, 1),
 EntryDate > DateAdd(DataCardValue10.SelectedDate, -1)
 )
) <> 0

Can anyone provide some insight on what might be happening here? I read some other threads about limitations and workarounds with SharePoint lists. Is this realated? None of the date fields have a time associated with them so I don't think that's the issue.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

Pstork1 69,485 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
This is a common issue. The problem is that Dates, even Date only fields, are always Date and Time values. So if you are trying to check equals then you will almost never get a match on the time portion. To use equals you have to filter out the time portion of both sides of the equals and compare just the date strings. Like this

CountRows( Filter(TimeStudy, Text(EntryDate, ShortDate) = Text(DataCardValue10.SelectedDate, ShortDate) ) ) <> 0

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you for the explination. I had tried a similar approach but guess I didn't get the syntax correct.

Was this reply helpful? Yes No
vwyankee 360 on at

Like (0)

Report
Copy link

Link copied!
In case someone else comes by this like me and it didn't quite give the solution... I had a similar scenario, but this didn't work for me as it results in a delegation warning. Whether I was using CountRows or LookUp operators, as soon as I put the Text operator in there the delegation warning would show.

In my scenario the user selects a date and an employee and we want to prevent more than one entry for the same date with the same employee selected. Going with a similar approach @Anonymous listed that worked for him initially I was able to get the result I needed. I'm creating two variables and then comparing them against each other. I put this in the OnChange property of both my date picker and employee combo box.

UpdateContext({varDateSelected: Text(ddDate.SelectedDate, ShortDate), varSPDate: Text(LookUp(SPList, Date>ddDate.SelectedDate-1 And Date<ddDate.SelectedDate+1 And Employee=cbEmpName.Selected.Employee).Date, ShortDate)}); If(varDateSelected = varSPDate, UpdateContext({varDuplicateFound: true}), UpdateContext({varDuplicateFound: false}))

I then set the display mode of the save/submit button based on the variable varDuplicateFound.

Was this reply helpful? Yes No