Using #JSON text in #Visio shapes

I often create Visio masters with a fixed number of Shape Data rows, and sometimes I get requests to modify them because of changes in business needs. Well, my previous posts about Graph Databases got me thinking if it would be possible to embed JSON text in Visio shapes in some sort of meaningful way. Then the user could enter just attribute-value pairs at will, without the need for me to modify their master shape. Alternatively, the data could be inserted into the shape from a database, such as SQL server. So, in this article I discuss the suitability of JSON text in Visio shapes.

This slideshow requires JavaScript.

First, the easy solution is merely to create a text Shape Data row on a shape. It can hold 64k characters, however the default Shape Data dialog and window only displays a single line! This makes viewing or entering the data very tricky, even if you can copy and paste multi-line text into it.

Of course, I could code a custom interface to allow multi-line text entry, but there is a quicker solution. The Insert \ Screen Tip command opens a multi-line dialog in order to enter the Comment cell value. This really is the cell name because it was originally used to store a comment. Of course, we want to re-direct this to a Shape Data row that we create, so, say we create a Shape Data row named Properties, then we can enter the formula =SETATREF(Prop.Properties) into the Comment cell, and this will mean that the dialog can be used to View or Edit the value in that cell. Also, we can create an Action row with the Action =DOCMD(1685), which will open Screen Tip dialog. Unfortunately, the dialog cannot be re-sized, and you need to use CTRL+Enter to create a new line, but it is built-in … so no custom code.

So, now we have a method of viewing or editing multi-line text, I will take a closer look at the JSON format.

JSON is entered as name-value pairs, separated by commas, and curly brackets hold objects, whilst square brackets hold arrays. The names must be enclosed in double-quotes, and values must be a string, number, object, array, boolean or null ( see  JSON Data Types ). This compares with the Visio Shape Data types of text, fixed list, number, boolean, variable list, datetime, duration and currency ( see Type Cell (Shape Data Section) ).

This is an example of a JSON object that might be entered into the Properties shape data row:

{
 "name": "Elephant & Castle",
 "cuisine": ["American",
 "Bar",
 "British",
 "Pub"],
 "address": "1415 5th Ave, Seattle, WA 98101-2313",
 "rating": 4,
 "reviews": 308,
 "price_range": {
 "from": 2,
 "to": 3
 },
 "url": "https://www.tripadvisor.co.uk/Restaurant_Review-g60878-d464056-Reviews-Elephant_Castle-Seattle_Washington.html",
 "website": "http://www.elephantcastle.com/seattle"
}

Notice that there are examples of text, numbers, and array and an object, but I am only expecting one-level of sub-objects.

I tried applying the Properties shape data value as a Data Graphic Text callout, but it looked awful because there was so much text. Therefore, I had to find a way of extracting individual values from the JSON object. This is the best solution because it means that numeric values, such as the rating in this example, could be referenced by Data Graphic Data Bars, Icon Sets and Color by Value.

To do this, I added a couple of User-defined Cells and a couple of Shape Data rows per Name / Value pair.

JSONShapeSheet

The User.JSONText formula strips out any Tab characters, CHAR(9), and removes spaces after a colons. This makes it easier to process the text in the Prop.FindnValue cells. The User.Findn formula merely adds enclosing double quotes to whatever has been entered into the Prop.FindnName Shape Data row, labeled as JSON Name n. Then the User.FindnEnd formula finds the character start position of the requested JSON Name, and adds the character length to it.

The JSON Name is then found in the JSON object by the formula in the Prop.FindnValue cell, and it is processed according to the following:

  • If there is nothing entered in Prop.FindnName then return an empty string.
  • Remove any carriage returns, CHAR(13), and line feed characters, CHAR(10).
  • If the first character of the value is a { then process as an object.
  • If the first character of the value is a [ then process as an array.
  • If the first character of the value is a then process as a string.
  • Else process as a number.
=GUARD(
IF(LEN(Prop.FindnName)=0,"",
SUBSTITUTE(
SUBSTITUTE(
IF(STRSAME("{",MID(User.JSONText,User.FindnEnd,1)),
SUBSTITUTE(
MID(User.JSONText,User.FindnEnd+1,FIND("}",User.JSONText,User.FindnEnd+1)-User.FindnEnd-1),
"""",""),
IF(STRSAME("[",MID(User.JSONText,User.FindnEnd,1)),
SUBSTITUTE(
MID(User.JSONText,User.FindnEnd+1,FIND("]",User.JSONText,User.FindnEnd+1)-User.FindnEnd-1),
"""",""),
IF(STRSAME("""",MID(User.JSONText,User.FindnEnd,1)),
MID(User.JSONText,User.FindnEnd+1,FIND("""",User.JSONText,User.FindnEnd+1)-User.FindnEnd-1),
MID(User.JSONText,User.FindnEnd,MIN(IFERROR(FIND(",",User.JSONText,User.FindnEnd),0),
IFERROR(FIND("}",User.JSONText,User.FindnEnd),0),
IFERROR(FIND(CHAR(13),User.JSONText,User.FindnEnd),0))-User.FindnEnd)))),
CHAR(13),""),
CHAR(10),"")
)
)

The values are guarded to prevent accidental destruction of the formulas by overtyping in the Shape Data window.

Although I am not testing for well-formed JSON text, I think I have shown that simple JSON objects can be stored in Shape Data rows, and that the values can be extracted for use in Data Graphics, or even for custom formula references to enable actions such as varying the width of lines according to some value. It could even be used to automatically create hyperlinks.

I plan to add this capability to the Node and Edge shapes in discussed in my previous articles :

Using #Visio and #PowerBI with #GraphDatabase in #SQLServer

Using #Visio and #PowerBI with #GraphDatabase in #SQLServer – Part 2

JSONWithGraph

 

 

 

 

 

 

Advertisements

4 Responses to “Using #JSON text in #Visio shapes”

    • Dmitry Says:

      Next logical could be to generate JSON text out of shape data

      • davidjpp Says:

        I think that would be a fairly trivial bit of code from a single shape, but I imagine that you are really wanting a nested JSON objects from a number of shapes, including containment & connectivity?


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 )

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 )

w

Connecting to %s

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

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

Smart graphics for visual people

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: