Update items from a SPO list with multiple values from different list items (vlookup with multiple matches)

[webmagic2022]

I have the following scenario for a People search solution in SPO. The list needs to be updated with Eduction information from another list.

1. SharePoint List A: Employees

This list contains employee information with columns: EmpID, Name, Email and Phone and is used to search employee information.

 2. SharePoint List B: Education

 This list contains education information from each employee. Due the structure of the external HR system multiple records will be created for each degree obtained. The employee id may appear multiple times in the list, as an example below:

Goal:

I would like to setup a flow by using the action Get Items and perform a for each loop to get the items from List B (Column Education) and synchronise them with action Update Item to the “Education” column of List A. Each value obtained needs to be separated with a dash.

List A: populated with Education information from list B

I have been thinking for a while but it is difficult to find a good solution to collect items in an array and update each item with the values from the Education column from List B.

So far; my flow is as follows:

1) Manually Trigger

2) Get Items from List A

3) Get Items from List B

4) Set Variable (this is where I am stuck on how to proceed further)

Anyone can give some guidance to accomplish this?

Thanks in advance for your help!

[edgonzales]

@webmagic2022 

Hi there.  You're very close.  Here's what I might try:

• Get the items from list A
• Get the items from list B
• Inside the following Apply to each loop, cycle through each EMP ID from List A
• First action inside the loop is a Filter Array where the array being filtered is List B and the criteria (left side "Choose a value") is EmpID from List B, and the right side is an expression: "items('Apply_to_each')?['EmpID']" or something similar (without the double quotes).  This basically says "Show me all of the records from List B, where the empid is equal to the one we're looking at from list A.
• This will (hopefully) give you an array of ALL of the columns from List B that match.  So use a Select action on the output of that step and only pull out the Education column.
• Now, you'll have an array with possibly multiple rows of just the education columns that match that EmpID.  So use a Compose action with an expression using Join() where the parameters are the output from your Select, and the pipe as your delimiter.  Something like this: join(body('Select'),' | ')
• Use the output of that compose to update the education column in List A

Hopefully, that's enough to get you started.  Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[webmagic2022]

@edgonzales 

Thanks for your great explanation. Today I started to follow your guidance. But I got stuck to get the result I expect.

What I have done so far:

List A contains employee information and is the list to populate the Education information. In Dutch "Opleidingen"

Personeelsnummer=EmpID from list A

List A:

List B contains the educations for each employee. For each education a separate row.

The flow I have created:

Get Items from LIST A and LIST B

Result after update items: ,not correct

Any clue? 

[grantjenkins · Accepted solution]

@webmagic2022 This is what @edgonzales was suggesting.

For this example, I'm using the following lists: ListA and ListB. Note that the Title field in both lists contains the Emp ID.

Full flow below. I'll go into each of the actions.

Get items List A and Get items List B retrieve all the items from the two lists.

Apply to each iterates over the values from List A.

Filter array uses the value from List B and filters where List B Title is equal to List A Title (the Emp IDs).

Select uses the output from Filter array and uses the following expression to extract just the Education values. Note that I use Text mode here to get a simple array of values (see arrow on screenshot below).

item()?['Education']

Finally, Update item will add the Education values joined by " | ". The expression used for the join is:

join(body('Select'), ' | ')

After running the flow, we should have the following output in List A.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[webmagic2022 · Accepted solution]

@grantjenkins : Thank you very much, you make my day!

Thanks for detailed explanation and Power Automate example you have created! 

Also many thanks to @edgonzales ! 

Best wishes for 2023! 

[jairamjidgekar]

Hi @grantjenkins 

Thank you for the explanation. 

When I perform the same steps, my lookup columns still coming as empty array in the "select" operation. 

 "Topics": [],

"Topics#Id": [],