cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmistry
Level 8

Flow to set a calculated date field to null and send an email when a due date approaches

Hi,

 

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.

6 REPLIES 6
Super User
Super User

Re: Flow to set a calculated date field to null and send an email when a due date approaches

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

 

dmistry
Level 8

Re: Flow to set a calculated date field to null and send an email when a due date approaches

Hi ErichH,

 

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. 

Super User
Super User

Re: Flow to set a calculated date field to null and send an email when a due date approaches

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

VacationEntitlement.PNG

 

 

the sharepoint list looks like

 

VacationEntitlement_List.PNG

 

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 

dmistry
Level 8

Re: Flow to set a calculated date field to null and send an email when a due date approaches

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.

Super User
Super User

Re: Flow to set a calculated date field to null and send an email when a due date approaches

@dmistry

 

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

utcNow()

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

formatDateTime(timestamp, format)

Returns a string in date format

startOfHour(timestamp, format)

Returns the start of the hour to a string timestamp passed in

startOfDay(timestamp, format)

Returns the start of the day to a string timestamp passed in

startOfMonth(timestamp, format)

Returns the start of the month of a string timestamp

dayOfWeek(timestamp)

Returns the day of week component of a string timestamp

dayOfMonth(timestamp)

Returns the day of month component of a string timestamp

dayOfYear(timestamp)

Returns the day of year component of a string timestamp

ticks(timestamp)

Returns the number of ticks (100 nanoseconds interval) since 1 January 1601 00:00:00 UT of a string timestamp

dmistry
Level 8

Re: Flow to set a calculated date field to null and send an email when a due date approaches

Hi ErichH,

 

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

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 420 members 4,541 guests
Please welcome our newest community members: