cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PMac
Helper II
Helper II

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

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"
 
 

Odata filter for last 7 days.PNG

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!
1 ACCEPTED SOLUTION

Accepted Solutions

@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.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

13 REPLIES 13
ScottShearer
Super User
Super User

@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'

 

FormatDateTime.jpg

 

Let me know if you need an example.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

@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:
Odata filter for last 7 days 2.PNG
 

@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.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

@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?

@PMac 

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

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

@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.

 

@PMac 

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

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

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

 

Odata filter for last 7 days 3.PNGOdata filter for last 7 days 4.PNG

@PMac 

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

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

 

formatDateTime() is not needed.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

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

@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?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

@ScottShearer this is what it is producing:

 

Odata output for last 7 days.PNG

@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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,734)