Mapping SharePoint List Columns to Visio Shape Data Types

David Parker

10 years ago

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

Choice

Number

Currency

Date and Time

Lookup

Yes/No

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:

=Prop._VisDM_Number*Prop._VisDM_Number__percent

or, to make it read-only:

=GUARD(Prop._VisDM_Number*Prop._VisDM_Number__percent)

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.

Conclusion

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.