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.

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.

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