Answered

Limiting SQL Server Rows That Are Returned

(0) Share

Report

Posted on by robbinz2121

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.

Categories:

Business Process Flows

I have the same question (0)

All responses (5)

Answers (1)

TaylorBecktold 295 on at

Like (0)

Report

Re: Limiting SQL Server Rows That Are Returned

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.

Was this reply helpful? Yes No
robbinz2121 12 on at

Like (1)

Report

Re: Limiting SQL Server Rows That Are Returned

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

Was this reply helpful? Yes No
Verified answer

TaylorBecktold 295 on at

Like (0)

Report

Re: Limiting SQL Server Rows That Are Returned

@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.

Was this reply helpful? Yes No
robbinz2121 12 on at

Like (1)

Report

Re: Limiting SQL Server Rows That Are Returned

@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!

Was this reply helpful? Yes No
TaylorBecktold 295 on at

Like (0)

Report

Re: Limiting SQL Server Rows That Are Returned

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

Was this reply helpful? Yes No