Power automate join two sharepoint lists
|
There is a common requirement to copy data between SharePoint list. This article explains the steps to replicate fields “when an item is created or modified” in the child list including attachments to a Master List and send a customized notification email to end users using Microsoft Power Automate. Considering the ideal scenario, in an organization, each department will add/modify the employee data for their respective department SharePoint list and the same data should be replicated to the Employee Master List. The challenge is that there is no unique attribute/ID for multiple SP list under SharePoint site and attribute ID will be unique only inside the scope of each list. For example, if an item is added under IT department SP list, then the ID no for that particular item is unique only inside that list. It will change ID value once it is added to any other SP list based on the items availability in the destination SP list. Hence, a new field of unique attribute needs to be created in all the lists against which each items under the same SharePoint site can be uniquely identified. Limitations :- This article do not cover the function “When an item is delete from the Child List” should in turn delete the same item from Master List. Will be preparing the document for the same and will publish soon 😊. Precautions :-
Please follow the below steps, Create a SharePoint List and a Master List. Make sure, Master List must have additional field named ‘UNIQID’ as a single line of text. Consider two child lists (Finance and IT) and Master List with the same headers.  Login to Microsoft Power Automate. Create a new flow -> Automated flow from blank -> Give name for your Flow -> Select Trigger “When an item is created or Modified” -> Create Select the SharePoint Site and choose the required child list. Example, child list called Finance Department Initialize the variable for creating UNIQID for child list. Assign the same value in Master list against the same list item. Call Initialize variable for UNIQID and UPDATEID and store value as a string Call Initialize variable for MASTERID and store as integer The above commands, sets up a UNIQID for items in the same list based on ID attribute. Ensure that this value remains unique in all child lists under the same site. Example, (FINID-VauleofID). UPDATEID and MASTERID value keep it as blank and will assign the value later Choose an action called Get Items under SharePoint For fetching items from the master list, use the option Filter Query field from the Get Items action. Select the Show Advanced Options to show all the fields and put the value in the Filter Query field for matching the items from Master List In the above scenario, if the filter query condition (UNIQID eq ‘UNIQID’) is true, Master list will populate only the matching items. Because when filter query condition is true, the data field already exists in the Master List. This logic will reduce the flow run time consumption and API calls between Power Automate and SharePoint List. Call for Apply to each to store fetched Get Items Master List attributes values. Add an action inside the Apply to each for assigning the values for UPDATEID and MASTERID Append Variable – Select UPDATEID in Name filed and assign UNIQID variable in the value field Increment Variable – Select MASTERID in Name field and assign ID value from Get Items action Next action called Condition to check the items Availability in both list Select the List name Master List and populate the required filed. The Value of ID field should be MASTERID variable and value of UNIQID should be UNIQID variable. All other fields should be fetched from When an item is created or Modified field values. A sample screenshot of final data is given below: We have populated the required data to master list. If there are any attachments in the child list, it can be pushed to the master list using the following steps. There are two scenarios Scenario 1: Update Item If the action is update item, then the applied logic will delete all the existing attachments from the Master List against that item and initiate another replication of all attachments from the child list to the Master List against the same item (Attachment deletion will happen in master data if the attachments are deleted from child SP list). Choose next action “Get Attachments” Fetch the all Attachments from Master List Next Action to “Delete Attachment” from Master List Click on Next Action “Get Attachments” and select the Child List, Value of ID field for get attachment is the “ID of When an item is created or Modified”. Next action “Get attachment Content” for copying the all attachment contents from the updated child list Next Action “Add attachment” and insert all attachment into Master List against the item value. The above steps will update the attachment in Master List. To trigger a customized email notification for an item update to the respective team, follow the steps below. Call the action “Send an email” and do the necessary changes in the message content, we can customize the email with HTML code also. Please find the sample below: Scenario 2: Create Item If the Action is Create Item, copy all attachment content from the new item and push into the master list against the same list item. Create an Action “Get Attachment” and select Child List, Next Action “Get Attachment Content” to store all attachment from Child List Next Action “Add attachment” and insert all attachment into Master List against the item. The above steps will update the attachment in Master List. To trigger a customized email notification for an item update to the respective team, follow the steps below. Call the action “Send an email” and do the necessary changes in the message content, we can customize the email with HTML code also. Please find the sample below: |
