Using SharePoint Choice Columns with Visio Services

Back in March 2014, I wrote about mapping SharePoint Column Types to Visio Shape Data Types ( see ), and I skipped over the remaining SharePoint column types that could not be directly mapped. I described Rich Text previously ( see ), 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:


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


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:


You can also test for the presence of an item


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:


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


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

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

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another 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

Smart graphics for visual people

%d bloggers like this: