Power Apps - Building Power Apps

SQL View - New Row & Email Alert

Like (2) Share

Report

Posted on 10 Apr 2025 11:48:12 by craig_01

153

Hi There,

I have a SQL View that is based on live orders, one of the columns name (ColumnA) has a "trigger value" called Supplies Required, when a new order is populated into the view, i am looking to create a flow that would then email a list of items that have Supplies Required as a value in ColumnA.

Any suggestions on the best approach to build this flow.?

Im starting with a recurring time trigger, and then using Get Rows (V2) SQL operation.... But not sure what to do after that.

Thanks

Categories:

Connectors

Dataflows

All responses (4)

Answers (0)

RyanAutomates 134 on 10 Apr 2025 at 15:42:13

Like (0)

Report

SQL View - New Row & Email Alert

Hi,

Ah yes you're correct, you can't trigger off a view.

So I guess it depends on how you want to go about it - you could go with your scheduled flow method and implement something like Michael is suggesting, you just won't get instant email updates, but it's free and simple.

However in an ideal world, you'd trigger off the specific order change in sage.

To do this, I would recommend using KingswaySoft (if you can and want to go that far - it does come with licensing costs.) - it has defined connectors for each sage software and has built-in transformation tools. You can use it to handle real-time changes in sage and mirror these changes through your data transformation into a SQL database from which you can trigger changes off of through Power Automate to generate your email.

Hope this helps,

Ryan
Suggested answer

Michael E. Gernaey 41,027 Super User 2025 Season 1 on 10 Apr 2025 at 14:42:34

Like (0)

Report
SQL View - New Row & Email Alert

Hi @craig_01

When you say trigger value, is it an actual SQL Trigger, or I think you are simply saying its the thing that you want to drive your flow to do something(whether in an automated real-time if possible, or in a scheduled flow, correct?

Also, do you have a deterministic or non-deterministic key to your view? Do you require Pagination to run through or are these fairly small payloads? Just want to avoid you ending up with duplicate rows, if using non-deterministic and large payloads.

Question: Business Logic question to be specific. How do you determine later that you have or have not already sent details for a row from a week ago? And how do you determine between 4 different orders. These are important when you want help building something as the logic is what makes the ability to build it.

If you use the scheduled, which is fine due to your use of Views and the connectors limitations, but are you looking at some specific column to make sure

a) its only today

b) and your field name eq 'Supplies Required'?

c) I understand the check on a string for (b above) but what field actually has the Data you want to email

d) how to determine orders (group by with filtering)

e) Email to whom? Do you want to email every single row in one email, or do you want different emails for different orders?

I had to make some pretty huge assumptions here, so I also hope you take this as a takeaway as it's a lot to do that :-) if I am wrong

Suggestion: Have you looked at using a Stored Proc instead of a Query? No preference per say just asking as you could then group them before it got to this spot. Of course if you used execute sql query I feel you'd get more flexibility in the long run

So, I would look at it like this

1. Trigger (Reoccurring)

2. Get Rows or Execute SQL Query

Now is where it gets interesting as I am doing the following:

assuming its 1 email per order, where order can have multiple rows for the items needed (I have no idea how this is yet from the data provided)

I want to grab all the Unique Order Id's first, then loop through and grab only the ones for that unique Order Id and send an email. To do this I have to get that unique list.

3. Add a Select and make the input, the output of Get rows (v2)

We are going to grab only the Order Id or whatever you call it

3a) In Your Select click the advanced switch

3b) Since we only want a simple array or Order Ids I don't want to make a "key" to it.

Select the Dynamic Column from your Get Rows V2, that is your Order Id and that is all

4. Add a Compose. Inside the Compose put the expression (not string)

This will take all the order id's and just make a unique simple array

union(body('Select'),body('Select'))

5. Now we want to iterate through all the Unique Id's so that we can easily just grab those and send the email for that one unique id

Add an Apply to Each

use the input From the Outputs of our Compose above it

6. inside the Apply to each

6a)Add a Filter Action

---for the input use the output from Get Rows V2/Execute Sql Query

--- on the left click the Dynamic Property from Get Rows, that is the Order Id Column

---leave in the middle is equal to

---in the right put item() (as an expression)

What we are doing is creating a singular array of data that are all the columns for that Unique ID

6) Add another Select - we may not want to send ALL columns, so we use a select to make it easy to send just the rows we want

with the From being output of the above Filter Array

6B Now in the left side Key, just type the string you want as the Label in the email

In the Right side, for each key/value pair type

item()?['ColumnName']

Just like that, and it will grab the data from the Filter Array, instead of the Get Rows (as we don't want all these, we want just our filtered subset)

As an education thing: Selects done properly do not need Apply to Each to loop through. It does it behind the scenes to build your data. but make sure you select Body from the Filter Array and not the Item property as it will cause an Apply to each to pop in and we don't need it

7) Ok now that you have all the fields and data you can use the Create HTML Table action to generate a table for your data in HTML for your email

The input is the Output from the Select 2

NOTE: If you rename any of these actions, make sure to use those names, versus my defaults

8) Use the Send Email V2 to send your Email.

All you need to do, is put whatever you want in the body and then add the Dynamic Property Output from the Create HTML Table

And bingo :-)

If this helps resolve your Issue please Mark as such and maybe a like

Then feel free to come back and ask more questions if you are stuck :-)

Few other notes:

1. You have to figure out how you are storing your Email Addresses. If you need, create an Environment Variable, or use a SharePoint list where you can do a lookup to get the Email Address (string) to send to people.

Emails email1;email2;email3 etc
craig_01 153 on 10 Apr 2025 at 14:01:11

Like (1)

Report

SQL View - New Row & Email Alert

@RyanAutomates Thanks for the response.

The "Supplies Required" value is already populated within the orderline detail.. The reason i am using a SQL view is because the data is feeding from Sage which has multiple tables, so i have them joined in a view to show the specific order information such as Customer, OrderLine Details, etc.

I believe the trigger based on create or update only works when using a sql table, and not a view.

So what i was attempting to do within the SQL Get Rows > Filter Query, was to look for records that had ColumnA eq "Supplies Required", as any other order lines that dont have this filter criteria im not worried about.

Thanks
RyanAutomates 134 on 10 Apr 2025 at 13:29:03

Like (0)

Report

SQL View - New Row & Email Alert

Hello @craig_01,

What is your data source?

I would trigger this based on create or update events, rather than using a scheduled flow.

Can you clarify if the row being added is created with Supplies Required already with a value, or is the row created and the value is updated? This will determine whether to use a creation or a modified trigger.

For example:

Since you want to trigger it on the creation of a new row, you need to use a trigger which fires when a new row is added into your table.

For example if you are using a SQL database, then you should use the "When an Item is Created" trigger with the SQL Server Connector connected to your database. You can then add a condition control to check if that Order has a column Supplies Required that is not null. You can then perform a SQL Query action step or Get Rows to list all rows that match your criteria, and put the output (or you could play around with formatting it using compose actions) into an email using the exchange connector actions.

If you are using Dataverse, you should use the Dataverse when a row is Added and specify that Supplies Required should not be null.

Then list rows where Supplies Required = the Supplies Required value from your trigger, then format it and send an email via exchange actions.

Hope this helps,

Ryan