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

SharePoint Get Items only when Date EQ Today

Hello everyone,

I am trying to build a Flow that will create a report containing only the items in a list where the Date is today.

Users complete an MS Form to notify Security when a visitor is coming on site with a vehicle. This creates an entry in a SP listt.

 

The list is  items that represent vehicles due on site. One of the columns is simply the date the vehicle is expected. It is provided from a Form date picker.

I attach two screen shots.

The Date In column is just there to show the date in UK format the actual Date column is hidden.

I need to use Get Items to get the items where the Date equals Today.

This Flow will be a scheduled flow which will run early every morning and send a report to Security Staff so they know which vehicles are expected today.

 

My question is how do I express Todays date so that I can filter my Get Items query?

I guess I am on the right lines of initializing a variable? But Im not sure how to express it.

The trigger in my example is Manual, it will be changed to schedules once I have got this to work.

 

The listThe listThe FlowThe Flow

 

 

 

 

8 REPLIES 8
burgett94
Resolver II
Resolver II

Hello @AdamInLondon 

 

I believe you would need to use a Filter Array then you can format the SharePoint Date Column so that it looks like:

 

formatDateTime(outputs('Get_items')?[DateColumn],'yyyy-MM-dd')

 

is equal to

 

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

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more

Thanks @burgett94 

OK so its:

 

  • Trigger
  • Get Items
  • Filter array

The Date column is in format yyyy-MM-dd

 

so how do i say "only get items where Date is equal to Today"

I can see an Expression "utcNow()" but that will return a timestamp for now not the value of today's date on its own...

 

Permits 3.JPG

 

burgett94
Resolver II
Resolver II

So where you have Date you need to put the below expression like in the screenshot

 

formatDateTime(outputs('Get_items')?[Date],'yyyy-MM-dd')

 

burgett94_0-1622652330289.png

 

 

Then after "is equal to" you will do the same, just with the below expression

 

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

 

 

burgett94_1-1622652429832.png

 

 

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more

Thanks, im beginning to understand how it should work but I get an error saying "the expression is invalid"

I have copied and pasted the expression and manually typed it, same result. 

I have studied the expression and it looks OK to me but im pretty new to this.

 

Screenshot 2021-06-03 at 10.40.31.png

burgett94
Resolver II
Resolver II

Sorry that's on me! It should be as below

formatDateTime(outputs('Get_items')?['Date'],'yyyy-MM-dd')

 

It was missing the apostrophes either side of Date

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more

Ah of course! pesky quote marks... Now saving without error message, thanks!

AdamInLondon
Helper II
Helper II

Im getting this now:

 

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(outputs('Get_items')?['Date'], 'yyyy-MM-dd'), formatDateTime(utcnow(), 'yyyy-MM-dd'))' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

 

The Date value seems to be correct, here is an example:

AdamInLondon_0-1623058879362.png

 

 

 

bburgett
New Member

Sorry someone in HQ decided we can't use work accounts on here.

 

In your Get Items within the Filter Query enter Date ne null so that it will only return items that have a Date value assigned to them. This will mean you won't have this error as it won't find any null values.

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.

Top Solution Authors
Users online (1,974)