Power Automate gives us a useful tool to integrate data between systems in the cloud.
This article will take the scenario where we have a collection of data in SQL that we want to integrate into the Dataverse Database for use in PowerApps – focusing on how we can pass User Roles and Teams where we may need this data present to help manage security rights.
Introduction
DoOften, you may have information about users stored in your SQL server which you want to integrate in your Dataverse teams? This , so this article will guide you through how to set up a Power Automate Flow to associate Users to the connect Teams based on a separate SQL Database.
The example we are using here is from our experience in Asset Management, where each Asset may have its own Team of Stakeholders that we want mapped into PowerApps as a Team to correctly set User permissions to that Asset. populate your teams.
This guide will instruct you from the perspective of tables, views and values unique to our work; you may need to adapt this to match your server and tables.
Step 1: Opening Power Automate
First, you will need to log into your Office 365 account on your web browser. From there you will need to navigate to Power Automate using the menu bar on the left. https://www.office.com/
Step 2: Creating the Flow
Once you’reNow we are in Power Automate, you will need to create a new Flow. The side-menu will bring you to the Create page, from where you can select the type of Flow you want to build. We suggest using a scheduled cloud flow to make sure your data stays up to date with any changes made in SQL.
Step 3: Connect to the SQL Database
The first and most important step to add is the SQL Server action Get Rows (V2). This is the action which will retrieve the data from your SQL server to bring into your Dataverse tables. You will need to set up a connection with your SQL Server, for which there are multiple options, but we recommend using the SQL Server Authentication option.
For this, you will need the server and database name you are getting your data from, and the username and password for your server. However, any other method will work too, so you can pick the option which works best for you.
Step 4: Read or Select from the SQL Table or View
Once you have created your server connection, you will be able to select which table or view you want to read your data from. For this example, we will be using the Roles view, as the Asset ID in this table tells us which team each user should be allocated to.
Note. If you have any criteria for which records you want to bring through, you can select the advanced options button to get access to the various fields which let you filter the data, but if you want to bring through all of your records, leave these fields blank instead.
Step 5: Doing the Flow Right with Variables
Next, you can initialise a series of variables to be populated later in the flow. This step is optional, but it is recommended as it will help you manage and track the information which is required for the lookup fields and relationships later down in the flow. When initialising variables, it is important to give them all distinct names so that you do not set or call the wrong variable at the wrong time.
Step 6: Avoiding Hard-Coding the Environments
The next stage is used to retrieve configuration details. This step is important as it lets you call upon details for your flow stored within Dataverse which can be edited for each different environment. To ensure that not everything is done in just one flow, retrieving the configuration details is done in a child flow, and the results of this flow are returned as variables for the parent flow to use.
The actions of this flow returns the details of the data stored in the configuration table (this works best if there is only one record in the table as it means you do not need to filter the table, and the correct details will always be returned), enters the data into the variable and then responds to the parent flow. In our example, we will be returning the email of the configuration user, as it will be important later down in the parent flow.
This step avoids having to edit your Flows when exporting and importing into future Environments (such as the Test or Production Environments in your Release Cycle) – and keeps to good development practise regardless of working on a low-code or no-code platform like Power Automate.
Step 7: Match the Asset from SQL to PowerApps
Back in the parent flow, the next action you will need is an apply to each which uses the value of the Get Rows (V2) action; this ensures the flow cycles through each row returned from the SQL server and applies the same logic to each one.
Then, you will need to create a list rows action which returns the asset details, as these details will be used to find the matching team later in the flow. In our example, each team is linked to an asset and the roles table contains an asset ID field.
A filter is then used to return the unique GUID of each asset found which matches an asset ID brought from SQL.
This provision to update if existing, insert if not, is key to building a good integration – as we will avoid duplicating data if a pure insert is run multiple times.
Step 8: Use the right Team
Next, the results of the list assets action are used to filter the next list rows action using the owning team ID value. This second list rows action lists the teams which are linked to each asset found, and the unique GUID of this team is then recorded with a set variable action in one of the variables created earlier.
Step 9: Match the User from SQL to PowerApps
The next step requires a third list rows action; this one is used to find the unique GUID of the users who need to be linked with their correct team.
Before this action is included, it’s important to note that this action requires the emails listed in the roles table in SQL to already have been created in Dataverse, which can be done through another flow. In order to return the details of the right user, a filter is used which matches the email from the roles table to the email stored in the users table.
Then tThe unique GUID is then stored in one of the variables initialised earlier with another set variable action.
Step 10: Add the User to the Team for the Asset
After this, a condition is used to check if any user has been found which matches the email from the roles table. If a matching user was not found, then an email is sent to the configuration user, with the email address which was identified with the child flow, warning them that an error has occurred. But if a matching user has been found, then a new row is created in the roles table using the information from the roles table in SQL.
Then, uUsing the ID variables populated earlier in the flow, the relationship between the user and the team is created, adding the user into the correct team.
If this Team has 1 or more Security Roles attached, this then immediately applies the permissions granted by that Role to the User who has just joined the Team – giving us a way for changes in SQL to define the Security Rights available in Power Apps.
NOTE: the Relate Rows Step in Power Automate uses an aspect of the OData Service that is called by a URL and so needs the destination URL included in the action. This means that we can find the URL of our PowerApps Environment ‘hard-coded’ in the Flow and so is an important aspect from step 6 to use Environmental Configuration and avoid the Flow being tied to a particular environment.
This article on our site may be useful here: https://www.crmcs.co.uk/content/creating-a-flow-to-add-a-many-to-many-relationship.aspx
As is this article from the Power Platform community: https://tattooedcrmgirl.com/2019/10/30/microsoft-flow-the-relate-records-action-demystified/
The Benefit
Today our solutions may comprise many Apps or Systems, particularly given the ease we can build new Canvas Apps connected to various sources of data.
Historically integrating these systems as a single solution could be an expensive and time-consuming job for a development team, often requiring a 3rd Integration Manager such as BizTalk or Scribe – Power Automate gives us a tool for this kind of low-code connection of data across the cloud that can be simpler, leaner and quicker to apply to our business problems.
In this example, we see how a SQL Database of Assets and Stakeholders can be integrated into PowerApps so the people related to the Asset (often referred to as the Related Parties) are made available in PowerApps and set the right security permissions.
Taking this one step further, we used this system to then push the security rights from Dataverse and into SharePoint so that the right Users were able to view the SharePoint Site for that Asset – giving us a combined security solution between SQL, PowerApps and SharePoint to keep a unified view of security.
FURTHER READING
