cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wi11iamr
Advocate II
Advocate II

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
v-bacao-msft
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-bacao-msft
Community Support
Community Support

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.

View solution in original post

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!

CW
Post Patron
Post Patron

I'm trying to do a very similar thing BUT, where does one learn to get the value of a lookup field in flow you have to use this expression body('Get_Lookup_ID:_Colour')?['value'][0]['ID'] and all the associated sections???

Is it all really that complicated??

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (2,308)