Utilizing the new Related Items column via workflow - Part 2

Author by Drew Madelung

This is a continuation from Part 1 of the blog post Utilizing the new Related Items column via workflow. In Part 1 I wrote about the scenario that I had in which I wanted to connect a document library and a custom list using both a lookup column and a Related Items column. This post discussed how to make the column visible so it can be added to a list or library. In this post I will discuss a strategy to use a SharePoint 2013 Workflow created by SharePoint designer to interact with the Related items column.

 

To solve my scenario of showing what documents are connected to the deliverable list, I added the Related Items column to the custom Deliverables list. My hope was that I could perform these steps when documents are uploaded to the deliverable documents library:

  1. A document is uploaded to the deliverable documents library and a selection of the matching lookup from the deliverable list is chosen.
  2. The related items field on the corresponding deliverable list is updated with the document that is uploaded.
  3. As more items are added for the same deliverable list item the related items are appended on top of each other.

I found the format for updating the Related Items column from this blog post by Pieter Veenstra.

This is the format for a single record in Related Items:
[{"ItemId":13,"WebId":"b95aa3f5-5fc2-4df7-b690-7381971e8ab7","ListId":"7fb76569-48d5-45a6-9224-116ee234c304"}]

This is the format for multiple Related Items:[{"ItemId":13,"WebId":"b95aa3f5-5fc2-4df7-b690-7381971e8ab7","ListId":"7fb76569-48d5-45a6-9224-116ee234c304"},{"ItemId":14,"WebId":"b95aa3f5-5fc2-4df7-b690-7381971e8ab7","ListId":"7fb76569-48d5-45a6-9224-116ee234c304"}]


I just needed to find a way to use variables to input this data. I created a list workflow that happened on change and creation that was able to successfully perform these steps using variables! Here is the overall workflow and I will break down the sections below.

2014-09-24-21_44_22-https___my.jci.c[2]

Setting Initial Variables

2014-09-24-22_13_16-https___my.jci.c[1]

This section first pauses for a duration to allow the document to be fully checked in and then sets 2 variables.  The first variable, v_relate_deliverable_intID, gets the ID of the current item lookup value. The second variable, v_temp_related, gets the current status of the Related Items column in the Deliverable list that corresponds with the current item lookup value.

2014-09-24-22_02_04-Lookup-for-Strin[1]

This information is needed to ensure that we don't overwrite any data that already exists in the Related Items column. This variable is then used in the IF statement. This statement checks to see if there are already Related Items or not.

If we need to add to already existing Related Items

The primary steps of this section build the variables that are needed to take the existing Related Items string value and add on the new document's string value. 2014-09-24-22_15_42-https___my.jci.c[1]

The first 2 Find statements get the index number at the beginning and the end of the existing Related Item string where the outside brackets exist. For example, a broken down version of an item with 3 Related Items where we want to add a 4th would look like this: [{Item1},{Item2},{Item3}]. The brackets are existing at Index 0 and 24. These values are stored in the variables v_index_1 and v_index_2. Our end goal is to get [{Item4},{Item1},{Item2},{Item3}]. The next 2 Calculate statements add 1 and subtract 1 from the index to be able to perform the substring actions. Continuing my example this sets the 2 new calc and calc2 variables to 1 and 23. The next statement which starts with Copy from... is actually the action named "Extract Substring of String from Index with Length." This takes the indexes that we calculated with calc variables and outputs it to the variable output. Continuing my example this variable would now equal {Item1},{Item2},{Item3}. The next 3 statements are used to build the new Related Item string for the item that we are adding. The format should look like this {"ItemId":"[%Current Item:ID%]":WebID":GUID","ListID":"GUID"}. 3 statements are needed because of a restriction in SharePoint Designer workflows. When trying to put that into a Set workflow variable action you receive the error:

"Using the special characters '[%%]' or '[%xxx%]' in any string, or using the special character '{' in a string that also contains a workflow lookup, may corrupt the string and cause an unexpected result when the workflow runs."

2014-10-20 22_07_23-https___my.jci.com_sites_enterpriseIT_unity_pmo

To get around this error, the strings can be built separately and then combined. v_temp_start = {"ItemId": and v_temp_end = "WebId":GUID","ListID":"GUID"}. The next command sets the variable output2 to [v_temp_start][Current Item ID][v_temp_end].  This gives us the full Related Item string into a variable. Continuing on the example above we now have {Item4} The final statement in this section combines the already existing Related Items with the brackets removed with the new Related Item and wraps them in brackets. It sets the variable v_related_items to [[output2],[output]] as seen in the screenshot below. 2014-10-20 22_21_06-String Builder

If there are no existing Related Items

The steps of this section use the same logic as above to get the string value needed for the new Related Items text. We just need to get the string [{Item1}] without manipulating any already existing Related Item text. 2014-10-20 22_24_12-https___my.jci.com_sites_enterpriseIT_unity_pmo

These steps are identical to what is used and are broken into 3 statements to avoid the "Special characters.." error.

Updating the Deliverable List

The last statement before the transition to the end of the workflow is an "Update list item" action that is used to take the newly created Related Items string and update the corresponding item in the Deliverable list. 2014-10-20 22_29_34-https___my.jci.com_sites_enterpriseIT_unity_pmo

We are able to perform this step because we have the Related Deliverable column on our document library which we have put in the variable v_related_deliverable_intID. That means our update action can state in words with a screenshot below:

Update the Related Items column to the variable v_related_items for the item in the Deliverable list in which the Current Item ID matches v_related_deliverable_intID.

2014-10-20 22_32_38-Update List Item

Wrapping it up

Part 1 of this blog series discussed the scenario of connecting a list and a library using both a lookup column and the new Related Items column. This would allow visibility to the the connected content from both the list and the document library. First we needed to make the Related Items column visible and able to add to a list. Part 2 discussed how to update that column using a SharePoint designer workflow. Below is a final screenshot of a list item with Related Item links that were automatically added to it when a document was uploaded to a corresponding library. 2014-10-20 22_37_12-Deliverable Tracker - Incomplete - Internet Explorer I also added some JSLink on the web part to display the actual related items instead of just a count. But that is for another blog post! (maybe part 3?)]]>

Author

Drew Madelung

Technical Architect