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
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:

image

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

image

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.

image

In addition, two hyperlinks were also created automatically:

image

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

image

 

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.

image

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).

image

Choice Columns

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

image

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

image

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:

image

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

image

This then presents a similar experience to that in SharePoint:

image

 

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.

image

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.

image

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.

image

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

image

 

Yes/No Columns

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

image

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

image

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

image

 

Number Columns

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

image

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

image

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

image

 

Currency Columns

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

image

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.

image

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

image

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

image

 

Date and Time Columns

A Date or Time column can be set in SharePoint

image

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

image

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.

image

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

 

image

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

image

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.

image

 

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.

image

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

image

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.

image

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

image

In Visio, they are both stored as text URLs.

image

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

image

 

Calculated Columns

 

image

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.

image

 

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.

 

Further reading

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

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 – https://blog.bvisual.net/2014/03/03/mapping-sharepoint-list-columns-to-visio-shape-data-types/ […]

  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 https://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. 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 https://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 […]

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Chris Webb's BI Blog

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

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com 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

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: