Get ID from separate list for update in Sharepoint Lookup Column

[wi11iamr]

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.

[v-bacao-msft · Accepted solution]

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:

Please take a try.

Best Regards,

[wi11iamr]

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]

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??