How to update an item in a SharePoint list base on the changes from another SharePoint List?

[Chase_Ho]

HR department is having a list to reocrd the information of every employees (now we call this list as "Employee Profile").

On the other hand, HR also requires team leaders to upload the resigination letters to another list as well (we call this list as "Resignation Log").

Once HR see that there is a new resignation letter received, she would find out the Employee ID from "Resignation Log", then change the status at "Employee Profile" base on the Employee ID she found.

And I would like to know that could we create a flow to modify an item in a list once an item created in another list?

[v-xida-msft · Accepted solution]

Hi @Chase_Ho,

Could you please show more details about the Employee Profile list and the Resignation Log list on your side?

I have created a Employee Profile list on my side and the data structure of it as below:

Note:  The Employee ID and Employee Name are both Single line of text type column. The Department column is a Choice type column. The Status column is a Choice type column and the available values of it is In-service and Departure. 

I have also created a Resignation Log list on my side and the data structure of it as below:

Note: The Employee ID column is a Single line of text type column, the Resignation Date is a Date type column.

I have made a test on my side and please take a try with the following workaround:

• Add a "When an item is created" trigger, specify Site Address and List Name set to Resignation Log list.
• Add a "Get items" action, specify Site Address and List Name set to Employee Profile list.
• Add a "Filter array" action, From set to output of "Get items" action. Click "Edit in advanced mode", type the following formula:

@equals(item()?['Employee_x0020_ID'], triggerBody()?['Employee_x0020_ID'])

• Add a "Apply to each" action, input parameter set to output of "Filter array" action.
• Within "Apply to each" action, add a "Update item" action, specify Site Address and List Name set to Employee Profile list. Id field set to following formula:

item()?['ID']

Title field set to following formula:

item()?['Title']

Status Value field changed to Departure.

Image reference:

The flow works successfully as below:

The Changes within Employee Profile list and Resignation Log list as below:

Best regards,

Kris

[Chase_Ho]

Kris, Thank you very much for your suggestion below. I am sorry that i am out of office now which I could not have a test on it, but I think it is workable and inspire me a lot. On the other hand, may I know that how could I learn the syntax rules to write formulas in creating flows (or other Office 365 functions) like you did below? I am just good at using excel and VBA, not sure if I am qualified to do so, it would be better if there is some instruction for me to learn. Thank you again. Best regards, Chase

[v-xida-msft]

Hi @Chase_Ho,

Microsoft Flow has its own expression rule, if you want to use expression within flow actions, please check the following article:

Using expression in flow actions

More details about the WDL expression, please check the following article:

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

Best regards,

Kris

[jav-226]

Hi Kris,

I wanted to develop similar function using SharePoint list. 

Do you mind if I ask what is the x0020 in the Employee ID meant for?

Employee_x0020_ID

[JKat]

x0020 is a space. e.g. the Sharepoint field name would have been "Employee ID", so it becomes 

Employee_x0020_ID

If you look at the Sharepoint list via Site Libraries and lists (i.e. open the settings page for tha list), the click on the field as it to edit it, you can see the correct format of the name at the end of the page address.

[JessBerry]

Hello, 

I have found this information quite helpful however I am havingone issue. The connecttion seems to be set up and working however it is changing the status of all entries in my second list. 

I am creating an acceptance form for students in a program. When a student accepts their seat it should look them up by their Student ID and change their status in my student contact list from perspective to current. However when I test this, it is changing ALL my students on my list to current. 

Any thought on what I might be doing wrong or where to look? 

[Anonymous]

HI, 

i am trying to recreate an exact table of yours with very similar situations and objectives, the only difference is my employee ID is not a 4 digit number but instead a code (AA123,AB165) and hence the filter array is unable to locate my employee ID. and suggestions of how to solve this so i can lookup the unique employee ID from the parent list

Thanks!

[Anonymous]

Hello If the Employee ID is of type 'String' on both the sharepoint, will it work ?

[NicoleOuyang]

Hi Kris,

Your answer is very helpful. However, I have two more required fileds in the sharepoint "Employee Profile"

1. User ID: type of this filed is "Single line of text"

2.Join Date:  type of this field is "Date and Time"

Could you please share me more how to write the fomular for this two columns in the step "Update Item" (Under the step "Apply to each"). Becauese this two fileds are required to fill in the setting of "Employee Profile".

Thanks!

[chaule]

Why do your need the Filter Array step?  Can't you just filter the list right in the Get Items?

[Powerplatform01]

Hi,

Please watch

https://youtu.be/639qnKw85n8

Please like and subscribe this video.I hope it's work for yoh

[Anonymous]

Hi there - I just had this happen. Did you ever figure out how to fix this issue?

[skghosh]

Hi,

I am creating a Leave Request Form in PowerApps, added Power Automate flow. Everything working fine.

Now, I need to update my LeaveBalance List (AppliedLeave Column) with the data from Leave List(TotalDays column) once the Leave is approved.

Need your urgent help and advice on this please.