• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Visio 2013 / Using SharePoint Choice Columns with Visio Services

Published on January 25, 2015 by David Parker

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.

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)

Winking smile

Easier when you know how

Related

Filed Under: External Data, Shape Data, ShapeSheet Formulas, SharePoint, Visio 2013 Tagged With: SharePoint 2013, Visio Services

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Update to LayerManager add-in for non-English users
  • Pushing Data Visualizer in Visio beyond its limits
  • Pushing Data Visualizer in Visio to the limits!
  • Teams Tuesday Podcast Recording about Visio
  • Linking Data to Visio Shapes in Code

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Themes Validation VBA Video Visio Visio 2007 Visio 2013 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • LinkedIn
  • Twitter

Search this website

Recent posts

  • Update to LayerManager add-in for non-English users
  • Pushing Data Visualizer in Visio beyond its limits
  • Pushing Data Visualizer in Visio to the limits!
  • Teams Tuesday Podcast Recording about Visio
  • Linking Data to Visio Shapes in Code

Copyright © 2023 · Executive Pro on Genesis Framework · WordPress · Log in