lookup values from 2 lists

[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,

MT

[faustocapellanj]

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

[MT2]

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

[faustocapellanj]

Hi @MT2

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

1. For the trigger, select SharePoint - When an item is created. From here, select the Site Address and the List Name.
   
   
   
2. 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.
   
   
   
3. 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.
   
   
4. 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

[v-yuazh-msft · Accepted solution]

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.

[MT2]

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"

[MT2]

Hi @v-yuazh-msft

Thankyou for the solution. This works perfectly.

Thanks,

MT

[v-yuazh-msft]

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.

[amirf13]

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.

[slawrimore]

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

[slawrimore]

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