Sending an Email based on when an Item is added to a list

[Tsamuel3]

I'm trying to build a flow in Power Automate that sends an email on the date an item is added to a SP list only if the start date (a column in the list) is a date later than the created date.

I created this filter query: Created ge '2023-02-21' and StartDate0 ge '2023-02-27' in the Get Items action.

There is a recurrence action to send at 5:00 EST. That is copied from a workflow that worked. The email is set to send to me (for testing purposes only).

I'm a novice so I know nothing about Odata or queries, etc. I looked up some queries on Google and I copied from another flow that worked. The column Start date has a '0' after it only because that's what I copied from another flow. I don't understand why that is there.

I tested and had no errors but I didn't receive an email. I don't know why the email didn't send. Any help is appreciated.

[Rhiassuring · Accepted solution]

Hi there, 

The other user had the 0 on their StartDate because that is their internal column name. You can find your internal column name by going to your list, going to settings, and clicking on the column you want to use. The internal name will appear in the URL, at the end. Mine is also StartDate0. If you have something else, edit that in the expression I give you below.

If you want this to run only if the Start Date > Created Date, you would probably use a different trigger - it doesn't need a schedule, but rather, "When an item is created".

If the goal is to send an email only, this is going to be a one-action-workflow. Our trigger and the email. Our triggers allow us to use a "trigger condition" so the workflow won't even fire if the criteria is not met.

Click on the triple ellipses on your trigger, and click Settings. 

Go to the bottom and click "+ Add" under Trigger Conditions.

Paste this:

@less(utcNow(),triggerBody()?['StartDate0'])

Click done.

It's just saying "only trigger this workflow if utcNow() (this means "what day is it right now?") is less than the Start Date that the user entered." Formulas / expressions take a bit to learn but not so bad when you get the hang of them - it just takes practice. And Googling. A lot of Googling.

So now your flow will only fire when the Start Date is less than the Creation date. 

Now all you need to do is add your email to the body of the flow and test it out. You will not see a run at all when your Start Date is equal to or less than today.

[Tsamuel3]

Thank you so much! This is so helpful.

I was following you until this sentence “So now your flow will only fire when the Start Date is less than the Creation date.” confused me. I'm looking to do it the other way around, the creation date is less than the start date (created 2/22/23 - start 2/27/23, for example), which is what I thought you state in the other parts of your explanation. Is this sentence a mistake or should I change the expression? OR do I misunderstand expressions?

[Tsamuel3]

In addition, I need to be able to send emails based on a column in the list. That's why the variables are added. I tried to add variables with the When an Item is Created trigger and it doesn't seem to like that. I tried to add an Apply to Each action, but it asks for an output from previous step. I look for When an Item is Created output but none of the choices work. Am I totally off base?

[Tsamuel3]

Looks like I got it to work! The test email sent properly. Now just for some fine tuning. Thanks for the help in message #2.

[Rhiassuring]

With your question - that was me messing up my words, yes, the expression is to trigger when the Start Date is further in the future than "Today" (which will be the creation date since it's triggering on creation.) 

[Tsamuel3]

I thought my flow was working and now realize it isn't. It doesn't show any flow errors but when I click Test it just twirls and doesn't do anything. I the last time I tested it worked. However, now when I look at run history, today's runs failed. Can you tell from the flow layout if I did anything wrong or do you need more details?

[VictorIvanidze]

Yes we need more details 😉 Could you show the error?

[Rhiassuring]

@Tsamuel3 The most helpful things to see would be the spot where the workflow errors (if you click the "Failed" run you'll note the actions have a green check or something else. The things without the green check - probably a red icon - we'd need to see. And then, the configuration of that action that's failing.

[Tsamuel3]

I'm trying to compare the Send an email (V2)2 of the failed and succeeded versions to see what's different but not successful. The @Anonymous.com is what I used before when it worked.

[VictorIvanidze]

In "Set variable 2" use concat() function.

[Tsamuel3]

I'm sorry but I don't know exactly how or where to add it. 

But I want to say, I copied this action from another similar workflow that works as expected, and it doesn't have a concat, except for time zone. I'm curious as to why this flow needs one.

[Tsamuel3]

Is there anyone who can help me with this?

[VictorIvanidze]

Did you investigate why in "Set variable 2"  the dynamic value is empty?

[Tsamuel3]

I did. I copied the action from a previous workflow that did not have a value. That workflow is very similar to this one and since I'm not familiar with the "why" or "how" of things, I used the same one. If I need to add a value there, I don't know what it should be.

[Rhiassuring]

Hi @Tsamuel3 ,

• In your "Set Variable 2", you'll want to backspace all of that. 
• Click into the field, and you'll get 2 tabs appear - Dynamic Content or Expression.
• In Expressions, type "concat" and then type a ( bracket. 
• Then, keeping your cursor at the space after the (, click Dynamic Content and find that "Mgr COSID" value. 
• Then, type ",'@ford.com'). 
• Click "OK". 

That's just how we tell Power Automate that "i want to put these two pieces information into a single string".

It'll look like this:

That will put your two values together, properly, so that the variable will make sense in your email.

[Tsamuel3]

Thanks. Based on your instructions above, I added this to the value field: 

concat(triggerOutputs()?['body/MgrCDSID']",'@ford.com')

But I got this error:

[Rhiassuring]

Did you first write it out in the expression tab field, then click okay, and have it appear purple in the value field?

[Tsamuel3]

I wrote in Expression, then Dynamic, then clicked ok.

[Rhiassuring]

I see a " after your ] that doesn't need to be there