Flow for Delivery Alerts

[Anonymous5]

Hi all,

I’d like to create an alert system that is tied to a set of data tables for our maintenance and purchasing system. I believe Flow would be a good fit for this but this from what little I know about Power BI IT it may also suit this purpose. For the sake of this post, I’ll explain what I’d like Flow to do.

I’d like to set Flow to check our Purchase Order SQL data table for order status on a daily frequency using Transform Data using Power Query. Here it could note when an individual PO status changes to “Received”. Separately I’d like to set up a table stored on OneDrive for “POs to watch” that would be manually entered by our employees for any sensitive orders. Using “Get Data” with the Power Query connection, could Flow “compare” the updated SQL table against the OneDrive table and send a notification when one of sensitive POs (by unique 7-digit PO Number) is delivered in the system?

Rgds,
Rob

[v-litu-msft]

Hi @Anonymous5,

You can use the When an item is modified (V2) trigger to monitor the SQL if some data updates.

You can use the Apply to each action to loop through all the rows get from OneDrive, then compare them use the Condition action.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous5]

Hi Lin,

I have not used this trigger before. Would this continuously monitor my SQL table for changes? My fear is that this table is pretty active so it would be executing quite often. When this trigger occurs would it only look at the single row where the data change occurred? I was thinking triggering this on a time schedule a few times a day would be adequate.

Also, I agree that a condition would be best and that the OneDrive Excel Table should be used for comparison but I'm a little unsure of how to tie it all together. What action would be used for this comparison of the OneDrive table to the SQL table? Get Rows (Excel connector)?

I look forward to any additional help.

[barret]

If you use the "when an item updates" trigger, then yes, your flow would execute every time a record updates, for each individual record. If the table updates frequently, then it would probably not be the best alternative. Does your SQL table have some kind of timestamp that indicates when a record is updated to a status you want to watch? If so, then I might suggest a scheduled flow that looks at the timestamps with a SQL WHERE clause. It wouldn't be a perfect solution due to the vagueries of timestamps, but it would run far less often.

[Anonymous5]

Hi Barret,

The table has a column for “delivered” (yes/no), as well as a separate column for the datestamp with time stamp.

I would be fine with a lower frequency for checking the status and I’m confident I can point Flow toward the correct place to look. What I am not sure about is how to incorporate my “watchlist” that would be made separately to compare against the table.

Obviously a condition should be used here and some version of Apply to Each but I can figure out how to put it all together unless the individual PO numbers are placed in the condition box but this seems a bit clunky to update.