Image: PhotoGranary/Adobe Stock SharePoint lists do not always store all the info about an entity. For instance, you may be tracking sales and commissions however keep the commission rates in a tightly-secured 2nd list. Remembering to upgrade both lists as data changes in time can be a cumbersome task.
SEE: Hiring set: Microsoft Power BI developer (TechRepublic Premium)
To simplify this process and increase data quality and stability, this tutorial shows you how to integrate two Microsoft SharePoint lists using a Microsoft Power Automate circulation. We’ll work with 2 basic lists: One shops private sales quantities and the 2nd stores each staff member’s commission rate. That way, lots of people can upgrade the Sales list but just a few crucial individuals will have access to update the Commissions list.
How the 2 SharePoint lists interact
You can work with SharePoint lists or Microsoft Lists, but for this tutorial, we’re using SharePoint lists. Figure A programs two SharePoint lists. Sales can have several records for each Employee ID worth. The second list, Commissions, lists employees and their rates. Each worker will have just one record in Commissions.
The 2 lists are easy but associated by the Employee ID columns. Now here’s the situation we’re dealing with: The Sales list is upgraded regularly. As the list is updated, Power Automate will examine the Commissions list for a matching Staff member ID. If none exists, Power Automate produces a brand-new record, copying the Staff member ID worth and utilizing a default commission portion of.345. SEE: Microsoft Power Platform: What you require to understand about it (free PDF) (TechRepublic)
This is our easy scenario. At no point will the circulation determine a commission or delete any records in either list, but both of those tasks are extensions of this situation. Right now, the Commissions list has five records. Notice that you have two staff members with the last name Jones: Jones001 and Jones002. Currently, every Employee ID value remains in both lists.
How to upgrade records utilizing Power Automate
We wish to update the Commissions list when a new Worker ID worth appears in the Sales list. To do this, we can use Power Automate and conserve someone the manual job of adding a new Commissions record for the new employee.
At this moment, we need to define the term “new.” Within the context of what we’re doing, new does not imply the staff member is new to the company. Rather, new ways that the Worker ID value isn’t in Commissions.
To get started, we’ll create a Power Automate circulation that creates a brand-new record in Commissions when a new Employee ID worth is contributed to the Sales list. The only requirement for our task is that both lists share the Staff member ID column. You’ll need to know the SharePoint website and the list names.
We’ll utilize only one flow, which is set off when you include a new record to the Sales list. Now, let’s include this flow with the following steps:
- Check in to your Microsoft account and select Power Automate from the Apps launcher.
- Click Produce in the left pane.
- Browse to the bottom and click the SharePoint adapter.
- In the resulting list, click the When An Item Is Created option (Figure B).
Select a flow trigger.
From the dropdowns in the
very first flow card, choose the SharePoint website
- and the Sales list (Figure C).
- Figure C Identify the SharePoint website and list
- . Click New Step to show the next card. If SharePoint isn’t in the leading list, click the Expand List of Connectors down arrow in the thin gray line.
- In the resulting list, click SharePoint to upgrade the bottom list of available triggers and actions. In the bottom pane, click Actions.
- Define the SharePoint website and the name of the 2nd list
- , Commissions. Click the Program Advanced Options link. Click inside the Title control and go into Worker ID ne, where ne is brief for”
- not equal. “Click the Add Dynamic Content link if necessary.
- From the Dynamic Content list, select Worker ID to include it to the Title control (Figure D).
Specify the list to upgrade and include the Worker ID expression to the Title control. In a nutshell, the very first card recognizes the action that triggers the action in the second card. That trigger is adding a brand-new product to the Sales list. If you change an existing record, you won’t trigger this circulation. You activate it just when you add a new record. The 2nd card defines the list that the circulation updates, passing the Staff member ID worth. If the Worker ID value isn’t in the Commissions list, Power Automate includes the record.
Now, let’s include a few records to the Sales list to see how the flow responds.
How to use the Power Automate circulation
You don’t need to do a thing to set off the Power Automate flow besides upgrade the Sales list. Just move to your SharePoint interface and open Sales. Now, go into the following record: Edington, Edington001 and 14000, as shown in Figure E.
This record should trigger the flow. The Worker ID worth isn’t in the Commissions list yet, so the expression Worker ID ne Employee ID that you went into in steps 11 and 12 holds true. The brand-new Staff member ID value, Edington001, doesn’t equal any worth in Commissions. Consequently, the flow copies the new Staff member ID to the Commissions list.
As you can see in Figure F, the circulation included a record for the new Staff member ID, Edington001. The rate is a default worth, which the list uses for all new records. Secret workers can alter that rate, however the default ensures that a rate exists for each record.
The flow added the Staff member ID to the Commissions list. Depending on your environment, it may take SharePoint a couple of seconds to upgrade the Commissions list. If you check that list and you do not see the brand-new record, wait a couple of seconds for SharePoint to catch up. Now, add another record for Edington001,
as shown in Figure G
. Then, inspect the Commissions list. Figure G The circulation included the Staff member ID to the Commissions list. Include another sales record for Edington001.
This record activates the flow because it’s a new record, but it does not include a record to Commission since the list already has a record for Edington001; thus, the Worker ID expression is false.
SEE: Hiring Kit: Database engineer (TechRepublic Premium)
There’s a lot more you may wish to do with this kind of Power Automate circulation. For instance, if you change the Worker ID for an existing record, nothing happens. You can add more conditions and actions, or you can create a brand-new circulation to include that check.
Read next: Best organization intelligence tools (TechRepublic)