Odata filter to show changes in SharePoint list for last 7 days

[PMac]

I've been reading all the previous solved messages to try and find the right expression but I can't seem to get anything to work.  I want to generate an email, on a weekly basis, that will show items from a SharePoint list that have been 'completed' in the last 7 days.

 I keep getting:

{

"status": 400,

"message": "The expression \"Completed_x0020_Submission_x0020_Date eq 2019-12-27T12:44:25.0000000Z\" is not valid.\r\nclientRequestId: 3dd8bd3f-f18a-4dc7-a343-939132d237a7\r\nserviceRequestId: 3dd8bd3f-f18a-4dc7-a343-939132d237a7"

And I'm using the expression:

formatdatetime(addDays(utcNow('yyyy-MM-ddTHH:mm:ssZ'),-7,'yyyy-MM-ddTHH:mm:ssZ'))

I know I'm missing something obvious but I just can't work out what it is.  Any help would be appreciated!

[ScottShearer]

@PMac 

I suggest a number of changes:

1.  You need to put single quotes around your formatDateTime() expression - this is why you are getting the error
2.  Change your date format to yyyy-MM-dd
3. Change eq to ge for greater than or equal to

Completed_x0020_Submission_x0020_Date ge 'YOUR EXPRESSION HERE'

formatDateTime(utcNow(),'yyyy-MM-dd'

Let me know if you need an example.

[PMac]

@ScottShearer  - I'm now getting the following error:

{

"status": 400,

"message": "Column 'Completed_Submission_Date' does not exist. It may have been deleted by another user.\r\nclientRequestId: 0b732267-0f75-441b-a4db-60c6d7c613cf\r\nserviceRequestId: 0b732267-0f75-441b-a4db-60c6d7c613cf"

}

The column is definitely there and this is the name (copied and pasted from the SharePoint list).  I even removed the spaces and put underscores in case that was impacting things.

I've changed the expression to:

formatdatetime(addDays(utcNow('yyyy-MM-dd'),-7))

And updated the filter query field as suggested:

[ScottShearer · Accepted solution]

@PMac 

The error indicates that Flow doesn't like the column name that you are using.  You can check the column name in two ways:

1)  Run your Flow without the filter and take a look at the output of the Get items action.  You need to refer to the column in exactly the same way as it is shown in the output.

2)  Go to the list settings and click on the column name.   Take a look at the URL - the internal column name will be at the end of the URL after Field=

Please let me know how it goes.

[PMac]

@ScottShearer you're my hero!  Now I just need to work out why it's showing July to Sept rather than the last 7 days.  The flow was set up in August but that shouldn't have an effect on the expression should it?

[ScottShearer]

@PMac 

When the Flow was created should have no impact.  Can you please post the filter and expression that you are using?

[PMac]

@ScottShearer 

Filter:  CompletedSubmissionDate ge '@{formatdatetime(addDays(utcNow('yyyy-MM-dd'),-7))}'

I managed to get August to now by use le, but that doesn't narrow it down to the last 7 days unfortunately.

[ScottShearer]

@PMac 

Can you post screen shots of the Get Items filter and of the expression from the expression window?

[PMac]

@ScottShearer  I can't capture it all in one go but here go you:

[ScottShearer]

@PMac 

Please change the expression to the following and give it a try:

addDays(utcNow(),-7,'yyyy-MM-dd')

formatDateTime() is not needed.

[PMac]

@ScottShearer unfortunately that didn't change anything.  It's still showing approx 30 rows from July to September last year.

[ScottShearer]

@PMac 

Not sure what the issue might be. The way to troubleshoot is to take a look at a run - can you post a screen shot of the Get Items action from your last run?

[PMac]

@ScottShearer this is what it is producing:

[PMac]

@ScottShearer  I think I may have solved it.  The column that is being used to pull the data from needs to be a date/time column rather than a single text column.  I'm still testing but it's looking good so far.