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

Get ID from separate list for update in Sharepoint Lookup Column

I have the following Sharepoint Lists:

Extended Attributes

  1. ID
  2. ItemNo
  3. Vendor
  4. Colour

lstColours

  1. ID
  2. Colour

My flow takes an Excel spreadsheet as input to update an existing Sharepoint list, which contains a number of Lookup Columns, such as Colour. 

In the Excel spreadsheet, users will provide a text value, such as "red", which I then need to translate to the Lookup Columns' ID from lstColours.

Within my ApplyToEach update task, I have separate GetItems tasks which take the text value (E.g. "red") and lookup the asosciated ID from the respective list.

This works and returns something like this:

"@odata.etag": "\"2\"",
    "ItemInternalId": "2",
    "ID": 2,
    "Title": "Black",
    "nf1m": "Black",
    "x3aq": "BLK",
    "Modified": "2019-04-15T00:49:37Z",
    "Created": "2018-12-10T07:22:23Z",

I then try to reference the ID field from the GetItems lookup, using a variety of body() and item() expressions, to no avail. Any help in being able to correctly locate the ID from the Colour array, would be most appreciated.GetLookupID for Item Update.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Get ID from separate list for update in Sharepoint Lookup Column

Hi @wi11iamr ,

 

Could you share more details about the configuration of Get items 2 and the four Get Lookup actions?

Which of the four Get Lookup actions are Get items action or Get item action?

If it's a Get items action, and you're using Filter Query to filter out the item ID for the specified color, you could reference Colour Id using following expression.

 

The precondition for using this method is that the color in lstColours list has a unique value, so Get Lookup action will only get one item.

 

Expression reference:

body('Get_Lookup_ID:_Colour')?['value'][0]['ID']

Image reference:

28.PNG

 

Please take a try.

 

Best Regards,

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

Re: Get ID from separate list for update in Sharepoint Lookup Column

Hi @wi11iamr ,

 

Could you share more details about the configuration of Get items 2 and the four Get Lookup actions?

Which of the four Get Lookup actions are Get items action or Get item action?

If it's a Get items action, and you're using Filter Query to filter out the item ID for the specified color, you could reference Colour Id using following expression.

 

The precondition for using this method is that the color in lstColours list has a unique value, so Get Lookup action will only get one item.

 

Expression reference:

body('Get_Lookup_ID:_Colour')?['value'][0]['ID']

Image reference:

28.PNG

 

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
wi11iamr
Level: Powered On

Re: Get ID from separate list for update in Sharepoint Lookup Column

Thanks @v-bacao-msft , all 4 tasks were Get Items, using an OData filter on the text value, such as "red" for the colour. In each list the text values are unique, so I do only get a single value returned.

For my expresssion reference, I was using

body('Get_Lookup_ID:_Colour')?[0]['ID']

instead of as you pointed out

body('Get_Lookup_ID:_Colour')?['value'][0]['ID']

Thank you for this, it now works like a charm!

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: 18 members 4,304 guests
Please welcome our newest community members: