Making Your Database Play with Visio's SmartShapes
Date: Aug 6, 2004
Art That Works
What if you could get a great visual to show which computers in the organization need an upgrade before they'll support the big boss' new pet application? If somebody in the IT department has that info in a database, they can link that data to SmartShapes in a Visio drawing and, presto! The pre-upgrade computers will display as orange, rather than tan. Made the upgrade? With the help of a quick refresh, the shape will reflect the change to the database by changing to tan.
Maybe your organization wants to roll out Office 2003, but you know that some commissioned programs on a few computers only work with certain versions of Microsoft Access. Those types of problems would be another occasion to fire up a shape drawing with red flags or landmine symbols representing that data. If you can easily summon such an image from the proper database with very little notice, Visio might help shape the way your IT department keeps track of equipment—and what's on it. Whatever you want to highlight and track, we'll get you started with this how-to, using an example from the ordering department of our midsize, SmartShaped hospital, Eye Tee General in Meshuggeneh Falls, New England.
Step 1: Roll Up Sleeves and Numbers
It starts with the database, of course. We'll need some sample data to work with for this article, so let's get a database going. If you're really starting from scratch, the simplest way to go is to run the wizard in Microsoft Access. If you can't find the database wizard anywhere on the menu or toolbar and want this process to be easy, follow these steps:
Choose the option that looks the toughest compared to templates: File, New, Blank Database.
Type a name for your file on the line that says Blank Database *.mdb (use MedStock.mdb for this example) and click the Create button.
A dialog box opens, and the second option in the dialog box is Create Table by Using Wizard. Double-click that option.
Make sure that the radio button by Business is selected; then click Products in the Sample Tables drop-down list. The Sample Fields will change in the drop-down list next to that.
Click ProductID and then click the right-arrow button to make ProductID appear in the far-right window. Add ProductName, UnitsInStock, and ReorderLevel in the same way (see Figure 1). Click Next.
Name your table Product Reorders, select the option to let Access set the primary key, and click Next.
Choose Enter Data Directly into the Table and select Display Help on Working with the Table. Click Finish.
)
Figure
1 Adding columns to the sample table.
Now, whether you're an Oracle aficionado or any other kind of database daemon, start typing entries in the database fields of the Product Reorders table, using the following data (especially using our data for the last two columns). Skip the numbers under product ID; those will be filled in automatically.
|
Product ID |
Product Name |
Units In Stock |
Reorder Level |
|
1 |
Autoclave |
2 |
1 |
|
2 |
Suture |
15 |
20 |
|
3 |
Syringe |
115 |
50 |
|
4 |
Forceps |
6 |
3 |
|
5 |
Sponge |
60 |
50 |
|
6 |
Retractors |
12 |
17 |
|
7 |
IV Kit |
20 |
15 |
|
8 |
Surgical Pack |
60 |
60 |
|
9 |
#10 Blade |
21 |
19 |
|
10 |
Sharps Disposal |
5 |
2 |
Step 2: Get Visual
Now we're ready for Visio:
Open Visio.
Choose File, New, New Drawing (US Units).
Choose File, Save As, and type a filename (use Supplies.VSD for this example).
Select the text tool and create the list of supplies from the database in the drawing space, as shown in Figure 2. Note the Product ID number from the database within each text drawing.
From the File menu, choose Shapes, Block Diagram, US Units. A Shapes Template appears.
Scroll until you see the cross. Click the Pointer tool on the toolbar; then click the cross and drag it above the text drawing for Autoclave. Resize the cross until it looks like the one in Figure 3.
Copy the resized cross and paste it above each text drawing.
If any shapes or text drawing objects refuse to line up, use Ctrl-click to select more than one; then choose Shape, Align Shapes from the menu. A dialog box appears with graphical representations of the types of alignment you might want, as shown in Figure 4. Make your selections and choose OK.
Before going any further in Visio, you should make a decision about the artwork you want to use. For this example, we're going to use or modify shapes that come with Visio and will change color to indicate the order levels of the supplies at Eye Tee General Hospital. But we want more precise static representations of each type of supply to go along with those indicator shapes.
TIP
Ask your supply vendor if it's okay to snag photos from their web site for your in-house database, and then paste the static objects onto a background, leaving room for Visio's SmartShapes next to each.
For this example, we're going to use Visio's Text Tool to create "text drawing objects" (instead of photos) representing the products. If you place the Visio SmartShapes above the Visio text objects, it will appear that the SmartShapes are the objects, and the text objects are just labels for them, which may seem more logical to people than a photo of a product plus a shape object.
NOTE
One little rule from the start: Include the Product ID number in all art for the products. Later, when you're matching database records to anonymous shapes to pieces of art, you'll never miss the mystery.
)
Figure
2 Creating text drawing objects that contain product ID numbers and product
names from the database.
Now you can select or create the SmartShapes to go with your products or text objects. The SmartShapes may as well be one type for this demonstration, just to keep it moving. The cross in Basic Shapes is reminiscent of a Red Cross symbol. We'll grab that and pop it above the first text object in the drawing (Autoclave).
)
Figure
3 Dragging a cross shape to the drawing area and resizing it with the
Pointer tool.
)
Figure
4 Aligning shapes on command.
Now you're ready for the Viz Wiz.
Step 3: Reel In Your Data
These steps are a hand-pull through the wizard; it's a boring read, but it will show you what Microsoft should have put in their docs and had to do for me over the phone. There's even a step beyond the wizard for linking shapes to records in the database, before you can even think about getting the shapes to change colors for different conditions.
In Visio, select the first cross.
From the Tools menu, choose Add-Ons, Visio Extras, Database Wizard. Choose Next.
Make sure that the radio button is selected for the option Link Shapes to Database Records; then choose Next.
Choose Shapes in a Drawing and click Next.
Make sure that the name of your drawing is in the top of this next dialog box, and that the page number at the bottom of your drawing is in the middle of the dialog box, as shown in Figure 5. If you're on the correct page, you should be able to scroll to the correct shape name in the list ("cross," in this example). This is a dialog box that can trip you up. It says Select Shapes, and you'll be tempted to highlight just the word Cross. You need to select all 10 crosses in the drop-down list. This can be fixed in post, but you don't want to do it. Instead, select all of those crosses. (It's okay if their numbers aren't 1–10, as long as there are 10.) Click Next.
Choose the type of data source you're using (Microsoft Access database, for this example) and click Next.
Select your database file. Choosing a database object to connect to is probably best left at the default settings; same with the object types. Tables and Views should be checked already; Product Reorders should appear in the Database Objects scroll list. Make sure it's highlighted. Leave the Define Table button alone and click Next.
In the dialog box to choose the number of fields that comprise the primary key for the selected table, choose 1 (one). Click Next. For the primary key field, choose Product ID and click Next.
For the default key value, choose None—it's a button below the place where normal selections are made, but it's important to choose it (see Figure 6). Click Next.
In the following dialog box, you choose events and actions to add to the shape. Skip the Shape (on Drop) events. For Right Mouse Actions, choose Select Database Record and Refresh Shape Cells. For security reasons, skip Update Database Record and Delete Shape and Database Record. Click Next.
Now it's time to choose the shape cell to be used for storing the primary key field value. The correct default will be a variant on the primary key itself, so if your primary key is ProductID, the shape cell will be Prop.ProductID. Click Next. The Link ShapeSheet Cells to Database Fields dialog box has an Automatic button that should put together names such as the above, though with a more formulaic flair. Click Next; then click Finish (see Figure 7).
Now you're outside the wizard. (Aren't you glad Inform IT is here?) Right-click the first shape in the drawing and choose Select Database Record from the pop-up menu. A list of all the record's primary key values appears. Choose the first record's primary key value (or the one that matches the first text drawing or photo (Autoclave is 1), and click OK.
Right-click the next shape and choose its primary key value (2, and so on), and click OK through the inventory. Because the primary key values are just numbers (and in this case, those numbers happen to be the Product IDs), it's a good thing we included the Product ID numbers in the static art, right?
)
Figure
5 Match filename at top of dialog to filename at top of page. Scroll to
match page number in the middle of dialog to page number at bottom of page.
Select all 10 crosses contained in the page, regardless of numbers beside them
in the dialog box.
)
Figure
6 Don't skip clicking the None button in this dialog box.
)
Figure
7 Visio presents the three database field names you need in the second
column. When you move them to the right, they get the names they need as
they're linked to ShapeSheet cells.
Your database is hooked up to Visio! Take a celebratory break and refresh yourself with a cool Frapp Lite, or work just a few more minutes to see your SmartShapes refresh themselves with a cool black "light" when they dip into a low inventory condition.
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:
Right-click the first cross and select Show ShapeSheet.
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.
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:
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).
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.
)
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...