cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

SP List - Get Items by date range? Please help!

Hello all, Sorry if my query seems amateur but I'm new to the flow world! I'm trying to use a flow to "get items" from a SP list. My list has a column called "expiration dates" which range from years ago, to years in the future. The list is hundreds of rows long and is constantly being updated via forms and sometimes manual addition. I need to somehow extract the people who's expiration dates are within (90 days from now) and (180 days from now), and have them as a "Value" which I can then use for further tasks. Hoping it's an expression I can use within Get Items? Although I understand there may have to be a bit of a workaround here including variables, etc... Many Thanks in advance
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: SP List - Get Items by date range? Please help!

Hi @MotionMan,

 

You could configure filter criteria in Filter Query to filter the eligible items.

Use the addDays() function to add 90 and 180 to the date of utcNow() and check if the value of the expiration dates field is greater than and less than these two values.

Image reference:

71.PNG

70.PNG

The expression configured inside single quotes:

addDays(utcNow(),90)

addDays(utcNow(),180)

Please take a try.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Helper V
Helper V

Re: SP List - Get Items by date range? Please help!

@MotionMan

 

This seems something like what I have just done in the last week.

 

Okay so I created in my list a calculated column (Contacted) and set that at in my case as expiry date minus 14 days.  Then I ran my flow with a condition referencing that item (Contacted).  My trigger for the flow was a daily occurrence and everything works well. i.e. it sends out an email to the company 14 days before their certificate expirers.  See below:

 

flow pl.png

 So the other steps are as follows:

contacted.png

 

 

The contacted field is the date to contact the supplier for a new certifcate.

 

The expression is:

 

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

 

Please note the dates will allways be in the above fromat, if you want to say have 25 December 2018 or say 25/12/2018 in an email then you will have to find out how to do this as I cannot currently help with this.

 

I don’t understand why you need 90 and 180 days, could you just not pick say 90 and then use my suggestion above.   If you have to have both 90 and 180 then I need to know more to be able to help but as I am a relative newbie myself I may have to refer to others.

 

Regards,

 

Andrew

 

 

 

 

 

 

 

 

Highlighted
Frequent Visitor

Re: SP List - Get Items by date range? Please help!

Hi Andrew thanks for your advice but the nature of my task reuquires me to grab any body whose expiration dates falls within (90 days from now) and (180 days from now).

 

For example search (value) for [90 days from now], [91 days from now], [92 days from now]... up to 180 days to grab everybodies expiration dates from within that period of three months - which starts three months from current date. 

 

Further uses for this data will be to populate a calendar.

 

Thanks,

Ollie

 

 

Highlighted
Frequent Visitor

Re: SP List - Get Items by date range? Please help!

Decided now, the best way for me to do this is actually going to be by selecting months.

 

So want to select all dates in January, Feb, etc. - If I need 12 seperate flows then so be it. Still don't know how to do it though! 

 

Just need the "Get Items" value to = list of everyone with expiration dates in that month.

 

Thanks all!

Highlighted
Community Support
Community Support

Re: SP List - Get Items by date range? Please help!

Hi @MotionMan,

 

You could configure filter criteria in Filter Query to filter the eligible items.

Use the addDays() function to add 90 and 180 to the date of utcNow() and check if the value of the expiration dates field is greater than and less than these two values.

Image reference:

71.PNG

70.PNG

The expression configured inside single quotes:

addDays(utcNow(),90)

addDays(utcNow(),180)

Please take a try.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper V
Helper V

Re: SP List - Get Items by date range? Please help!

@v-bacao-msft

 

Can you explain X0020, I hope to respond with more later on today on this so if you can hold off for four hours from now that would be good.

Highlighted
Super User III
Super User III

Re: SP List - Get Items by date range? Please help!

@WDRC:

 

When referencing SharePoint columns in ODATA filters, Flow uses the SharePoint internal column name.  Each column has two names - a display name and an internal name.  The display name is what you see in a SharePoint list form and in Flow Dynamic properties.  The internal name may/may not be the same as the display name.  Internal names cannot have spaces.  The _x0020_ is what is used in the internal name rather than a space.

 

To see a columns internal name, go to the list settings and click on a column name.  In the URL, you will see &Field= at the end.  The internal name is what follows &Field=

 

Scott

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

Scott
Highlighted
Helper V
Helper V

Re: SP List - Get Items by date range? Please help!

@ScottShearer

 

So I found the following on a Vlog

 

formatdatetime(triggerBody()?['Publish_x0020_Date'],'dddd MM/dd/yyyy')

 

My column is is called Cert Expiry the statement after the Field is =Cert%5Fx0020%5FExpiry so I input the below:

 

formatdatetime(triggerBody()?['Cert_x0020_Expiry'],'dd/MMMM/yyyy')
 
The x0020_Expiry turned orange so it looks as though it has picked this up from the SP list.  I am wanting to get the format of the date to apear in an email later on in the flow to spit out say 25 December 2018.  But I get an error of the following:
 
Unable to process template language expressions in action 'Date_into_UK_format' inputs at line '1' and column '2532': '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.'.

 

It looks as though I have not input it quite right, so what mistake have I made.  It is just these little things are nice to do that take so much time to make work.

 

Regards,

 

Andrew

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (6,930)