Help With Shape Data in Office Map

[inshead]

https://imgur.com/a/1vrDopD

So I've created a new office layout map for our corporate office. It's a bit out of the ordinary for most Visio use cases so it's been next to impossible to find similar scenarios to use for reference. Maybe I'm just not using the right terminology but I'll do my best to describe what I'm trying to figure out.

I attached the image link above to hopefully help describe the scenario. The way I created the map was by basically creating an overlay tracing of a semi transparent image of a blueprint. I then edited the configuration and restructuring as needed then the result is just grouped and assigned to a layer intended to be static and stay locked. I end up reusing this "template" for several more diagrams but this specific map is intended to provide a name for each office and desk so everyone knows who sits where.

The way I initially accomplished this was by creating another layer on top of that static "template" and then just created an additional shape that sits on top of each office. Or in the case of a shared office the desk shape would be used. With the shapes I just made them with no fill and no line so they would be invisible but able to be used as a shape to link the data to. I then imported my excel spreadsheet that consisted of several columns (Index/Office #/Name/Title/Dept). Initially all I need displayed is name which is easy enough to do by dragging and dropping each row to the office shape they are located creating the data link.

This is where the tricky part comes in. From the linked rows I used the Name column to populate the employee name in each office but most just didn't look good using the default text callout so after repositioning a lot of them I decided there needed to be a way to easily see how the office was laid out based on responsibilities and teams. Insert me spending way too much time deciding what color to use to represent the major departments and creating the legend based on that then manually updating the fill color for each text box.

My question finally is this: Based on the external data I linked should I assign each shape to a row number (index/office #) FIRST so that when someone leaves or moves offices I can just update the corresponding row number then refresh the diagram? If I do go that route how can I assign a row to an office shape that would both automatically display the employee name based on the corresponding column AND also change the fill color based on the value listed in their Department column. Essentially I would like to have each department assigned a color that would be autofilled when needing to change employees in the spreadsheet. EDIT: To clarify that I know about the Color By Value option of a data graphic but the problem with using that is that it wants to fill the entire hidden office shape instead of the text box with the name.

To make this post even longer the reason I'm needing to figure this out is so that I can publish this to our shared drive and allow HR to handle keeping it updated by only needing to edit the spreadsheet. The office structure itself won't change but people do come and go so having them be able to just edit the spreadsheet in excel and have the data refresh to a timed interval I ideally wouldn't need to spend additional time tweaking this. Sorry for such a wordy post but hopefully I explained this well enough. Thanks in advance.

[Yacine]

Looks like you have everything already almost perfect. Nice drawing on Imgur BTW.


What I would definitely have avoided is to colorize the labels by hand. Not reasonable for such a big drawing.


Luckily Visio isn't just a drawing application.


You'll need to do 2 things:
1) Put some smartness in the label shapes to colorize them based on values of your excel sheet (department)
2) Set up a macro that transform the already drawn shapes, without having to redo all your work.


1) Automatic coloring
Key is to let Visio know the mapping, department to color value.
A good place for storing this information is the page's shapesheet.
I assume you're working in developer mode (Google it, if you aren't).


In the shapesheet of the page add two custom properties in the prop section.
prop.departments and prop.colors
prop.departments gets the department identifier as it stands in Excel. eg: "Executive;HR;Accounting;Project Mgmt" (and so on)
prop.colors gets the list of colors in the order as they are associated with the departments.
The color values can either be color indices (1,2,3,...) or RGB values ( RGB(102,30,45).
Make sure to use the right list separators. eg semi-colon, otherwise you need to specify it later.


Now to the label shapes.
Pick one of them for your trials. Open its shapesheet.
I assume there will be something like "prop.department" in the prop section.
Now you can code the color either directly in the shapesheet cell "FillForegnd" or use an intermediate user field. I recommend the latter, for better legibility.
So add a user field user.color and write the formula:
  INDEX(LOOKUP(Prop.departmentt,ThePage!Prop.departments),ThePage!Prop.colors)
In the field Fillforegnd write the formula : user.color
Now the label shape should color itself automatically.




2) Applying the smartness to the other labels:
I thought first to write a VBA macro, but there is a much easier way to do it.
a) Save the modified label shape into a stencil. eg the document stencil
b) Select all the other labels, Right Mouse Click, "Replace by master" and choose the previously saved label as replacement.
Done.

Now everything is controlled by the Excel sheet. No further editing in Visio. I hope that answers your question.

PLEASE WORK WITH A TEMPORARY FILE.

[Nikolay]

I could suggest my SvgPublish tool, it can export basically arbitrary graphics that you can control from Excel
That is, with it, you could customize every aspect of how the graphics look. You want names in boxes, you may have them; you want the color changing depending on the department, no problem.

It also has some built-in stuff like a sidebar and tooltip where you can show person details in a visually appealing way (i.e. not just a blunt Visio "shape data" window, but some person card)
Also pictures are support (such as photo)

Built an example (on Excel refresh, the boxes can change color, and also the name inside of them):
https://nbelyh.github.io/test/Office2.html

It supports basically any graphics, but now it's somewhat similar to Linux - "you can configure everything, and you will configure everything" (just kidding).

The workflow with the SvgPublish could look like this:
- you create the Excel file. The "key" in that Excel file is the room number.
- you link that Excel file to your diagram.
- you customize the template and publish that to a shared folder /web site/sharepoint, whatever is applicable in your organization

Now the update loop:
- your colleague edits the Excel file, and puts "right" people and their departments in corresponding "room" rows.
- you open the Visio file, click "Refresh" button to get the latest data.
- you click "Publish" in the SvgPublish, and the published version is refreshed.

So from your side, it should be two clicks - refresh data, republish.

BUT, it won't be "live". I.e. if you colleague edits the Excel file, you need to go through the publishing steps above.
Rendering directly from Excel file is not supported by SvgPublish (yet?)

[Yacine]

That is, with it, you could customize every aspect of how the graphics look. You want names in boxes, you may have them; you want the color changing depending on the department, no problem.

I understand the export function, but how do you make that customization from your tool?

[Nikolay]

This is sort of a key feature of it actually... You can use templates to achieve this.
The template can include: CSS (to adjust styles), HTML (to show some data), JS (to provide some interactivity or pre-rendering)

Here is a video, where I tried to explain how it's done from the tool (had nothing better to do today anyway)



Here is the source file for the exported version above:
https://unmanagedvisio.com/upload/Office2.vsdx

The article on templates:
https://unmanagedvisio.com/using-custom-templates-in-svgpublish/

So "this machine can not only make glowing in the dark noses and produce tons of toxic waste, but also decrypt alien languages"

I'm planning to make actually a very basic video this weekend, no complex "SKI" samples or "POKEMON" stuff, just a button and show/hide not to be confusing

[Yacine]

It is indeed very inspiring, I'll jump in again for my P&IDs.

In the example you showed how to link the box to a JS script, but in the previous CSS example you used prop variables. Couldn't you have assigned the coloring class there as well?

In the template dialog the first tab should probably say content, not conent.

[Nikolay]

The JS is not per-shape, it's for a diagram (i.e. just a global piece of code that is injected to target HTML).
That's why I need to loop through the shapes.

Regarding styles - the "simple" template supports only direct text replacement.
So it could have been like this for example (without any code):

Code Select Expand


TEMPLATE:
<div class="{{Props.Department}}">Some Text</div>

CSS:
.Sales {
  color: yellow
}
.Marketing {
  color: green
}


That would work. But consider "Research and Development" for example? It has space inside. The text substibution will be "broken".
So the script also "maps" the department values to simple one-word styles. Or department names must be fixed. Or you need a special property for each shape like "class". But there are options, yes.

But yes, they can be applied directly to the content, there is actually no need to modify the rectangle with that script. It was just what I came up with first 

For another thing, in the template editor, there is no auto-complete ("intelli-sence") for properties. You have to type it, and that's a challenge. What name to use? Label/Name/NameU (actually it's label now)
Just planning to improve that a bit here...

If you decide to try it out, please go with the latest 1.6 release, it has some fixes for these editors:
https://github.com/nbelyh/svgpublish-releases/releases

[inshead]

I could suggest my SvgPublish tool, it can export basically arbitrary graphics that you can control from Excel
That is, with it, you could customize every aspect of how the graphics look. You want names in boxes, you may have them; you want the color changing depending on the department, no problem.

It also has some built-in stuff like a sidebar and tooltip where you can show person details in a visually appealing way (i.e. not just a blunt Visio "shape data" window, but some person card)

Ah, Nikolay! It's honestly an honor to have had you reply to this. And ironic. Your SvgPublish tool is what started me on this path about 3 years ago and gave me a real insight into how useful and versatile Visio really CAN be for those willing to take the time. It was your Pokemon Office map that initially gave me the vision to do something similar. I worked on a version at my previous company but wasn't able to get it right before leaving. Now I'm about 3 months in with this new company and one of the first things I noticed was that there was no documentation or diagrams (office, network, fire escape, etc.). So the first thing I wanted to try to tackle to get noticed was an interactive office layout.

I was SO pumped when I was FINALLY able to get a version to work and get it successfully deployed/published to network drive. I quickly saw that there would be a much more immediate value for a print friendly version so I've had the interactive version on hold for now. As you can see from the link below I had decided to use a fill color for each office based on color then intended for the hover or tooltip to display the information. I had to switch to a different method with the print friendly version obviously which is what led me to the map as it looks today.

I know it's not the same methodology as other somewhat similar diagrams I've seen but I've also not been able to find any examples similar to what I've created. Nikolay, I had downloaded the .VSDX of your Pokemon Office to try to get to the core of how you made each office a clickable unit despite having multiple shapes within. By that I mean, for example, the hover over highlight effect is able to detect and highlight multiple shapes. What I discovered instead was an idea that was going to be more within my scope of capabilities. After looking at your layers I noticed that you had used the Space shape as a lower layer to distinguish each individual office from the one next to it. Eventually through trial and errors I found my own method to work for what I needed by using the "invisible" shape on it's own layer and linking the rows to those shapes.

I did try using data graphics to auto display the values located in the Name column but the results as I needed them were never consistant nor was I able to easily automate the coloring or shape of the text box/callout, In my head it seems like it would be an easy thing to do in having a fill color determined by a value. Maybe if I assigned each text box to a specific layer for them I could then create some sort of rule for that layer only that would fill the text boxes based on the column of values from the external data but I couldn't figure it out or find anything to lead me in the right direction.

I didn't expect to get much of a response by finally posting here but this is also part of what's kept me so interested in using Visio is the community and creators that are so involved and interested in it. So thank you Nikoay and Yacine. These are the kinds of suggestions and options I needed to get me moving in the right direction again. I'm no coder but I've definitely come a long way in the last few years. My job as a sysadmin has pushed me into so many different lanes that I've had to get familiar with a lot of different languages but JS and VB (and even CSS) are not ones I'd add to that list so the coding side of Visio gets a bit intimidating. Which is what has led me to finding other unique methods.

Finally below is a handful of examples of things I've created in Visio over the last year or 2. They aren't really anything too involved but I've been proud of my progress which is easily noticeable when I look back at how my diagrams have improved. Thanks again guys!

https://imgur.com/a/Xs51dUX