I was recently asked how to link data from different tables but with similar column names to Visio shapes. In this case, each table has the same unique identifier, but some of the column names are the same. The problem is that the data linking matches the column name with the label of a Shape Data row label, so the shared columns were updated with the values from the last linked data recordset. In this article I present a simple way that this restriction can be overcome.
As the images above show, the solution is to rename some or all of the column names so that they can all be linked to the Visio shapes using Data | External Data | Custom Import or Quick Import. I don’t use Quick Import much because I prefer the better control that I can get with Custom Import for Excel tables. I have used Power Query in Excel via Data | Get & Transform Data | From Table/Range to create these examples, but it could also be done in, say, SQL Server to create Views or Stored Procedures.
In this example, new queries, qryX and qryY, are created from the source tables, tblX and tblY, and the non-unique id columns are renamed.
The first option is to link each these queries to Visio shapes separately as multiple data recordsets.
The second option is to merge qryX and qryY together to make a new query, qryXY, and link just this single data recordset to the Visio shapes.
The following images shows how these two options manifest themselves in the Visio UI. In each case, the shapes have five Shape Data rows, and they are both using the ID column as the primary key.
There could be operational reasons for choosing one option over the other, such as the frequency that each recordset gets updated and the diagram refreshed.
Related articles
Linking Data to Shapes in Visio after using Data Visualizer
Data Visualizer (DV) in Visio Plan 2 (Data | Create from Data | Create ) is great because it provides a way of automatically creating a diagram from data, but it also prevents some of the other data-linking features in Visio from being used. This is because DV wants to take control of the data…
Using Visio Color by Value on Connectors
Data Graphics in Visio Plan 2 and Visio Professional is great, but it only enables us to use them with 2D shapes in Visio, i.e. not on connectors. So, what if you want to change the line colour of the connectors between the 2D shapes because of the data flowing between them? Well, it is…
Pushing Data Visualizer in Visio to the limits!
Regular readers of my blog will know that I like to use the Data Visualizer (DV) in Visio Plan 2, but I recently tried to help a user who really decided to push it to the limits. In this scenario, there were multiple connections, but with different labels, being created between the same flowchart shapes,…
Linking Data to Visio Shapes in Code
One of the most useful capabilities of Visio Professional and Visio Plan 2 is to link external data to shapes and have them refreshed by changes in the data source. So, many of my solutions involve writing code to make these links, and they are covered with some VBA examples in my book, Mastering Data…
Update any Visio ShapeSheet cell with External Data
When Microsoft introduced a new way of linking external data to Visio shapes in 2007, I initially bemoaned the inability to update anything but Shape Data row values, unlike the old database add-on that I had been using for 10 years. The new method, though, has many advantages over the old way, not least that…
Visio 2010 MVP Session videos reprise
Back in 2012, my fellow Visio MVPs, Scott Helmers and Chris Roth, and I recorded a series of 24 videos about Visio 2010. They were first hosted on Microsoft’s web site, then they put them up on YouTube, they they got deleted :-(. Well, we have managed to retrieve them, and put them back up…
Leave a Reply