How to synchronize two Microsoft SharePoint lists using one Microsoft Power Automate flow
Your email has been sent
Especially when SharePoint lists need to be kept separate for security reasons, this Power Automate flow tutorial will help you keep both lists up-to-date.
SharePoint lists don’t always store all the information about an entity. For instance, you might be tracking sales and commissions but keep the commission rates in a tightly-secured second list. Remembering to update both lists as data changes over time can be a cumbersome task.
SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)
To simplify this process and increase data quality and integrity, this tutorial shows you how to synchronize two Microsoft SharePoint lists using a Microsoft Power Automate flow. We’ll work with two simple lists: One stores individual sales amounts and the second stores each employee’s commission rate. That way, many people can update the Sales list but only a few key people will have access to update the Commissions list.
For this tutorial, I’m using Microsoft SharePoint lists and Microsoft Power Automate, which is free with most Microsoft 365 licenses on a Windows 10 64-bit system.
Jump to:
You can work with SharePoint lists or Microsoft Lists, but for this tutorial, we’re using SharePoint lists. Figure A shows two SharePoint lists. Sales can have multiple records for each Employee ID value. The second list, Commissions, lists employees and their rates. Each employee will have only one record in Commissions.
Figure A
Now here’s the situation we’re addressing: The Sales list is updated regularly. As the list is updated, Power Automate will check the Commissions list for a matching Employee ID. If none exists, Power Automate creates a new record, copying the Employee ID value and using a default commission percentage of .345.
SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)
This is our simple scenario. At no point will the flow calculate 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 employees with the last name Jones: Jones001 and Jones002. Currently, every Employee ID value is in both lists.
We want to update the Commissions list when a new Employee ID value shows up in the Sales list. To do this, we can use Power Automate and save someone the manual task of adding a new Commissions record for the new employee.
At this point, we should define the term “new.” Within the context of what we’re doing, new doesn’t mean the employee is new to the company. Rather, new means that the Employee ID value isn’t in Commissions.
To get started, we’ll create a Power Automate flow that creates a new record in Commissions when a new Employee ID value is added to the Sales list. The only prerequisite for our task is that both lists share the Employee ID column. You’ll want to know the SharePoint site and the list names.
We’ll use only one flow, which is triggered when you add a new record to the Sales list. Now, let’s add this flow with the following steps:
Figure B
Figure C
Figure D
In a nutshell, the first card identifies the action that triggers the action in the second card. That trigger is adding a new item to the Sales list. If you change an existing record, you won’t trigger this flow. You trigger it only when you add a new record.
The second card specifies the list that the flow updates, passing the Employee ID value. If the Employee ID value isn’t in the Commissions list, Power Automate adds the record.
Now, let’s add a few records to the Sales list to see how the flow reacts.
You don’t have to do a thing to trigger the Power Automate flow other than update the Sales list. Simply move to your SharePoint interface and open Sales. Now, enter the following record: Edington, Edington001 and 14000, as shown in Figure E.
Figure E
The Employee ID value isn’t in the Commissions list yet, so the expression
Employee ID ne Employee ID
that you entered in steps 11 and 12 is true. The new Employee ID value, Edington001, doesn’t equal any value in Commissions. Consequently, the flow copies the new Employee ID to the Commissions list.
As you can see in Figure F, the flow added a record for the new Employee ID, Edington001. The rate is a default value, which the list uses for all new records. Key personnel can change that rate, but the default guarantees that a rate exists for each record.
Figure F
Depending on your environment, it might take SharePoint a few seconds to update the Commissions list. If you check that list and you don’t see the new record, wait a few seconds for SharePoint to catch up.
Now, add another record for Edington001, as shown in Figure G. Then, check the Commissions list.
Figure G
This record triggers the flow because it’s a new record, but it doesn’t add a record to Commission because the list already has a record for Edington001; thus, the Employee ID expression is false.
SEE: Hiring Kit: Database engineer (TechRepublic Premium)
There’s a lot more you might want to do with this kind of Power Automate flow. For instance, if you change the Employee ID for an existing record, nothing happens. You can add more conditions and actions, or you can create a new flow to add that check.
Read next: Best business intelligence tools (TechRepublic)
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to synchronize two Microsoft SharePoint lists using one Microsoft Power Automate flow
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
Zero-trust security operates on the fundamental premise that trust should never be assumed, regardless of whether a user or device is inside or outside the corporate network. In this TechRepublic Premium guide, we will explore the benefits of incorporating zero-trust security into your organization’s cybersecurity framework to prevent data breaches and strengthen the protection of …
Every organization relies on data that must be protected and backed up in a reliable and secure way by authorized personnel. While the content or criticality of the data may vary, the processes behind a successful backup strategy are universal. This checklist from TechRepublic Premium will help you establish a standard framework for backing up …
Artificial intelligence comes in many varieties, from tools that respond to customers via chat to complicated machine learning algorithms that predict the trajectory of an entire organization. Despite years of activity and the imaginative ideas found in science fiction, AI doesn’t yet comprise sentient machines that reason like humans. Rather, AI encompasses more narrowly focused …
Data governance is no longer a ‘nice-to-have’ measure in organizations as there is a growing concern around the globe that unregulated use of data can result in serious privacy violations, among other issues. That’s why data governance has topped the agenda in many continents as more efforts are being put in place to provide standards …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply