Create FLow that send email based on calculated date?

[ArtW]

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.

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"

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.

[Pstork1]

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.  

[ArtW]

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.

[Pstork1]

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.

[Pstork1]

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.

[ArtW]

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

[ArtW]

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

Here is the flow I created:

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

[Pstork1]

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

[ArtW]

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

[Pstork1]

I was saying that your condition should look like this

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

[ArtW]

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?

[Pstork1]

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.

[ArtW]

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.

[Pstork1]

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?

[ArtW]

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?

How email is set up in flow:

[Pstork1]

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.

[ArtW]

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.

[Pstork1]

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

[ArtW]

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.

---

[Pstork1]

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