I am reading in data from a sharepoint list and I need to filter the data on 3 criteria:
1. Draft is false, AND
2. Date Posted is greater than or equal to today - 7 days, OR
3. Due Date is less than or equal to today + 14 days.
Conditions 2 and 3 are nested - so the primary criteria is Draft is equal to false. Then, if the item meets criteria 2 OR 3, then it should be returned. When I test each condition independently, or use "and" for all three criteria, it works fine.
When I use a formula expression to determine the ranges:
(Draft eq 'false') and ((Date_x0020_Posted ge '@{addDays(utcNow('yyyy-MM-dd'), -7)}') or (Due_x0020_Date le '@{addDays(utcNow('yyyy-MM-dd'), 14)}'))
This is what is shown in the test input results, which looks correct to me:
(Draft eq 'false') and ((Date_x0020_Posted ge '2024-01-15T00:00:00.0000000') or (Due_x0020_Date le '2024-02-05T00:00:00.0000000'))
I also tried using functions to generate the target dates and use those outputs as variables in the odata filter (these outputs are formatted as 'yyyy-MM-dd'):
(Draft eq 'false') and ((Date_x0020_Posted ge '@{outputs('FormatPost7')}') or (Due_x0020_Date le '@{outputs('FormatDue14')}'))
Testing yields this expression, which again looks correct to me:
(Draft eq 'false') and ((Date_x0020_Posted ge '2024-01-15') or (Due_x0020_Date le '2024-02-05'))
However, I have rows of data being returned that do NOT meet the criteria as written.
I have tried all of the following:
- Using 'gt' and 'lt' instead of 'ge' and 'le' - same incorrect results
- Removing the 'yyyy-MM-dd' format from the UTCnow piece of the expression to get the date
- Using data functions to reformat the date (i.e. derive the target dates and using those outputs as dynamic variables in the expression)
- Using data functions to convert UTC to my time zone, and then do reformatting of the dates
- Trying various different date formatting both in the expression as well as in the formats for variables
- Hardcoding the dates (rather than using an expression - not what is needed, but tried it to see if anything would change)
Any help would be appreciated! I have spent hours trying to figure this out and I can't figure out why this isn't working as I'd expect.
My testing yields incorrect results, with records that should fail the nested OR criteria being present in the output:
| Draft | Due Date | Date Posted | Result |
| False | 2023-12-31 | 2023-11-29 | Should FAIL, but shows up |
| False | 2024-01-05 | 2023-11-03 | Should FAIL, but shows up |
| False | 2024-01-31 | 2024-01-08 | PASS - shows up |
| False | 2024-02-29 | 2024-01-22 | PASS - shows up |
| True | 2024-01-31 | 2024-01-20 | PASS - not returned |
| True | 2023-11-29 | 2023-10-15 | PASS - not returned |
