Making Your Database Play with Visio's SmartShapes

• Print

Step 4: Light It Up

Before you can teach the Shapes any new tricks, you must go to Tools, Options, click the Advanced tab, check Run in Developer Mode, and click OK. Now, when you right-click a shape, you'll see the option Show ShapeSheet. Select this option, and you'll open a whole world of possibilities in the form of spreadsheet-like storage for all sorts of information, from art in graphical form to quasi–Excel-style formulas. We're going to write a formula that says, basically but less stylistically, "If there are enough syringes, you'll see a comforting, traditional red cross; if the syringes fall below the reorder level, the red cross will turn as black as the plague." Here's how to do it:

1. Right-click the first cross and select Show ShapeSheet.

2. Go down to the Fill Format section of the ShapeSheet. (Each cell has its own ShapeSheet.) The first cell in the Fill Format section is FillForegnd. Click to highlight it.

3. You're going to overtype the simple HSL (Hue, Saturation, Luminescence) or RGB (Red, Green, Blue) color instruction that's already there. You could do so within the cell, but you might be stepping on your feet since there's not much room there, so go up to the formula bar below the menu and toolbar at the top of the page. As you type this formula, which we'll parse in a minute, you'll get a feel for the "language" of SmartShapes:

4. `If(Prop.UnitsInStock<=Prop.ReorderLevel,RGB(0,0,0),RGB(255,0,0))`

The If sets up the conditional statement, which is set off in parentheses. We're basically saying that if the units in stock are less than or equal to the reorder level, then display the shape in black; otherwise, in red.

TIP

Not familiar with using RGB color numbers in decimal? Try this chart.

5. Hit Enter, and you'll see the color from the very first formula. Repeat steps 1–2 for each of the shapes (all the cross-type objects, not the "art for art's sake" objects, if any), but paste the formula to all your crosses by selecting each one, opening its ShapeSheet, and pasting the formula into the FillForegnd line in the Fill Format section (see Figure 8).

Figure 8 With the sponge cross highlighted in the drawing, and FillForegnd highlighted in the Fill Format section of the sponge cross' ShapeSheet, the sponge cross will take color as soon as someone hits Enter in the formula bar (upper left, by the X and checkmark buttons).

And huzzah!!! What a feeling! All the right things turn red and black! See Figure 9 for proof.

Figure 9 The colors reflect the reorder levels in the database.

There's a list of functions tucked away under Help, Developer Reference, Microsoft Office Visio ShapeSheet Reference, Functions. You can use XML and ActiveX controls with Visio, and download the Visio SDK.

Maybe for your next act, you can have automatic email sent when the coffee can gets low...