Unanswered

lookup values from 2 lists

(1) Share

Report

Posted on by MT2

Hi,

I am fairly new to Flow. I have been trying to develop a flow that looks up values for a column from another list- very similar to vlookup in excel. But I am stuck, not knowing how to proceed.

Premise

I have two lists - Leave Request and Staff Reporting.

Leave Request has 5 columns - Leave Type, Date From, Date To, Employee Name and Line Manager. Line Manager is a lookup column which is referencing "LM" column in Staff Reporting

Staff Reporting has 3 columns - Employee Name, LM, LM Email

Problem

When a new item is created on the list - Leave Request, i want to create a flow where the Employee Name is looked up in the list "Staff Reporting" and the corresponding value for LM is returned and stored in the column Line Manager.

Can someone please help with the above scenario?

Thanks,

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

faustocapellanj 1,662 on at

Like (0)

Report

Hi @MT2

Can you please post the column type for each of the columns in both list? I will assume that DateFrom and DateTo are Date columns and Leave Type is a Choice column, but what about the rest?

Regards,
Fausto Capellan, Jr

Was this reply helpful? Yes No
MT2 12 on at

Like (0)

Report

Hi @faustocapellanj,

Please see column details below:

Leave Register : Columns
Leave Type: Single Line of Text
Date From: Date and Time
Date To:Date and Time
Employee Name: Single Line of text
Line Manager: Single Line of text (Lookup column)

Staff Reporting: Columns
Employee Name: Single Line of text
LM: Single Line of text
LM Email: Single Line of text

Thanks,

MT

Was this reply helpful? Yes No
faustocapellanj 1,662 on at

Like (1)

Report
Hi @MT2

This should be a quick one with the approach I'm taking. Please follow the steps below:

For the trigger, select SharePoint - When an item is created. From here, select the Site Address and the List Name.

For the next step, add a SharePoint - Get items action, which you will use to query the StaffReporting list. Select the Site Address and the List Name, and then click on Show advanced options on the lower left corner. Once expanded, build a Filter Query similar to this: EmployeeName eq 'Employee Name'. The EmployeeName in red is the internal column name; eq is equals; and 'Employee Name' in blue wrapped in single quotes is the dynamic content available from the SharePoint - When an item is created trigger. The screenshot below shows everything for this step.

For the last step, add a SharePoint - Update item action, which you will use to update the newly created item in the LeaveRequest list. Select the Site Address and the List Name, and for the Id field, select the ID from the When an item is created dynamic content. And for the Line Manager field, enter the expression below:

first(body('Get_items')?['value'])['LM']

What this expression does is to retrieve the LM value from the SharePoint - Get items action. You may wonder why the expression. I'm using that because when Flow runs the SharePoint - Get items action, it returns an array even if there's only one item in it. And every time Flow deals with arrays, it adds an Apply to each action automatically. So, to bypass the automatic Apply to each action, I used the first() function in the expression.

Finally, save your Flow, test it, and provide us feedback.

Please, do not hesitate to reach out if you have any questions.

Regards,

Fausto Capellan, Jr

Was this reply helpful? Yes No
Verified answer

v-yuazh-msft on at

Like (4)

Report

Hi @MT2,

I have made a test on my side, if the "Line Manager" column is of type "lookup", it is not possible to update the "Line Manager" column for your scenes.

Please modify the "Line Manager" column to Single of text column, and then you could refer to screenshot below to create the flow:

When an item is created in the Leave Request list as below:

The flow would run successfully as below:

Best regards,

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
MT2 12 on at

Like (0)

Report

Hi @faustocapellanj

I tried the flow that you suggested but it fails at the "Get Items" step.

And throws the below error :
"status": 400,
"message": "Column 'EmployeeName' does not exist. It may have been deleted by another user.\r\nclientRequestId: a5ede1bc-ca08-4306-be36-aa3e8f005ad7\r\nserviceRequestId: 28bfbf9e-707d-8000-1226-3654ea4fe789"

Was this reply helpful? Yes No
MT2 12 on at

Like (0)

Report

Hi @v-yuazh-msft

Thankyou for the solution. This works perfectly.

Thanks,

MT

Was this reply helpful? Yes No
v-yuazh-msft on at

Like (0)

Report

Hi @MT2,

If your problem has been solved according to my solution, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best regards,

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
amirf13 12 on at

Like (0)

Report

Hi, I am trying to create the same flow and its running ok but the "update item" is not happening. when i'm going to check on the test area in Power Automate i can't see what is wrong. Both fields are text.

Was this reply helpful? Yes No
slawrimore 8 on at

Like (0)

Report

What was the solution to ""message": "Column 'EmployeeName' does not exist. It may have been deleted by another user.\r\nclientRequestId: a5ede1bc-ca08-4306-be36-aa3e8f005ad7\r\nserviceRequestId: 28bfbf9e-707d-8000-1226-3654ea4fe789" I do not see a response. I am having the same stop at Get Items.
Thanks
Stan

Was this reply helpful? Yes No
slawrimore 8 on at

Like (0)

Report

I found the issue. It all stems from sharepoint allowing you to change the visible name of a field, but does not change the internal. I had to start over with a new list in order for the names to match. I could have looked up the internal name in SP, but wanted it to be less confusing.

Thanks
Stan

Was this reply helpful? Yes No