This is a continuation from Part 1 of the blog post Setting a Multi-Value Lookup Column Using SharePoint Designer Workflow – Part 1. In Part 1 I wrote about the scenario in which we wanted to connect a document library and a custom list using a multi-value lookup column. Part 1 outlined the formats required to set or update a multi-value lookup column using a SharePoint Designer workflow but only goes as far as a single update. What if we wanted to keep the current value and append the new ID?
In this second part I will outline how to gather the current set of lookup values and successfully concatenate them together. As I have done before, I prefer to show the whole workflow and then break it down in sections.
The workflow is built in 2 stages. The first stage gets all of the information and the 2nd calls an SP 2010 workflow to run the actual update. (This was something that I called out in Part 1 as a requirement to get this to work).
The first thing we need to do is pause. This workflow runs on creation of a document and has multiple required fields. We need to allow time for them update all of the info and save the document prior to the workflow starting. The next 3 fields are setting the variables that we will use throughout the workflow.
- v_related_deliverable_intID is set to the ID of the Related Deliverable field which is the lookup to the custom list in which we will be updating the lookup column
- v_deliverabledocs is the multi-value lookup field in which we are updating but first getting their current value. We are getting this based on the ID of the Related Deliverable field
- v_currentID is the Current Item ID (this variable is actually not required and is mainly used for debugging)
The last action here is to log all of these IDs in the workflow history so we know what items are being updated. Here is an example of the the log action:
- v_deliverabledocs is returned as “510,494″ which means that the multi-value lookup column currently contains items with the IDs of 510 and 494.
- We are updating the item in the list with the ID of 77.
- The ID of the current item that we want to add is 572.
We need to have the IDs of 510, 494, and 572 updated to the multi-value lookup column for the ID of list item 77. We can’t just add 572 to the variable as it does not include the ;#;# delimiter. We need the value entered to be 510;#;#494;#;#572.
Checking the variables
The next section begins the if statements.
The first if statement checks to see if there are any current items in the multi-value lookup column. If there are no values there is nothing that needs to be appended so it can be treated as a single entry. If there is a current value it begins the process.
The first step in the process is to see if there is more than 1 ID is currently in the field. A ‘find substring in string’ action is used to check for a comma in v_deliverabledocs. The next step is to set a variable to hold the delimiter ;#;#. We then need to check if there is more than 1 item in the field. If there was no comma found that means there is only 1 item and we do not need to parse the strings. If there is a comma found we need to do different actions.
Setting the variables
The primary if statement checks to see if more than one ID is returned.
If more than 1 ID is returned a ‘Replace substring in string’ action is used to replace the comma with the proper delimiter and output it to a variable. In this example it takes “510,494” and turns it into “510;#;#494“. The second action sets the variable v_temp_deliverables to the output variable from the prior statement and adds the delimiter so it now is “510;#;#494;#;#”. This variable is logged to debug the process and ensure the variables are being set correctly.
If only 1 ID is returned than we set the variable v_temp_deliverables to the single ID returned along with the addition of the delimiter variable.
End of stage 1
The variable v_temp_deliverables is set with the proper IDs and an open ended delimiter.
The first action here sets the variable v_deliverableids to the existing v_temp_deliverables plus the Current Item ID. This gives us the proper format to update the multi-value lookup column. If there was no existing value in the column we set the variable to just the Current Item ID. This information is also logged for debugging and history.
Running the update
The last step is to call a SharePoint 2010 workflow passing the variable v_deliverableids.
Here is the 2010 workflow which updates the item in the list using the passed along parameter.
Hopefully these 2 posts will help you if you are in a similar situation.