How to synchronize two Microsoft SharePoint lists using one Microsoft Power Automate flow

SharePoint lists really don’t usually shop all the facts about an entity. For occasion, you might be monitoring sales and commissions but retain the commission prices in a tightly-secured second checklist. Remembering to update both equally lists as information changes around time can be a cumbersome endeavor.

SEE: Employing kit: Microsoft Electric power BI developer (TechRepublic Premium)

To simplify this course of action and boost details high quality and integrity, this tutorial reveals you how to synchronize two Microsoft SharePoint lists employing a Microsoft Energy Automate flow. We’ll perform with two simple lists: 1 merchants particular person product sales quantities and the 2nd merchants each and every employee’s commission fee. That way, quite a few people can update the Product sales list but only a few key individuals will have accessibility to update the Commissions checklist.

For this tutorial, I’m employing Microsoft SharePoint lists and Microsoft Electric power Automate, which is free with most Microsoft 365 licenses on a Windows 10 64-bit process.

Jump to:

How the two SharePoint lists do the job collectively

You can operate with SharePoint lists or Microsoft Lists, but for this tutorial, we’re using SharePoint lists. Determine A exhibits two SharePoint lists. Profits can have various data for each Worker ID value. The second checklist, Commissions, lists personnel and their charges. Each worker will have only 1 history in Commissions.

Figure A

Now here’s the circumstance we’re addressing: The Profits list is updated on a regular basis. As the checklist is up-to-date, Electrical power Automate will examine the Commissions listing for a matching Worker ID. If none exists, Power Automate makes a new file, copying the Worker ID value and applying a default commission share of .345.

SEE: Microsoft Energy System: What you require to know about it (totally free PDF) (TechRepublic)

This is our easy situation. At no stage will the movement calculate a fee or delete any records in possibly record, but each of these responsibilities are extensions of this situation. Right now, the Commissions record has 5 information. Observe that you have two staff members with the very last identify Jones: Jones001 and Jones002. Currently, each Employee ID benefit is in both equally lists.

How to update records employing Electrical power Automate

We want to update the Commissions listing when a new Employee ID worth exhibits up in the Sales listing. To do this, we can use Ability Automate and conserve a person the guide activity of incorporating a new Commissions record for the new personnel.

At this issue, we need to outline the time period “new.” Within the context of what we’re executing, new doesn’t indicate the personnel is new to the company. Somewhat, new implies that the Worker ID value isn’t in Commissions.

To get started out, we’ll develop a Electricity Automate stream that results in a new record in Commissions when a new Personnel ID value is included to the Income checklist. The only prerequisite for our task is that both lists share the Personnel ID column. You’ll want to know the SharePoint site and the checklist names.

We’ll use only one particular circulation, which is activated when you include a new file to the Income listing. Now, let us increase this circulation with the pursuing ways:

1. Signal in to your Microsoft account and decide on Power Automate from the Applications launcher.
2. Click Build in the remaining pane.
3. Look through to the bottom and click on the SharePoint connector.
4. In the ensuing record, click on the When An Product Is Designed alternative (Figure B).

Figure B

1. From the dropdowns in the first movement card, pick out the SharePoint web page and the Gross sales list (Figure C).

Determine C

1. Click on New Step to display screen the next card.
2. If SharePoint isn’t in the prime list, click the Expand Listing of Connectors down arrow in the slender grey line.
3. In the ensuing checklist, click SharePoint to update the bottom listing of out there triggers and steps.
4. In the bottom pane, click on Steps.
5. Specify the SharePoint site and the name of the second list, Commissions.
6. Click on the Present Superior Choices url.
7. Click on inside the Title handle and enter Personnel ID ne, in which ne is small for “not equal.”
8. Click the Include Dynamic Content backlink if needed.
9. From the Dynamic Information record, decide on Personnel ID to incorporate it to the Title management (Determine D).

Figure D

In a nutshell, the to start with card identifies the motion that triggers the action in the second card. That induce is adding a new product to the Profits checklist. If you change an present history, you will not bring about this movement. You result in it only when you include a new document.

The 2nd card specifies the listing that the move updates, passing the Worker ID benefit. If the Worker ID price is not in the Commissions checklist, Energy Automate adds the record.

Now, let’s incorporate a handful of records to the Product sales record to see how the move reacts.

How to use the Electrical power Automate movement

You never have to do a thing to bring about the Electrical power Automate circulation other than update the Revenue listing. Basically go to your SharePoint interface and open Gross sales. Now, enter the following report: Edington, Edington001 and 14000, as demonstrated in Figure E.

Figure E

The Personnel ID worth is not in the Commissions list however, so the expression

Worker ID ne Staff ID

that you entered in steps 11 and 12 is legitimate. The new Personnel ID price, Edington001, doesn’t equivalent any benefit in Commissions. For that reason, the move copies the new Employee ID to the Commissions checklist.

As you can see in Figure F, the stream additional a file for the new Personnel ID, Edington001. The level is a default value, which the record uses for all new information. Important staff can transform that level, but the default assures that a price exists for just about every record.

Figure F

Based on your surroundings, it could possibly consider SharePoint a number of seconds to update the Commissions record. If you look at that record and you don’t see the new history, wait a couple of seconds for SharePoint to capture up.

Now, incorporate another file for Edington001, as revealed in Figure G. Then, look at the Commissions listing.

Figure G

This record triggers the circulation simply because it is a new file, but it doesn’t incorporate a record to Commission due to the fact the checklist already has a record for Edington001 as a result, the Staff ID expression is fake.

SEE: Hiring Package: Databases engineer (TechRepublic Premium)

There is a whole lot more you may want to do with this variety of Power Automate circulation. For instance, if you transform the Worker ID for an current file, very little takes place. You can incorporate much more disorders and actions, or you can generate a new stream to include that examine.

Go through next: Ideal enterprise intelligence applications (TechRepublic)