Utilizing the new Related Items column via workflow – Part 2

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

Utilizing the new Related Items column via workflow – Part 1

This is part 1 of a 2 part serious about using the Related Items column in SharePoint 2013.

I ran into an interesting request recently that ended up with an interesting solution. The request that I had was based around connecting a list and a document library. I built a custom list that was used to track deliverables for a project and all of the information about them. At first all actual document deliverables were “attached” to the list item when it was completed. This was not a good approach as attachments are not crawled and indexed. To fix that first issue, I created a separate document library that would hold the deliverables and added a lookup column back the deliverable list. Here are screenshots of the list and library with the lookup column.

Deliverable List

2014-09-12 21_49_56-Deliverable Tracker - BR 1.1 - Program Benefits Definition - Internet Explorer

Deliverable Documents

2014-09-12 21_47_04-Deliverable Documents - BR1.1 - GPP Deliverable Signoff.pdf - Internet Explorer

This was a good approach to tie the list item and a document together. But then the next question was asked…

If I am on the deliverable list, why can’t I see what documents are connected?

While pondering an answer I recalled that there was a new column in SharePoint 2013 called Related Items. I had used that column in the OOTB (out of the box) way with such things as tasks and videos but I had never tried to manipulate it myself. So I first went to try to add that column to my list to test it out but it was not listed as an available column to add…

Getting the Related Items column

This column is part of the hidden group (_Hidden) by default which does not display in the site columns list. This column can be found via the Task content type and updated to a new group. These are the steps to unhide the column:

  1. Navigate to Site Settings and click Site content types in the Web Designer Galleries section.
    2014-10-05 20_44_11-Site Settings - Internet Explorer
  2. Under the List Content Types section click on Task.
    2014-10-05 20_48_54-Site Content Types - Internet Explorer
  3. Click on the Related Items task link.
    2014-10-05 20_49_40-Content Type - Internet Explorer
  4. Click Edit site column.
    2014-10-05 20_50_25-Change Content Type Column - Internet Explorer
  5. Change the Group settings from _Hidden to an Existing group (such as Custom Columns).
    2014-09-24 21_18_23-Change Column - Internet Explorer

The Related Items column can now be added to a list or library! So I went ahead and added the column to my deliverable custom list and was ready to start figuring out how to use this column effectively.

In the next part I will discuss how to work with the Related Items column via a SharePoint 2013 workflow created in SharePoint Designer.

Link to part 2

Utilizing the new Related Items column via workflow – Part 2

Interact with list fields based on SharePoint Group

A very common question that is asked around the SharePoint community is about the ability to interact with item forms. These forms are how users view and edit items in a list. Changes to these forms can accomplished in a few different ways.

  1. Use SharePoint Designer to create an editable New, Edit, or Display form
  2. Edit the default New, Edit, or Display form pages using web parts.  (The edit page link for these forms can be found in the site settings via the gear menu.)
  3. Using a customized Infopath form (for now…)

Using any of these options it is possible to edit the forms in both a basic and advanced way. If you are creating new forms with SP Designer you can edit the HTML of the form directly and have control over nearly everything that is displayed.  You can do some easy customizations that can be very helpful for the end user experience. One example involves the hiding of a column (such as status) on the default edit form. These are the steps to do this in SP designer:

1.  Create a new Edit Form using SP Designer on the corresponding list

Edit1 - 2014-09-08 11_16_41-

2.  Locate the row that contains the column you want to hide and use HTML comment tags (<!– & –>) to comment out the row.

Edit2 - 2014-09-08 11_19_16-.aspx

One big disadvantage of this approach is that as columns are added, removed, or renamed the form will not reflect these changes. You will need to manually go update the field changes on each custom form that you created. Also this scenario hides the field for everyone that goes to the list. 

What if we wanted to only hide the status column for users of a certain SharePoint group?

This can be accomplished using jQuery and one of the best libraries available, the jQuery SPServices library. The files that you need and the instructions to get started using this library can be found on codeplex. Some great examples of using these services can be found on the blog of the creator of this wonderful tool, Marc Anderson.

Continuing on the example above of hiding a status column, let’s work towards the requirement of hiding that column unless you are in the SharePoint group “Approvers”. 

Here is the script that you can put into a Script Editor web part on the default Edit Form:

<script language="javascript" src="https://sitecollection/SiteAssets/jquery-1.11.1.min.js">
    </script>

<script language="javascript" src="https://sitecollection/SiteAssets/jquery.SPServices-0.7.2.min.js">
</script>

<script>
$(document).ready(function () {

if(checkrole('Approvers')){
$("[id^=Status]").closest('tr').show();
}
else {
$("[id^=Status]").closest('tr').hide();
}

function checkrole(groupname) {
    var IsvalidRet = false;
    $().SPServices({
        operation: "GetGroupCollectionFromUser",
        userLoginName: $().SPServices.SPGetCurrentUser(),
        async: false,
        completefunc: function (xData, Status) {

            if ($(xData.responseXML).find("Group[Name='"+groupname+"']").length == 1) {
                IsvalidRet = true;
            }
        }
    });
    return IsvalidRet;
}

});
</script>

Script Breakdown

The first 3 javascript calls load the appropriate libraries on the page. These calls can also be placed at a higher level (such as the master page) so they do not need be called on every page. The download files for jQuery can be found here and the SPservices can be found in the link earlier in this post.

The next script section starts with the document.ready() which detects the state of readiness of the page for you. Code included inside of this will only run once the page Document Object Model (DOM) is ready for JavaScript code to execute.

if(checkrole('Approvers')){
$("[id^=Status]").closest('tr').show();
}
else {
$("[id^=Status]").closest('tr').hide();
}

The if statements calls the checkrole function and passes along the group name that you want to check to see if a user is a part of. If the statement returns true it shows the status column and if it returns false it hides the status column. The lookup checks the page for ID’s that contain the word Status. You can use a browser development tool to inspect elements on a page to get their generated IDs. You should not take the section of the ID that is the GUID.

Edit3 - 2014-09-08 20_33_57-Tasks - ..

The last section of the script is the checkrole function. This uses the SPServices library and the SPGetCurrentUser function to return the users data. It then scans through the responseXML to find the group name and sets the variable to true.

function checkrole(groupname) {
    var IsvalidRet = false;
    $().SPServices({
        operation: "GetGroupCollectionFromUser",
        userLoginName: $().SPServices.SPGetCurrentUser(),
        async: false,
        completefunc: function (xData, Status) {

            if ($(xData.responseXML).find("Group[Name='"+groupname+"']").length == 1) {
                IsvalidRet = true;
            }
        }
    });
    return IsvalidRet;
}

The big disadvantage of this option is that hiding a column does not actually secure the column from the user, it just hides it on the form. If this user has access to this column in quick edit or datasheet view directly in the list they would be able to change it.

These are some options to get started with interacting with list forms after the announcement of the death of InfoPath and a great tool in SPServices that can take the SharePoint user experience to a new level.

Not folders or metadata, why not folders & metadata?

SharePoint is built and ready for content management but it can be a challenge to take advantage of the tools it provides. The question of whether folders or metadata should be used is a question that is asked over and over in every SharePoint project and SharePoint community. The 2 primary methods for organizing content in a document library is with folders and metadata. This is a topic that is very important and can make or break a successful SharePoint project roll-out. Everyone always asks what is the best strategy? Most SharePointers will quickly jump to “Folders are bad! Metadata is good!”. As a consultant must say though, it depends.

Are folders evil?

Everyone knows what content management using folders looks like. Managing content in folders was happening well before computers. When I was in middle school I had my trapper keeper and then awesome folders for each of my classes. I had my primary folder which was my trapper keeper and my nested folders for my classes. That same premise was done in basic file shares in computers. That premise has since been migrated into content management solutions like SharePoint. Content is stored in a collection of nested folders in a document library and organized in a way that probably made sense to the person who set them up, but may or may not make sense to others.

There are multiple disadvantages to folders which are highly publicized. There are more that can be discussed but these are the major ones that come up the most.

  • URL length limitation   —   Overall URL length is limited to 256 characters
  • Forced grouping   —   Everything file HAS to belong to a folder and cannot fall into multiple categories
  • File URL   —   Moving a file from one folder to another means the URL of the file changes
  • Folder naming   —  Folder names cannot contain special characters
  • Workflows   —   They cannot be performed on folders
  • Lack of control of information architecture or taxonomy   —   A folder can be edited by anyone with contribute rights which could break a standard
  • Internal knowledge   —   When people turn over information is lost which can include the original rationale behind a folder structure

What about this metadata thing?

Metadata is one of the strongest factors that places SharePoint amongst the top collaboration platforms. Metadata allows for the tagging of content with attributes that classify the content. For example an mp3 loaded onto a computer has attributes like Artist, Album Title, Track length. These attributes help describe what song it is so you can quickly listen to what you want. Can you imagine going through a deep folder structure just to get to your songs? How about not being able to quickly sort by artist or doing a search by most plays? This metadata tagging has great advantages for content management. As with music tagging, once you apply metadata to documents it is then possible to provide better searching, filtering, categorization, grouping, and sorting. This allows you to look at data in different ways with the capability of enterprise metadata across multiple documents.

But metadata may not fit everyone’s needs. There is a large planning and discovery phase that has to occur prior to launching a metadata structure to ensure it covers all different kinds of content across teams. After the discovery this structure needs to be developed and implemented. And after it is developed it will require more advanced change management and training than if folders were just being used. This can be a very new philosophy to people that can be challenge to understand as it is being adopted.

So what about those folders again?

After knowing the disadvantages of folders and advantages of metadata there is another point that needs to be made which is the advantage of folders. Foremost with any SharePoint projects is end-user adoption and acceptance. It should be a goal to have sustained adoption of your project. This involves an extended adoption that does not drop off over time. People love folders and at times taking a strict stance against folders can just push people away from your sites. You could build the fastest, prettiest, most secure SharePoint farm in the world but it would be completely pointless unless people use it. And user-adoption is not the only advantage of folders.

  • Permissions   —   They can be set at the folder level and any documents residing in that folder inherit permissions
  • Default metadata tagging   —   Default values for metadata can be set based on the folder it is located in
  • Bulk upload   —   It is easy to drag and drop many files into a folder either via the browser or in explorer view

Can we use both?

YES!  It is completely possible and in some circumstances best to use both. Why not give an end user the comfort of putting a document into a folder and automatically adding metadata when they do it? Why not build an admin view where the content owners upload their documents in a folder structure but end users have a view which “shows items in all folders” with sorting and filtering?

Now using a hybrid approach you still fall under the same disadvantages stated earlier in this post. That means proper governance and maintenance of an approach like this is required for it to succeed. There cannot be deeply nested folders or many changes to the structure. There needs to be proper planning up front for the long term goals of the library, folders, and content to ensure a sustained solution.

One solution that we at Concurrency provide is the creation of an effective content management strategy. This strategy will help decide the best use cases for taking advantage of all of the tools that SharePoint has to offer while ensuring end-user adoption and a successful SharePoint project.