cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robbinz2121
Frequent Visitor

Limiting SQL Server Rows That Are Returned

I'm trying to create a Flow that sends an email when a new sales order is added to our SQL Server database. The email is converting the SQL data to an HTML table and sending an email displaying the table within the email body.

 

Everything from the trigger of a new sales order being created to the email being sent is working as expect but I'm having trouble limiting the SQL results to be within the sales order that was created. First I must share that I did not have any "Top Count" set in my first test so I dealt with thousands upon thousands of emails being sent to myself this morning. I then changed Top Count to 1 thinking this would limit it to a single order, but in reality it limited the results to a single line item on the sales order even though there was multiple. I increased Top Count to 20, which is more than the line items on the order. This resulted in the email containing all 3 or the line items from the test sales order and then it proceeded to list additional line items, up to 20 rows, pulling from other sales orders. 

 

My question is: How can I filter so that all the line items are shown from the new sales order and only from that sales order?

I have my trigger which is set off when a new item is created in my CUSTOMER_ORDER table. The flow then moves to the next step which is Executing a SQL Query. The table the query is being run against is CUST_ORDER_LINE (line items of the sales order/CUSTOMER_ORDER). 

 

There is no Filter Query and Top Count is set to 20. I'm assuming the Filter Query being blank is my issue but I'm not sure how to filter to only show the newest sales order. The sales order number is held in the CUSTOMER_ORDER table. Is there a way to join the CUSTOMER_ORDER table with the CUST_ORDER_LINE table? Or maybe there is a better way to capture the sales order ID that caused the trigger to ensure it's the record that's used in the query results? Right now I'm just sorting the sales orders descending which I would think leaves the door open for a risk of two people saving sales order simultaneously and both triggering an email for the same order instead of the one they saved. 

New to Power Automate and Power Apps. Please let me know if there's additional details that would be helpful. 

 

 

PowerApps1.JPG

 

PowerApps2.JPG

 

PowerApps3.JPG

 

PowerApps4.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Taylor_Becktold
Resolver III
Resolver III

@robbinz2121 -

 

Thanks for providing the images. In the 'Filter Query' parameter of your 'Execute a SQL Query' action can you input something like this: CUST_ORDER_ID eq 'ORDER_ID' where that value in single quotes is the order id from the trigger.

 

Did my reply help? Please give it a thumbs up.
Did my reply answer your question? Please mark it as a solution!
If you liked my response, please consider giving it a thumbs up.

View solution in original post

5 REPLIES 5
Taylor_Becktold
Resolver III
Resolver III

Hi @robbinz2121 -

 

You are most likely going to need to include a filter query and here is a great reference for something like that: https://powerautomate.microsoft.com/en-us/blog/advanced-flow-of-the-week-filtering-with-odata/

But, can you also provide a screenshot of your Power Automate Flow and the SQL action you are using? The additional context will be beneficial in assisting you. 

 

Did my reply help? Please give it a thumbs up.
Did my reply answer your question? Please mark it as a solution!
If you liked my response, please consider giving it a thumbs up.

Thanks @Taylor_Becktold . I've updated my post with screenshots. I'll review the article you sent as well.

Taylor_Becktold
Resolver III
Resolver III

@robbinz2121 -

 

Thanks for providing the images. In the 'Filter Query' parameter of your 'Execute a SQL Query' action can you input something like this: CUST_ORDER_ID eq 'ORDER_ID' where that value in single quotes is the order id from the trigger.

 

Did my reply help? Please give it a thumbs up.
Did my reply answer your question? Please mark it as a solution!
If you liked my response, please consider giving it a thumbs up.

@Taylor_Becktold that worked. After seeing this worked I looked at the raw output of the trigger and saw that it did capture all the fields/columns of information from the record that created the trigger. Great to know.

 

Initially when I was debating this route I didn't realize that the data from the trigger would be captured in the flow and could be called on later in the flow. So I was under the impression that I would first have to somehow manually capture/call that data before I would be able to reference it in a filter. 

Thanks for the help!

Taylor_Becktold
Resolver III
Resolver III

@robbinz2121 I'm glad that worked for you and I appreciate the feedback! Welcome to the community 😀

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (5,193)