ODATA filter query

[ConanMcDonnell]

Hi

I'm very new to building Flows and I'm having some problems with a SharePoint-based Flow. Wondering if anyone can help.

We've got a requirement to evaluate a SharePoint List on a weekly basis and generate a spreadsheet which displays specific List rows based on some pretty basic criteria.

The List has items which are subject to inspection either every 6 months or a year.

The requirement is that the Flow is scheduled to run each week and build a digest of all items that are up for inspection in a month's time.

I've created a Flow which will run on a scheduled basis every week to evaluate the List and export items to a spreadsheet (although I've gone for .csv as the Flow seems to have a problem with creating .xlsx files which I can open) which is created in a SharePoint Document Library,  then send an email to an appropriate party with a link to the new digest.

As this is a weekly digest, I am trying to filter the List Items based on any Items where the inspection date is greater than (today plus 20 days) and less than (today plus 32 days). Obviously there will be some overlap between successive digests in terms of results but that's fine - we're not looking for perfect discreet data sets.

The issue is with creating the filter. You can't filter these items using an existing List View - which is annoying, so I have to use an ODATA filter. I'm struggling with the correct syntax.

Can anyone tell me how to build an appropriate filter where the field in question is: Field=Next_x0020_inspection_x0020_date

which is a Date field

and the criteria are that the value is greater than (today+20) and less than (today+32) ?

Should I be using an expression to format the field in any way for this to work?

Please let me know if further information is required.

Many thanks for any help you can provide.

Kind regards

Conan McDonnell 

[ScottShearer · Accepted solution]

@ConanMcDonnell 

Your OData filter should look like this:

Next_x0020_inspection_x0020_date gt 'addDays(utcNow(),20,'yyyy-MM-dd')' and Next_x0020_inspection_x0020_date lt 'addDays(utcNow(),32,'yyyy-MM-dd')'

Here is an example of what it might look like (I use MyDate as the date column):

The addDays expression must be entered in the expression window and the single quotes are important.

There are single quotes around the expression.

[ConanMcDonnell]

Hi Scott

Many many thanks for this - I think I got there with the syntax after following your example.

Unfortunately I've come unstuck as an error message is now informing me the filter won't work on a calculated column!

Unless you know a workaround, I'll maybe look at automating the calculation of the inspection date with another Flow, rather than a calculated column.

Thanks again for your speedy response and help.

Kind regards

Conan

[ScottShearer]

@ConanMcDonnell 

Unfortunately you are correct about the calculated column - you'll need to use a Flow to update that column instead of using a calculated column.

[ConanMcDonnell]

Hi Scott

Yes, once I saw that the calculated column was causing the issue I used a Flow to generate a new Inspection date.

It's all working fine now.

Thanks again for your help with the filter - it saved me a great deal of time.

Kind regards

Conan