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.
|5||Date or Time|
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.
|Single line of text|
|Multiple lines of text|
|Date and Time|
|Person or Group|
|Hyperlink or Picture|
|Calculated (calculation based on other columns)|
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 – percent||Number|
|DateAndTime||Date and Time|
|PersonOrGroup||Person or Group|
|Hyperlink||Hyperlink or Picture|
|Picture||Hyperlink or Picture|
|Calculated||Calculated (calculation based on other columns)|
|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).
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:
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.
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 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.
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.
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:
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.
Visio Shape Date Type Cells – http://msdn.microsoft.com/en-us/library/ff766784(v=office.15).aspx
SharePoint Calculated Field Formulas – http://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx
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.
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.