cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MT2
Level: Powered On

lookup values from 2 lists

 

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: lookup values from 2 lists

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:

Capture.PNG

 

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

Capture.PNGThe flow would run successfully as below:

Capture.PNG  

 

 

 

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.

7 REPLIES 7
Super User
Super User

Re: lookup values from 2 lists

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





Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


MT2
Level: Powered On

Re: lookup values from 2 lists

Hi @faustocapellanj,

 

Please see column details below:

 

Leave Register : Columns

Leave Type: Single Line of Text

Date From: Date and Time

 Date ToSmiley Very Happyate 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

Super User
Super User

Re: lookup values from 2 lists

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.
    UpdateNewItem-1.png

  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.
    UpdateNewItem-2.png

  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']
    UpdateNewItem-3.png
    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





Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Community Support Team
Community Support Team

Re: lookup values from 2 lists

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:

Capture.PNG

 

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

Capture.PNGThe flow would run successfully as below:

Capture.PNG  

 

 

 

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
Level: Powered On

Re: lookup values from 2 lists

 

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"
 
error.JPG
MT2
Level: Powered On

Re: lookup values from 2 lists

Hi @v-yuazh-msft

 

Thankyou for the solution. This works perfectly.

 

Thanks,

 

MT

Community Support Team
Community Support Team

Re: lookup values from 2 lists

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.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fourthImage

Australia Summit

Travel to Melbourne and network with thousands of peers!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 273 members 3,695 guests
Please welcome our newest community members: