Mapping SharePoint List Columns to Visio Shape Data Types

The shape data in Visio 2013 diagrams can be linked to many external data sources, and one of the most useful is a SharePoint List. However, the Shape Data rows have a smaller number of expected data types than the column types that are available in SharePoint. This article begins an exploration of the mapping of SharePoint list column types to Visio data types.

The Type cell of a Shape Data row defines the data type, which is a number from 0 to 7, that is stored in that row.

Data Type Description
0 String
1 Fixed List
2 Number
3 Boolean
4 Variable List
5 Date or Time
6 Duration
7 Currency

The Format cell can modify the display of Value, or can provide the option values for the two list types.

There is only basic validation of values entered into Shape Data rows. For example, you will not be allowed to enter text into a number row, or enter a value that is not in a fixed list row, but there is no built-in checking of the number of characters entered, or that a number is between specified values. However, you can write custom validation rules in Visio 2013 Professional to cover these types of checks.

A SharePoint List can have many more column types, and each column can be further specified with a description, restrictions, validations and calculation formulas.

Column Type
Single line of text
Multiple lines of text
Date and Time
Person or Group
Hyperlink or Picture
Calculated (calculation based on other columns)
Outcome choice
External Data
Managed Metadata

So, I created a custom SharePoint List with examples of each of the available column types, and in  cases where there are variations available, I created columns with these variations. This there are 18 columns, in addition to the automatic ones, rather than the 14 column types listed above.

Column Name Column Type Comment
Title Single line of text Automatic
Single line of text Single line of text  
Multiple lines of text -plain Multiple lines of text  
Multiple lines of text -rich Multiple lines of text  
Choice – single Choice  
Choice – multiple Choice  
Number Number  
Number – percent Number  
Currency Currency  
DateAndTime Date and Time  
Lookup Lookup  
YesNo Yes/No  
PersonOrGroup Person or Group  
Hyperlink Hyperlink or Picture  
Picture Hyperlink or Picture  
Calculated Calculated (calculation based on other columns)  
TaskOutcome Outcome choice  
ExternalData External Data  
ManagedMetadata Managed Metadata  
Modified Date and Time Automatic
Created Date and Time Automatic
Created By Person or Group Automatic
Modified By Person or Group Automatic

I then entered some data in each column (with the exception of the ExternalData and ManagedMetadata columns):

The default SharePoint page is shown below:


I then used Link Data to Shapes in Visio 2013 Professional to create the External Recordset, and linked


The default action of Link Data to Shapes is to create Shape Data rows with a Label from the Column Name, and to name the row with a “_VisDM_” prefix, then replacing spaces and special characters in the column name with an underscore character. Also note that the Number columns are assigned a Type=2; Currency columns are Type=7; Yes/No columns are Type=3; and Date or Time columns are Type=5. Visio assumes Type=0 if no Type is assigned.


In addition, two hyperlinks were also created automatically:


These hyperlinks appear on the right mouse menu of each shape:



Text (String) Columns

A text column type in SharePoint can be a single or multiple lines. A single line of text can require a value to be entered, and have a maximum number of characters (default 255), and multiple lines can be plain; rich text; or enhanced rich text.


The Shape Data window in Visio can only display a single line of text, which is unfortunate because each Visio Shape Data value can be 32k characters. The Data Graphic text display below shows that each of the Multiple line variants are actually storing multiple lines, but it is impossible to edit these lines in Visio without a custom interface. In addition, the Shape Data values are plain text, thus the html snippet that holds the rich text is displayed as plain text (though, interestingly, Shape Data values can be XML – called SolutionXML).


Choice Columns

The SharePoint Choice column type can have a single or multiple selection.


The Choice rows are automatically interpreted as just text, and it would be difficult for the multiple variants to be anything else.


However, the single choice variant Shape Data row could be enhanced to a Type=1 (Fixed List) and the Format cell to be updated with the available options:


This would mean that the Visio user could choose between the options:


This then presents a similar experience to that in SharePoint:



Lookup Columns

The SharePoint Lookup column type is similar to the Choice type, and if the number of rows is not too long, then it could be treated in a similar manner to the Choice type.


The data comes from another SharePoint List, and can also have single or multiple selection options. Additionally, related column values from the referenced list can be added as extra columns.


So, in Visio, the Type could be changed to 1, and the Format changed to display the available lookup options, if a single choice is set, and there are not too many rows.


This will be presented as a drop-down list in Visio.



Yes/No Columns

The Yes/No column type in SharePoint is described as a checkbox.


In the default SharePoint List though, this is presented as a drop-down with just No or Yes to select.


These rows are adequately mapped to provide the Visio user with a drop-down list to choose from.



Number Columns

Number columns in SharePoint Lists can have minimum and maximum values assigned, and can be optionally displayed as percentages.


In SharePoint, a Number column that has been assigned as a Percent, is displayed with a percent symbol.


In Visio, the percent symbol is not shown, and the value is one hundredth of the SharePoint displayed value.



Currency Columns

The Currency Format of a column can be defined in SharePoint.


I changed my SharePoint column from US Dollar to GB Pounds, but this did not change the display from USD to £ until I altered the Currency in the Column Settings in Visio.


This setting is translated as a CT(…) formula in the Shape Data value cell.


For example, CY(3000,”GPB”) is displayed as follows:



Date and Time Columns

A Date or Time column can be set in SharePoint


Just like the Currency column, I had to manually change the settings to English (United Kingdom) after changing it in SharePoint.


This change is reflected in the LangID column in the Shape Data section, where the US English value of 1033 is now UK English 2057.


Thus, the display in Visio is shown correctly as dd/mm/YYYY.



In the default SharePoint List, the setting of Date & Time will cause the calendar selector to have extra drop-downs for hour and minute.


Unfortunately, this is not possible in Visio, as the built-in calendar popup does not have this capability, even though the value can be a time including hours, minutes, and seconds.



Person or Group Columns

The SharePoint Person or Group column type references the internal SharePoint users and groups, and can allow single or multiple selections.


Although, the unique identifier is stored, the values displayed are names.


This can only be mapped to a text field in Visio, and there is no built-in interface to select a SharePoint user or group.

Hyperlink or Picture Columns

The Hyperlink or Picture column type in SharePoint are really both storing a URL.


The difference can be seen in the default list appearance, where the image is displayed in the Picture option type.


In Visio, they are both stored as text URLs.


However, they are also presented as hyperlinks on the right mouse menu.



Calculated Columns



Of course, Visio can also have calculations in ShapeSheet cells. For example,  the calculated SharePoint column above, could be expressed as follows:


or, to make it read-only:



Internal columns

The remaining columns, and the ID column at the start, were added automatically from this link. They should either be read-only or hidden.




There are many improvements that could be made to the integration of SharePoint List columns with Visio Shape Data, but in the meantime, the above information should assist on the choices available.


Further reading

Visio Shape Date Type Cells –

SharePoint Calculated Field Formulas –


5 Responses to “Mapping SharePoint List Columns to Visio Shape Data Types”

  1. Creating a Schema from Visio External Data record sets | bVisual Says:

    […] This morning, I realised that I should use my little macro on the SharePoint List that I created for my last post – […]

  2. Using SharePoint Rich Text Multiple Line Columns with Visio Services | bVisual Says:

    […] in March, 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. In […]

  3. Using SharePoint Choice Columns with Visio Services | bVisual - for people interested in Microsoft Visio Says:

    […] 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 […]

  4. Sam Duncan Says:

    I have a database, well – a Sharepoint list now, which is a cable database for our Datacenter. Switches can have numerous cables connected to them. I want the Shape Data to show each cable. When I do a link to data and select the row to use as an example, it only pulls in that one row. The same when I select link automatically. I am using the DeviceName column to match the data and the shape. Or, is it pulling in all of the rows which have that switch name, and I am just not seeing them? Thank you, in advance, for taking the time to reply.

    • davidjpp Says:

      If I understand you right, you have multiple rows with just the DeviceName as the key?
      You can have the DeviceName and port number as a combined key, then you would have one row per port.
      However, you can only have one row linked to a shape from the same recordset, but you can have rows from multiple recordsets.
      If I was you, I would create a Pivot Table from your data so that you get just one row keyed on the DeviceName, but multiple columns with one for each cable.

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: