cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LyonsBI_BRL
Helper III
Helper III

When an item is modified in SQL - Update and email

I'm running into an issue when I want to kick off an email when I've changed a value in my gallery for a particular item. It's connected to a SQL server, showing the items in the gallery. What I want this automated flow to do is, when someone changes the NICS Status, it updates SQL for that item and then kicks off an email to that customer, based on their email address for that particular record. 

LyonsBI_BRL_0-1619925458352.png

 

I have my automated flow configured like so. However, I'm not getting any emails. I did noticed when I first set the trigger, the Table names didn't populate. To fix this, I went into my SQL Created Items - Send an Email flow, and found out, when peeking at the code, it looks for the table in SQL format. 

LyonsBI_BRL_1-1619925554379.png

I know I'm missing something simple here. As it is looking at the email field, for the email address that is in that column in order to kick off an email. 

 

Any suggestions?

 

1 ACCEPTED SOLUTION

Accepted Solutions

@Sid_Jafri , @Pstork1 

SUCCESS! Okay here's what the solution was

1. @Pstork1 - Yes the turning on the RowVersions, was an absolute. 

2. @Sid_Jafri You were right about the output. So what it was looking for was this, triggerOutputs()?['body/NICS_x0020_Status']

 

It didn't dawn on me, until I went to Using triggerBody() / triggerOutput() to read CDS trigger metadata attributes in a Flow | Power Auto... and looked it up and looked out how it was formatted.

 

I ran my rest and the YES part kicked off! 😀

LyonsBI_BRL_2-1619995521930.png

 

View solution in original post

21 REPLIES 21
Sid_Jafri
Responsive Resident
Responsive Resident

Hi @LyonsBI_BRL ,

 

Did you trigger the flow and check that which condition is running? Does it trigger the Send Email action and you don't receive email? By looking the screenshot it looks like that NICS status is choice column. You should check what is your trigger outputs for When Item modified. If you share the screenshot of Flow output of "When Item Modified" will help

Your condition should have expression that 

triggerOutputs()?['body/_NICSStatus_Label']

Please Thumbs up if my post helped you solve your issue.

 

Hey @Sid_Jafri , 

 

Thanks for reaching out. 

 

So in my "When Item is Modified" settings I see this. Is this where I need to enter the expression of "triggerOutputs()?['body/_NICSStatus_Label']"

LyonsBI_BRL_0-1619949362600.png

 

I don't think it triggers the Send Email action as I never recieve any email. The NICS Status gets changed in the DB as it patches correctly. You are right so the NICS Status is a choice column. 

 

LyonsBI_BRL_1-1619949629967.png

Doesn't look like it wants to kick off the flow yet. I know I'm close though. Any other suggestions?

Hi @LyonsBI_BRL 

 

No, you have to write in Condition action. Please see attached. Also if you trigger the flow and share the screen shot of executed flow that shows the outputs of Item modified trigger will help to identify the exact field name for the choice column. I just assumed that name will be NICSStatus

Hey @Sid_Jafri 

Okay so I entered the condition. Went to try and run the App, selected LEO for it to trigger, but still no email yet. 

LyonsBI_BRL_0-1619951285979.png

 

But yes, the name of the Dropdown is called NICSStatus.

I have been trying to manually trigger the flow. It just sits there, even after I go into the app, change the NICSStatus for that particular item, and still no email.

 

LyonsBI_BRL_1-1619951443188.png

 

Hi @LyonsBI_BRL 

 

Please see attached screenshot for further trouble shooting. Can you confirm that condition action goes to Yes action or No action? May be email value that is coming from SQL is not yours, you can hard code your email address to confirm

@Sid_Jafri Still no go. Though I am using SQL and not Dataverse. 

 

I tried hard coding it for the email for it to just go to me than what's in the email field and still no go. 

What's very odd is for the SQL trigger for When Modified, it'll auto populate the server name and DB, but not the table. I have to manually write that in. Could that be a problem? 

@Sid_Jafri Finally got something going here!

Okay so I set the NO as well to email me. Sure enough that fired off. So somewhere it's the condition then for the Yes that's not working. 

 

So we know the SQL modified WORKS. That trigger is correct. It's just getting the YES to work.

LyonsBI_BRL_0-1619956053705.png

So what's missing then?

 

 

Pstork1
Dual Super User III
Dual Super User III

One of the known limitations on the SQL connector and that trigger is that a ROWVERSION column is required.  If there isn't one in your table that might be why the table doesn't show in the trigger.

from SQL Server - Connectors | Microsoft Docs

 Note

When invoking triggers, we have the following limitations:



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

@Pstork1 Yep I saw that as well and added a rowversion column last night called RowVersion

LyonsBI_BRL_0-1619986254968.png

Unfortunately still getting this. 

LyonsBI_BRL_1-1619986286360.png

It's somewhere in the trigger, I know that, what it is, is where I'm stuck on still. If I can figure this out, myself or the community members here, would really help pave the way for this tool I'm creating. 

 

Thanks!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,267)