Answered

Inserting data to an email from multiple sql tables from when item created trigger

(0) Share

Report

Posted on by PhillipMcleod

I have and SQL database accessible via a on premise gateway, that I wish to trigger an email notification when a particular customer transaction is generated.

The is pretty straight forward and I have this running successfully.

I would like to inset details from the triggering transaction to the Body of the Email, the information I would like to insert is from linked tables in the SQL Database. In the "When an item is created (V2)" trigger, one of the Advanced option is for a "Select Query".

I have entered a Select query in to this field with Joins but still can't access the fields in the other tables

Categories:

Using connectors

I have the same question (0)

All responses (5)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @PhillipMcleod ,

The select query is for selecting specific columns from current table, if you want to get entries from another table, you need to use Get rows (V2) action and also leveraging the Filter Query to get specific items from the linked table.

Best regards,

Was this reply helpful? Yes No
PhillipMcleod 6 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni
Do you have any samples of what this would look like and how would I go about linking 5 tables.
This is what the SQL would look like.

SELECT SD.SalesDeliveryNumber,
SDL.QuantityDeliver,
P.Code,
P.Description,
SO.SalesOrderNumber,
C.Name
FROM DR.SalesDelivery AS SD
LEFT JOIN (SELECT *
FROM DR.SalesDeliveryLine) AS SDL ON SDL.SalesDeliveryId = SD.SalesDeliveryId
LEFT JOIN (SELECT *
FROM IC.Product) AS P ON P.ProductId = SDL.ProductId
LEFT JOIN (SELECT *
FROM DR.SalesOrder) AS SO ON SO.SalesOrderId = SDL.SourceOrderId
LEFT JOIN (SELECT *
FROM DR.Customer) AS C ON C.CustomerId = SO.CustomerId

I have also tried "transform data using Power Query" as I'm familiar with power query also.
All the above tables are linked via primary key/foreign key in the SQL database.

There are certain field in each table that i would like to include in the email header/body
example
customer names from the customer table
Sales order Number from the Sales order Table
Product name from the Product Table

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @PhillipMcleod ,

You need 4 Get row for the other 4 tables. In each action, row id is from corresponding field from the trigger use dynamic content. I don't have a SQL database for demonstration, sorry about that.

Best regards,

Was this reply helpful? Yes No
PhillipMcleod 6 on at

Like (0)

Report
Copy link

Link copied!

Thanks @v-jefferni
I was using "Get Rows (V2)" as apposed to "Get Row (V2)" following your original reply.

How does this work with tables that are not the trigger table, for instance the "Sales Order Table" and "Products" is linked on the "Sales Delivery Line" table. "Sales DeliveryLine" is linked to the "Sales Delivery"(trigger) Table

Was this reply helpful? Yes No
PhillipMcleod 6 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni

I figured it out once I add the second "Get Row (V2)"

Was this reply helpful? Yes No