Sending Email when Date Meet Condition

[pnguyen540]

Hi everyone,

I am building a flow that automatically emails the form submitter if the 'Date of Event' is today.

I followed the following discussions: 

• https://powerusers.microsoft.com/t5/Building-Flows/Send-email-based-on-email-and-date-given-in-excel...
• https://powerusers.microsoft.com/t5/Building-Flows/Send-email-based-on-date-in-Excel/m-p/554926

Here is my flow:

In the Filter Array, the Advance Mode shows @equals(addDays('1899-12-30', int(item()?['Date of Event'])), utcNow('yyyy-MM-dd'))

I created a test file with two rows of records, with the 'Date of Event' as today and another as next year. The flow ran successfully, but no email was sent out. 

What can I do to correct the flow? Thank you so much in advance!

[FLMike]

Hi @pnguyen540 

Thank you so much that it shows that, but here is what i would do first.

above the condition add a Compose Action

In the Expression put

//I want to see this

The output of your addDaysExpression and then a - and then utcNow('yyyy-MM-dd') 

So essentially

addDays(INSERT YOUR APPLY TO EACH DATE HERE, int(item()?['Date of Event'])) - utcNow('yyyy-MM-dd')

So that you can see the actual values being compared. I believe they dont match, and its possibly the utcNow versus the date you are using and or, the int value you are getting from Date of Event

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[pnguyen540]

Hi @FLMike 

I apologize that I am slow to understand Power Automate. Can you explain what you mean by:

• above the condition add a Compose Action: what condition box are you mentioning? Is it filter array?
• INSERT YOUR APPLY TO EACH DATE HERE: is it '1899-12-30'? I tried to add the following to the Compose Action expression, that I put before the Filter Array: addDays('1899-12-30', int(item()?['Date of Event'])) - utcNow('yyyy-MM-dd') and it gave me the error. I believe I did not thoroughly understand your instruction.

The date column in the Excel spreadsheet that I am pulling is formatted as YYYY-MM-DD.

Thank you again for your help!

[FLMike]

@pnguyen540 

No you did fine, I needed to be a little clearer.

Can you show me the actual Action you added so I can see what was typed in it, I think I know the issue and its on me

Also, why do you put 1899 in there? Is that really the start date you want to add days too?

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[pnguyen540]

Hi @FLMike 

Here is the screenshot of my Compose action and the error I received. My input in the expression was 

addDays('2020-01-31', int(item()?['Date of Event'])) - utcNow('yyyy-MM-dd') 

Thank you again!

[FLMike]

Hi, @pnguyen540 

Sorry yeah thats why it was an error and thats on me to be more clear. You cannot type an entire expression(like this one) at one time. I know it sounds silly but you cant

can you put this in

concat(
string(addDays('2020-01-31', int(item()?['Date of Event']))),
'-',
utcNow('yyyy-MM-dd') 
)

I do have a couple of questions though

1. Is Date of Event an Actual Date? or a Number?

2. If you are trying to compare the Date of Event to Today, why are you adding days to it? That would seem to me to make it never work and if its a Date, not a number, doing #1 will definitely mess it up.

3. And where does the first Date come from the 2020-01-31 ??? 

I guess I just dont understand since you should have a date stored, and you just want to compare that to todays date.. whats all the other stuff fore?

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[pnguyen540]

Hi @FLMike 

I appreciate your patience. To clarify, I am trying to compute the following: if the date is equal to today, email the person on the list.

As for your questions:

1. The 'Date of Event' is formatted as date YYYY-MM-DD in Excel
2. My apology if it is not the correct way to approach. I simply follow the other two threads I found that are similar to what I try to accomplish.
3. That date is from another example I try to copy 😞 I saw some people use 1899-12-30, while some use more recent dates in the expression. I wanted to see if it made any difference.

What would be your suggestion for a flow that can send an email for those with dates matched today?

[pnguyen540]

After running the instruction you provided, I received this:

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[FLMike]

Hi @pnguyen540 

No worries at all, but that is why you are having issues and it never triggers.

Ok let me type what we will do instead.

[FLMike]

Hi @pnguyen540 

Yeah thats exactly what I figured would happen because you are trying to turn a Date into an INT and thats not possible

One second Ill type what we will change

[FLMike]

@pnguyen540 

OK sorry for the delay I had to eat.

I want you to change your Filter to the Following:

Here is your Original

I want you to change it by doing this

1. Delete the add days please so there is nothing in that box at all

2. Then I want you to click, so the Dynamic Properties/Expression window pops up

3. You should be able to find the Date of Event Property under your List rows present in a table, click it and add that there

4. make sure that in the Right side that you expression says

utcNow('yyyy/MM/dd')

I created this exactly field in my Excel and did all this just to validate it for you. 

I list my rows

I then use my filter, Just like I asked you to do, so it will look just like mine

My Compose then does a length(value) to tell me how many rows it found

Since I added a row with todays date, it returned 1 row

If you do this it will work for you

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[pnguyen540]

Hi @FLMike 

So I redid the flow based on your instructions. Here is what I have.

It ran successfully, but no email was sent. So I went into the particular run to see what happened. I noticed that the Filter Array Output did not have the date that matched today. And the date is displayed not in the format yyyy-MM-dd as I formatted in Excel.

What could be the next steps to proceed? Thanks again!

[FLMike]

Hey @pnguyen540 

Those aren't dates. Where did this data come from? That will never work. 

[pnguyen540]

Hi @FLMike 

The data comes from a spreadsheet we collected for training events. Here is what it looks like in my Excel spreadsheet.

Is there a way to convert it to be properly formatted for the flow?

[FLMike · Accepted solution]

Hmm @pnguyen540  

There is something not right. Your spreadsheet shows the Date

But the filter gave a number. Please explain exactly how you created the file.

Wait.. is that some kind of calculated column? I bet it is.. and I've been yanking my hair out for nadda. Thats not a date is it? Not really. Thats why it shows a number..

Here is what I would ask.

Open your Excel.

Left Click on the Column Header on the column BEFORE the Date of Event

Then right click the header (it should be highlighted) and then choose Insert a Column.

That column should have like a name of Column1 or something, Please just remember what it is but leave it as is.

In the new fields I want you to type 10/06/2023 for both rows (or however many rows you have).

Then I want you to highlight those new values (just the new date column), and change the format to a Date, but I want you to pick the following

Your dates should look like mine and the other column

Now, go back into your Flow.

-I need you to go to the List rows of a Table.

-And I need you to go to the Table selector and click the down error. 

-Then purposely choose the Enter a Custom Value option.

-Immediately after, at the end of the line will be an X click it

-This will take you out of Custom mode and should list the table as selectable

-Select the Table again.

-Now our flow will understand the new column.

In your Filter, I want you to remove the Date of Event column and add the new Column1. It should look like it this. Ignore any dates in the Date of Event Column as we wont be using that Column.

In the Filter you also need to change the utcNow() expression because the format is wrong, since the file has - in the date not /

change this

utcNow('yyyy/MM/dd')

to

utcNow('yyyy-MM-dd')

If you do all that, it should work. 

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey