Update a SharePoint List from a different Sharepoint List

[Alex_CCI]

I am creating flows that will send data from List A to List B.  The primary flow creates a new item in list B when one is created in list A.  That one is easy and works.  The second flow is intended to update list B when an item is updated in list A.  So far, I can't get it to work (flow below).  One issue it seems is the way I set up the list columns in list B, instead of using the SharePoint ID, Modified, and Modified By columns, I used custom columns of ID-A, Mod-A, and Mod By-A.

The List A ID date is entered in the ID-A column in List B.  Although this works great for creating the item.  This seems to be a problem for updating the items.  The "update" flow using the When an item or a file is modified action fails at the Update Item step.  I need a way to tell the flow which item to update.

FYI- If using an excel spreadsheet would work better for "List B," I'm happy to do so.  Using a List for B is not necessary, I just thought it would be easier.

[ViditGholam]

Hi @Alex_CCI  may be when you are creating a new item in your list B you can store the ID of this Item in your list A and then your Update would also be simple and easy

[Alex_CCI]

Can you be more specific, not sure what good storing the ID would do, if there is no way to use it to tell the flow which is the unique field to use to determine what row to update.

[Alex_CCI · Accepted solution]

SOLUTION - Add and Update Data from One List to Another (A to B)

Goal

User needs a “one-way” transfer of data.  When data is added to List A, the flow will add the new item to List B.  In addition, when an item on List A is modified (changed), the flow will update the same item in List B.  This flow allows Team A members to keep “official” clean data that can’t be modified by Team B members.  While Team B members need “view” access to all data, they can’t make changes to List A data, but they can make additions to List B columns that are designated for them.

Solution

Components

A key component of the solution is to use the Compose action to filter out the ID and the Version number from the ID (the “everything ID” which is the triggerOutputs()?[‘body/ID’]).

I use the following expression to get just the ID number:

int(outputs('Flow_gets_the_data')?['body/ID'])

This gets the ID from List A as a number.  That number is used to find the right item in List B.

I use the following expression to get just the Version number:

int(outputs('Flow_gets_the_data')?['body/{VersionNumber}'])

This gets the Version from List A as a number.  That number is used to determine if this is a new item or if this is a modification.

Flow Description

When a LIST A item is added or modified, the Flow gets the data and using the Compose action, the ID and Version are filtered out as numbers.  If the version is equal to 1, it is a new item and the item is added to LIST B and the flow stops.  Otherwise, the flow updates LIST B using the ID number filtered using the Compose action. If the word Delete, is found in the Delete column, the flow deletes the item from Flow B and A and a post is sent to the Team channel.  Otherwise, an “Item Modified” post is sent to the Team channel.

Sample List

These columns were used in testing.

List A

ID

Modified

Modified By

Title

Data-01

Data-02

Data-03

Comments

Delete

List B

ID

Modified

Modified By

Title

Data-01

Data-02

Data-03

Comments

Data-B-01

Data-B-02

Notes

Mod-A

By-A

List B uses the Modified and Modified By fields to track when Team B members make modifications to the item.  The Mod-A and By-A fields let Team B members know which Team A member originally added the item.

Issues

Delete- Users must remember to use the Delete List field to remove an item.  Either “Delete” or “delete” can be entered.  However, users must NOT use the LIST delete feature.

Calculations- If you are working with numbers (like calculating a salary), the Calculate List column will NOT show up as an option when adding Dynamic Content.  This is really a problem.  Just have a “calculate” column if both List A and B.  In additional testing I used the following “IF” formula to calculate the salary based on two different salary types (one based on credits of teaching and another based on a fixed salary).

=IF(Credits>1,Credits*[Credit Rate],[Current Salary]+[Salary Increase])

Watch the Microsoft Sharepoint Lists - Complete Beginner Tutorial YouTube videos for some help.

• 10:44 - SharePoint List Calculated Columns
• 13:10 - SharePoint List Views

Flow Outline (PIC of table is also below)

#	Step (What is happing)	N?	Action	Notes
01	When a LIST A item is added or modified		When an item is created or modified (SharePoint)	List Name = List A
02	Flow gets the data		Get item (SharePoint)	List Name = List A
03	Flow gets the ID for the Item (as a number)		Compose (Data Operation)	Formula int(outputs('Flow_gets_the_data')?['body/ID'])
04	Flow gets the Version for the Item (as a number)		Compose (Data Operation)	Formula int(outputs('Flow_gets_the_data')?['body/{VersionNumber}'])
05	If the item is NEW		Condition (Control)	Compose Version
06	Flow adds data to LIST B	Y	Create item (SharePoint)
07	Flow Terminates Successfully	Y	Terminate (Control)
08	LIST B is updated with changed data		Update Item (SharePoint)	Compose ID
09	If Delete is found in DELETE column		Condition (Control)	Use: outputs('Flow_gets_the_data')?['body/Delete’]
10	Flow deletes the item from LIST B	Y	Delete item (SharePoint)	Compose ID
11	Flow deletes the item from LIST A	Y	Delete item (SharePoint)
12	Item data has been DELETED post is sent to Teams channel	Y	Post a message as the Flow bot to a channel (Teams)
13	Item data has been UPDATED post is sent to Teams channel	N	Post a message as the Flow bot to a channel (Teams)