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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,343)