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

[MotionMan]

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

[WDRC]

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

 So the other steps are as follows:

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

[MotionMan]

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

[MotionMan]

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!

[v-bacao-msft · Accepted solution]

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:

The expression configured inside single quotes:

addDays(utcNow(),90)

addDays(utcNow(),180)

Please take a try.

Best Regards,

Barry

[WDRC]

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

[ScottShearer]

@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

[WDRC]

@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

[pakalolo13]

Could you show the code in your get items. I have ran this flow successfully in my flow but it is not producing an email and i think it is because my query may not be getting the correct results.  thanks in advance if you can help here!