How Can I Update a Multi-value Lookup Column

If I've well understood what you are trying to do is to copy the value of a multi lookup column to paste it in a multi lookup column in another list ?

Do you also need help to get the value of a multi lookup column ?

That is correct.  Both are multi select lookup columns pointing to the same parent list.  So if the lookup column in List A from List C (Parent) has values of Cat, Dog, Bird I want to update the lookup column in List B (Same List C Parent Column) to also store values of Cat, Dog, Bird.

ok and you need to query List A to get items that have Cat, Dog and Bird values for the multi value lookup column ? Or do you want to loop through all items of list A ? How do you know which items of list B have to be updated ?

I already know which list items need to be updated based on separate criteria that links the two list items together - I'm just struggling with how to update the lookup columns

For a multi lookup value, it should look like this :

2;#Cat;#3;#Dog;#4;#Bird (item ID;#item Title;#another item ID;#another item Title)

To get this value from an item, you can call the following web service using "call web service" action :

• Url : your_site_url/_vti_bin/lists.asmx
• Web method : GetListItems
• Editor mode : SOAP editor
• Web service message :

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">

  <soap:Header>

  </soap:Header>

  <soap:Body>

    <m:GetListItems>

      <m:listName>The name of the list where you want to retrieve the multi lookup value</m:listName>

      <m:query>     

       <Query>

        <Where>

         <Eq>

          <FieldRef Name="ID" LookupId="TRUE" />

          <Value Type="Text">ID of the item where you want to retrieve the multi lookup value</Value>

         </Eq>

        </Where>

       </Query>

      </m:query>

      <m:viewFields>

       <ViewFields>

        <FieldRef Name="internal name of the multi lookup column" />

       </ViewFields>

      </m:viewFields>

    </m:GetListItems>

  </soap:Body>

</soap:Envelope>

• Web service output : Default value
• Store result in : single_line_of_text_variable

Then to extract only the value of the column, you can use a Query XML action :

• XML source : XML
• XML : single_line_of_text_variable
• Process using : XPath
• //@ows_internal name of the multi lookup column
• Return result as : Text
• Store result in : single_line_of_text_variable_to_store_result

Then in your variable single_line_of_text_variable_to_store_result  you will have the correct format of the multi lookup value column and you will be able to update the item using the "Update item" action and this variable.

Hope it helps

Thanks!  I'll give this a try     

Hi Caroline,

Why would you prefer to use the Call webservice rather than the 'Query List' action. It's much simpler to implement (but less powerfull of course), but in this case, it would make the job.

I think it doesn't return the multi lookup value in this format :

2;#Cat;#3;#Dog;#4;#Bird

Hi Jonathan,

for updating those kind of columns, please refer to this post on my personal blog. Hope that helps.

Caroline Jung​ answer is correct if you want to go down the path of using web service calls, however, Alexandre JOLY​ suggestion is more user friendly and power user focused. Depends on the requirements and audience at the end of the day

Cheers,

Pat

Thanks Patrick - I've read your post before, that method assumes you know the values you want to update correct?  In this scenario I don't know the values that will be updated since they come from a lookup.  I think that's where I always stumble.         

From List A you get the values from the field. They should come back in a certain format, I think the ID only? Easiest to find out the format from the lookup value is to log it in the history. If it is the ID only, that is coming back, you can construct the workflow to query List C for the missing value attribute and then construct the complete string which should end up looking like this before you update List B: 2;#Item2;#3;#Item3;#4;#Item4

So I am clearly pretty dense here.  I think I understand the string I am trying to construct.  I can't even get this to log to history for a test.  Are you using the query list action or the set workflow variable action?  When setting a workflow variable for each type (ID and value) what variable type are you using?  Integer for ID? 

When setting a workflow variable as an integer to the lookup column in List A and logging it to history I get validation errors when trying to publish.

Conceptually the difficulty I have in understanding how to build the string is that I don't know how many attributes exist prior to building the string - so it might be 1 item, 3 items, 4 items.  Can you simply set two workflow variables (one for the ID and one for the value based on the ID from the ID variable lookup) and construct the string as {workflow variable: ID};#{workflow variable: value};# without knowing how many will be returned?

I've tested several ways of doing that and here's the easiest one I've found :

• create a single line of text variable (for example txtLookupFromSetVariable)
• use "Set variable" action like the following (where "Lookup" is your multi lookup value column) :

The variable txtLookupFromSetVariable is correctly formatted (1;#Item 1;#2;#Item 2) and you can use it to directly update your item.

Hope this helps

Thanks Caroline.

Logging the result to the workflow history I receive a different format {"results":[{"Id":5,"Value":"7"},{"Id":4,"Value":"Item 4"}]} which doesn't allow you to update the next list - and throws an error:

An unexpected 'PrimitiveValue' node was found when reading from the JSON reader. A 'StartObject' node was expected.

How do you get this result ?

Are you using O365 ?

set workflow variable action, variable type, text, then list lookup.  Yes I'm using O365.

OK, sorry, I thought you was using OnPrem.

Well, you can manipulate this result to get it into the correct format :

• use "Replace Substring in String" to replace {"results":[{"Id": by nothing
• use "Replace Substring in String" to replace "}]} by nothing
• use "Replace Substring in String" to replace "},{"Id": by ;# (I don't know if it will replace all th substrings or only the first one)
• use "Replace Substring in String" to replace ,"Value":" by ;# (I don't know if it will replace all th substrings or only the first one)

And it should look like the correct format (I hope)

Do you create a new variable for each replacement then rejoin them together or keep updating the original variable?

You can keep updating the original variable and logging the result to the workflow history to be sure it has the correct format

This is somehow what I end up with

,"Value":"

In the configuration of the "Replace Substring in String" action, have you entered ,"Value":" in the "Search String" field ?

How do you set the variable for a Lookup column for O365? I'm trying to do something similar by updating a lookup column in a List B.

Maybe I've inverted things - but here is how I have it configured:

You use the set workflow variable action, and then select the target list, and fields.