I have two date fields in a SharePoint list. First is called the Holiday Expiration Date (calculated date field), it shows a date value when a new item is added. There is another calculated column called 'Holiday Accrual Credit' (calculated number field).
I would like to build a flow that when runs it should set the Holiday Accrual Credit field to zero if Holiday expiration date is reached. So let's say for example if Holiday Accrual Credit Field value is 2.5 and Holiday expiration date field value is 8/21/2018 then it shoud send an email to the person who created that list entry on 8/21 to inform that your credit will expire today and then set the Accrual credit field value to 0 as well post email sent.
Please help me out building this scenario using Flow, thanks in advance.
Here a description how I would go about it
the SharePoint list contains only “normal” fields (no calculated ones) and I would use 2 flows
> first one triggered by the creation of a new row, it calculates the holiday credit and the expiration date and updates the recently added row.
> second one runs daily (or weekly), gets all row with credits > 0 and checks whether the credit has expired that is the credit date is less than the expression utcnow(). If yes set the holiday credit to 0
hope this helps
Thanks for the info. I already have the first flow that's accounting for things you mentioned in your post. Howver it would be more useful if you can share a screenshot of the second flow, would really appreciate it. Also how do I send an email to user prior to 30 days of expiration date, letting them know that their credit will expire in 30 days.
here is how a initial version could look like.
I would use 2 almost identical "daily flows" one that sends prewarning and one that expires entitlements
the sharepoint list looks like
in the first compose step calcuate the date against which to compare the content of "ExpirationDate" against
>> today (UTCNow) for actual deletion of entitlement
>> in x days (adddays(UTCNow(), x) for the prewarning
in the second compose step combine output of above with the rest of the ODATA filter string
Its output is used in "get items" action in the advanced options
at the end the list item gets updated the QuantityActual set to 0
Thanks a lot for the help.
Quick question though, I am not sure how the syntax works in specifying the formula. woudl you be able to tell me how can i compare something like Due Date vs Todays date? Thanks in advance. i just want to make sure I follow the correct syntax for flow.
there are several powerful expressions (functions) available for time difference calculations. See below list
How calculate when to send reminder:
1 - add 30 days to today - use addDays(timestamp, days, format?) in form of addDays(utcnow(),30,'yyyy-MM-dd')
then compare that to the stored value of experation date, which you can reformat using expression formatDateTime(Expirationdate, 'yyyy-MM-dd')
a reminder is due when addDays expression result is bigger than the expiration date.
watch out to send the reminder only once, either by having a LastAction column that need to be ina given status or by checking that the exparation date is smaller than today + 30 AND larger than today + 29
hope this helps
Date and time
Returns the current timestamp as a string
getFutureTime(interval, timeUnit, format?)
Returns a timestamp that is the current time plus the specified time interval.
getPastTime(interval, timeUnit, format?)
Returns a timestamp that is the current time minus the specified time interval.
addToTime(timestamp, interval, timeUnit, format?)
Adds an integer number of a specified unit of time to a string timestamp passed in
subtractFromTime(timestamp, interval, timeUnit, format?)
Subtracts an integer number of a specified unit of time from a string timestamp passed in
addSeconds(timestamp, seconds, format?)
Adds an integer number of seconds to a string timestamp passed in
addMinutes(timestamp, minutes, format?)
Adds an integer number of minutes to a string timestamp passed in
addHours(timestamp, hours, format?)
Adds an integer number of hours to a string timestamp passed in
addDays(timestamp, days, format?)
Adds an integer number of days to a string timestamp passed in
convertTimeZone(timestamp, sourceTimeZone, destinationTimeZone, format?)
Converts a string timestamp passed in from a source time zone to a target time zone
convertToUtc(timestamp, sourceTimeZone, format?)
Converts a string timestamp passed in from a source time zone to UTC
convertFromUtc(timestamp, destinationTimeZone, format?)
Converts a string timestamp passed in from a UTC to a target time zone
Returns a string in date format
Returns the start of the hour to a string timestamp passed in
Returns the start of the day to a string timestamp passed in
Returns the start of the month of a string timestamp
Returns the day of week component of a string timestamp
Returns the day of month component of a string timestamp
Returns the day of year component of a string timestamp
Returns the number of ticks (100 nanoseconds interval) since 1 January 1601 00:00:00 UT of a string timestamp
Would you be able to share the expanded view screenshot of the flow so that I can get an in depth idea, not good at flows but trying to get there.I just want to see what you configured in those actions and conditons
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Look out for new contribution recognition badges coming SOON!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
The top training and networking event across the globe for Microsoft Business Applications