Using SharePoint Choice Columns with Visio Services

Back in March 2014, I wrote about mapping SharePoint Column Types to Visio Shape Data Types ( see http://blog.bvisual.net/2014/03/03/mapping-sharepoint-list-columns-to-visio-shape-data-types/ ), and I skipped over the remaining SharePoint column types that could not be directly mapped. I described Rich Text previously ( see http://blog.bvisual.net/2014/11/14/using-sharepoint-rich-text-multiple-line-columns-with-visio-services/ ), and in this article I describe some of the possibilities of using the SharePoint Choice column type.

There are three different options for Choice columns in SharePoint 2013, but only the third, Checkboxes, allows for multiple selections.

In the SharePoint list item edit screen, the user is presented with a list of checkboxes:

In Visio, after using Link Data to Shapes, the Shape Data is displayed as a list, and I have also displayed the ShapeSheet of one instance:

Actually, each item is separated by two characters ;# and the whole list is enclosed with the same two characters:

=”;#Enter Choice #1;#Enter Choice #2;#Enter Choice #3;#”

Visio lists are normally only separated by a single character, with the default being a semi-colon, and the whole list is not enclosed with any extra characters apart from the double quotes. However, you can replace the two characters with a single character in order to get other Visio ShapeSheet functions to work properly:

=SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)

Therefore, you can get the value of an item in the list using the INDEX() function:

=INDEX(2,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”))

Which would return Enter Choice #2 in this example.

Note that I could have entered two further optional arguments for the INDEX() function to specify the delimiter and the error value:

=INDEX(2,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”),”;”,””)

You can also test for the presence of an item

=LEN(INDEX(2,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)))>0

Which would return TRUE if there is a second item, or FALSE if there is not.

With these functions, you can control the display of text or sub-shapes, or even the presence of hyperlinks.

I would normally advocate pre-creating the Shape Data rows, and any other cells that refer to values in them, in a master shape before using Link Shapes to Data. This would ensure consistency and efficiency, and would make your shapes automatically re-configurable with data refresh using Visio Services.

In the following example, I have shown 5 instances of the Multi Choice master, with each linked to a different row from a SharePoint list.

I used INSERT \ Field \ Shape Data to insert Prop._VisDM_ID as the first line, then INSERT \ Field \ Custom Formula to insert a longer formula to give me each selected option as a different line:

The custom formula assumes that there are a maximum of 5 choices made:

=IF(LEN(INDEX(1,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)))>0,INDEX(1,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”))&IF(LEN(INDEX(2,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)))>0,CHAR(13)&CHAR(10)&INDEX(2,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”))&IF(LEN(INDEX(3,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)))>0,CHAR(13)&CHAR(10)&INDEX(3,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”))&IF(LEN(INDEX(4,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)))>0,CHAR(13)&CHAR(10)&INDEX(4,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”))&IF(LEN(INDEX(5,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)))>0,CHAR(13)&CHAR(10)&INDEX(5,SUBSTITUTE(Prop._VisDM_Choice__multiple,”;#”,”;”)),””),””),””),””),””)

Then I added a Data Graphic item to show how many items were selected:

The custom formula, in this case, can use the alternative notation for a Shape Data row, so {Choice – multiple} instead of Prop._VisDM_Choice__multiple :

=(LEN(INDEX(1,SUBSTITUTE({Choice – multiple},”;#”,”;”)))>0)+(LEN(INDEX(2,SUBSTITUTE({Choice – multiple},”;#”,”;”)))>0)+(LEN(INDEX(3,SUBSTITUTE({Choice – multiple},”;#”,”;”)))>0)+(LEN(INDEX(4,SUBSTITUTE({Choice – multiple},”;#”,”;”)))>0)+(LEN(INDEX(5,SUBSTITUTE({Choice – multiple},”;#”,”;”)))>0)

Easier when you know how