Introduce the sample usage of Microsoft's Power Automate with SQL Server

Hello everyone! I’ve been away for a long time from blogging due to various events that have kept me busy, making it challenging to find time for writing. In my latest article, I briefly introduced Power Automate. Today, I’d like to try creating a simple Flow so that everyone can see how it can be practically used.

Prerequisites

• You should have an account with a subscription that allows you to use the Power Automate service, such as Microsoft O365 E1, E3, E5 subscriptions, and similar.
• In case you don’t have a subscription, the minimum cost for using Microsoft Power Automate is $15 per user (subscription) or $0.6 per flow run (pay as you go).
• You should have a SQL Server in the cloud for testing purposes.

Objective: We are going to create a Flow that will send a Leave Approval Email only when new data is added to the table in SQL Server.

1. First, let’s create a table to use on SQL Server:

CREATE TABLE Example_Leaves
(
  LeaveID INT NOT NULL IDENTITY PRIMARY KEY,
  Requestor NVARCHAR(100) NOT NULL,
  RequestDate DATE NOT NULL,
  SubmittedDate DATETIME NOT NULL,
  LeaveDetails NVARCHAR(MAX),
  IsApproved BIT NOT NULL DEFAULT 0
);

Note: In this example, the Requestor field will store the requestor”s email.

2. Next, go to https://make.powerautomate.com/ and click on “Create” in the left panel.

3. Choose “Automated cloud flow” and select “When an item is created (V2) SQL Server” as the trigger.

   • Once the “Example Leave Request” flow is created, enter the connection details for your SQL Server and click “Create connection.”
   • After the SQL Server connection is created, you will see the Server name, Database name, and Table name.

4. Once the “Example Leave Request” flow is created, enter the connection details for your SQL Server and click “Create connection.”

   After the SQL Server connection is created, you will see the Server name, Database name, and Table name.

5. Click the ”+ New Step” button and select “Start and wait for an approval.” Fill in the Approval type, Title, Assigned To, and Requestor, as shown in the example below.

   Note: In this case, I selected “Approve/Reject — First to respond” as the Approval type, which means anyone can approve/reject the request in the Assigned To field.

   

6. Click the ”+ New Step” button and select “Condition.” Then, select the Outcome in the “Choose a value” field to compare it with “Approve.”

7. In the “If yes” section, choose “Update row (V2)” for SQL Server to update the row and set the IsApproved field to true.

   Note: Do the same in the “If no” section, but set IsApproved = false.

8. After the “Update row (V2)” action, select “Send an email (V2)” from Office 365 Email to send an email confirming the approval details to the Requestor.

9. Done! 🥳

Once you’ve created the Flow, let’s trigger it to see how it works.

1. Insert some data directly into the SQL Server table:

INSERT INTO [dbo].[Example_Leaves] (Requestor, RequestDate, SubmittedDate, LeaveDetails)
VALUES ('{your-email}', '{your-request-date}', GETDATE(), '{your-request-detail}');

2. After inserting data into the table, let’s check the Flow to see if it starts running. In the image below, we can see that there is one flow with Status = Running. Note:

   • The Flow may take a short delay before triggering.
   • Status = Running until the Approver approves/rejects the request.

3. Now, let’s check our Inbox to see if we received the Approval email. The Approval Email will look like the image below, allowing you to Approve/Reject the request.

4. Let’s check if the IsApproved value in the table changes after we click “Approved” in the email and if the approval confirmation email is sent back to the Requestor. Ta-daaa! It works perfectly! 😄

That’s it! For anyone interested in using Power Automate for simple applications, I hope this will be helpful. But before you use it, please study both the advantages and disadvantages of Power Automate. See you next time! 👋

P.S. Lastly, if anyone is interested in reading more about the technology of Mycos Company, you can follow and read further content on their Medium page at https://medium.com/mycostech