Using SharePoint Choice Columns with Visio Services

Back in March 2014, I wrote about mapping SharePoint Column Types to Visio Shape Data Types ( see https://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 https://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.

image

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

image

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:

image

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.

image

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:

image

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:

image

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 Winking smile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: