cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ArtW
Level: Powered On

Create FLow that send email based on calculated date?

I'm adding this to a couple forums becasue I cannot find anyone that can answer this after 2 months.  I have a SharePoint page and need to create a flow using 3 columns.  The columns are POP Start (Period of Performance) / ITR Report Preparation / Job Manager.

I created a Recurrance Flow using a fixed date, but when calculated the flow fails and no viable solution has been presented yet. Can SOMEONE please show me a true tested flow with the following that is successful?

 

Here are the 3 columns as they would appear in SharePoint:  The flow needs to read the POP Start date, calculate the ITR date which is 820 days after PoP and then send an email to the job manager.

SharePoint Columns.jpg

 

 

Below is how each column is set up: Pop Start is  Date Column / ITR Preparation is a "Calculated column" with the calculation as shown "=[Pop start]+820" and job manager email is "person or group"3 columns.jpg

 

HERE IS MY STARTING POINT that when the ITR column was fixed and no caluclated it worked. Column nasmes slightly different but concept the same.

successful flow.jpg

 

20 REPLIES 20
Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

Can I ask a couple more questions.

1) When should the email be sent to the manager? On the Pop date, the ITR date or something else?

2) What needs to be in the email?

 

I would think this would be a Flow that would use a recurrence to run each day, then evaluate the list items to see if any match todays date and if they do then send out the email. But I suspect I'm missing something about the scenario.  



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

The email is sent when the ITR date matches todays date. You are correct, the flow will run daily and when the ITR date matches it will go to the email and send an email to the manager listed on that line.  When scanning he flow, there are no errors or failures. After running it for test, it stops right after looking at the ITR date and skips he the email. 

Thats why I'm showing how I have the columns set as well. Is it the way I setup the column?

To me a date is a date and no matter if its fixed or calculated the flow should run, but for some reason it wont run the calculated date flow.

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

Let me try to do a quick prototype in the Lab and see if I can get this to work.  Doing filters and comparisons with DateTime values is always tricky since even though they are set for Date only when you retireve them they include both date and time.  So you usually have to adjust them using DateFormatValue to get them in a known format so you are comparing Apples to Apples and not Apples to Oranges.



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

Re: Create FLow that send email based on calculated date?

OK, Here's what I would like you to try.  Initialize two string variables at the start of your Flow, one for UTCNow and one for ITR.

Configure the UTCNow variable using the following:

formatDateTime(utcNow(),'d')

That will store the date portion of UTCNow as a short date string like '7/9/2019' instead of the full timespan that is returned.

Then inside the loop just before your comparison do the same thing for the calculated ITR column.  That's a little tougher since it doesn't show up in dynamic content.  Use this

formatDateTime(items('Apply_to_each')?['ITR_x0020_Preparation'],'d')

That will get both dates stored as just a simple date without any time component.

Then compare the two variable values.  If the ITR date is today then they will match and the 'Yes' branch will be invoked.

 

The problem develops because whenever comparing dates you are always comparing DateTime values and the Time portion of one is rarely equal to the Time portion of the other. UTCNow returns a time value that is 00:00:00 while the calculated column will be the time whenthe record was calculated.  Dates may match but date time won't.

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



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

I'll give it a shot. I'm not quite sure how it should look so I will build it and email it.

ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

A new problem has risen. I used the following flow and came up with email skipped.

Here is the flow I created:

Jul 8.jpg

 

And this is the result of the flow. Ran good but skipped the email?

PStorik flow.jpg

 

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

Why do you have two lines in the condition?  You should be comparing the formatted UTCNow with the formatted ITR column.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

I'm very new and a novice at FLOW, I wasnt sure what you were trying to show in your post above.

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

I was saying that your condition should look like this

screenshot.png

with one of the formatDateTime formulas on each side.  YOu should not have an AND



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

Sir,

Thank you so much for the assistance. I have worked with several people including Jon Levesque who could not solve this mystery of sending an email based on a calculated date. Everyone tried to get me to use multiple "Compose" in the flow and that wasnt working.

The only thing happening now is I receive 3 emails instead of 1, any ideas?

 

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

That would be because you are sending the email inside the loop.  If you only want one email then you need to aggregate the content you want in the email inside the loop and generate the actual email outside the loop.  The specifics of that depend on what you want in the one aggregated email.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

Ok, here we go again; Inside / outside?  I just want one email and the flow to read the job maanger email address, the subject and a brief message of instructions.

 

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

If you have multiple lines in the list that have today's date for the ITR then you will get multiple emails.  Its processing things one line at a time. If you only want to send one email out to a manager if their name is duplicated in the list for two or more ITRs due on the same day then the logic gets much more complex.  When you say you got three emails were they all for the same manager for the same ITR report or were there three ITR reports due today?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

There is only one line with todays date but I have my email listed 3 times (one for different dates). 

Here is the current site page: You see the pop start /  the one ITR date matching today /  my email listed 3 imes.

 I removed one email so only 2 show and I received 2 emails.  Why is it reading the emails and not the line?

SP page.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How email is set up in flow:

EMAIL.jpg

 

 

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

If you are getting emails for every line that has your email that would suggest that the comparison between the ITR date and today isn't functioning correctly.  Look at the last run and see if its filtering on the dates correctly.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

Looks like I had an error but not sure how it happened. When I built the Flow with your suggestion of entering the two Formats: formatDateTime(utcNow(),'d') and  formatDateTime(items('Apply_to_each')?['ITR_x0020_Preparation'],'d')

Some how I ended up with this formatDateTime(utcNow(),'d') on both sides and maybe thats why it wasnt reading the ITR date correctly. When I corrected the error and put the other format in place, the Flow failed again.

Failure.jpg

 

 

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

That would do it, because that would always evaluate to True no matter what the value of the ITR date was.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
ArtW
Level: Powered On

Re: Create FLow that send email based on calculated date?

Well sir you gave a glimmer of hope that this can be done. Do you have any other possible suggestions? I post the entire objective with exactly what I am trying to do. Why is this so hard? Seems like a simple task to just read a date and send an email?

 


@ArtW wrote:

Looks like I had an error but not sure how it happened. When I built the Flow with your suggestion of entering the two Formats: formatDateTime(utcNow(),'d') and  formatDateTime(items('Apply_to_each')?['ITR_x0020_Preparation'],'d')

Some how I ended up with this formatDateTime(utcNow(),'d') on both sides and maybe thats why it wasnt reading the ITR date correctly. When I corrected the error and put the other format in place, the Flow failed again.

Failure.jpg

 

 


 

Dual Super User
Dual Super User

Re: Create FLow that send email based on calculated date?

@ArtW The Flow you sent me works as designed when I point it at my list. It generates one email per address in the job manager field for each record where the ITR date matches the current date. I did notice that inside your Apply to each 2  loop that the email address was Mail.  In mine its Job manager email which is the name of the column running the Loop. Are you sure you picked the right field from dynamic content to address the mail?

 

Otherwise everything works.



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

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

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!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (4,793)