The Database Design Report

Sometimes you inherit a large database from somebody else, and you simply don't see how it comes together. (OK, be honest. Sometimes you create a large database and still don't understand how it's put together.) While FileMaker's point-and-click interface makes it easy to build databases, teasing things out later is a different story. You can look at a script, field, layout, table occurrence, or even an entire table in FileMaker Pro, and have no idea whether the database actually uses or needs it.

GEM IN THE ROUGH
View Through a Window

Like most people, you may not realize you're free to move or resize your database windows, and even switch layouts, while you're debugging a script. Don't be shy; it's perfectly safe. Just bear in mind that changing layouts mid-script changes context, so it can also change the way your script works. Be careful to switch back to the original layout before you step further through the script.

Switching layouts is an invaluable technique when there are processes you want to check as the script is running ("Did I get the right found set?" "Is my script going to the right layout?" "Is the loop running through the found set properly?"). As powerful a tool as Data Viewer is, it can't show you two fields, or two layouts, side-by-side.

So, drop any fields you want on this developer-only layout, or use Table view and the fields you're working with (or any other setup that works for you). Then make a new window and switch it to this layout before you start debugging. Often, you can tuck this window off to the side and glance at it, or switch layouts as needed.

Finally, even if you prefer to use Data Viewer instead of a custom layout, it can still be helpful to open a new window before you start debugging. Although you can switch layouts while Script Debugger's running, you can't create a new one once it's started. Having that spare window open can keep you from having to stop the script, open a new window and start over again.

If you've shelled out for FileMaker Advanced, however, you've got help. Its built-in internal analysis tool, the Database Design Report (affectionately called DDR), gives you an overview of your database, where you can easily see how database items are connected and other details all in one place. You run the report, tell it what kinds of things you're interested in, and FileMaker presents the information in a series of Web pages.

Unlike the reports discussed in Chapter 6, the DDR is a report about the structure of your database, not about the data inside. It tells you what tables and fields you have, which fields are used on each layout, and so on, but nothing about the information in your records and fields.

19.3.1. Generating the DDR

The Database Design Report window lets you tell FileMaker exactly what you want it to report on. You get to pick which files and table occurrences to include, what kinds of things you want to report on, and what format you want the report to use. You also get to decide whether you want to open the report right away, or just save it for later use. To get started, choose Tools images/U2192.jpg border=0> Database Design Report. Up pops the Database Design report dialog box (Figure 19-6).

Figure 19-6. Turn on "Automatically open report when done" if you want to see your Database Design Report right away. If you're running this report because you want to look at it right now, turn this option on. Sometimes, though, you just run the report to have around in case you need it later. If that's the case, turn this option off to save a few clicks.

The Available Files list in this window shows every open file. To include a file in the report, turn on the checkbox by its name. FileMaker assumes you want every file at first, so you may have to do more turning off than on.

In the DDR window, you tell FileMaker which files to examine by turning on their checkboxes. Select one in the list to see all its fields in the "Include fields from tables in selected file" list. Again, you can use the checkboxes in this list to tell FileMaker which tables you want included in the report.

In the "Include in Report" section, there's a checkbox for each kind of database element the DDR can report on. Again, FileMaker assumes you want everything, but you're free to limit the report to just certain information. The less you report on, the faster the report runs, and the smaller the final files.

Normally, FileMaker saves the report in HTML format so you can read and navigate it in any Web browser, but it also offers a more structured XML format. XML files aren't easy for humans to read, but with the help of other software, you can process the XML and integrate information about your database into other systems. Furthermore, several companies make DDR analysis tools that process the XML version of your DDR and provide extra tools for browsing, finding, and reporting on the information it contains. (See the box on Section 19.3.3 for more information on these third-party tools.)

When you're done making decisions, click Create. FileMaker asks you where to save the report. The DDR is made up of several files, so you probably want to make a new folder to hold the report. The more complex your files, the longer it takes to create the DDR. In a file with dozens of tables, each of which may have dozens or even hundreds of fields, this could take a minute or more. FileMaker displays a progress bar for you, so you can gauge how long the process will take.

---

Note: A DDR is a snapshot of the database at the moment you create it. So it's good to make periodic DDRs as your database evolves. You can create a record of when you added or changed various parts of the database. A DDR can also help with troubleshooting broken elements (Section 19.3.3).

---

19.3.2. Using the DDR

If you checked "Automatically open report when done," when you created the DDR, FileMaker launches your browser and shows you the DDR Report Overview (Figure 19-7) as soon as the progress bar disappears.

Figure 19-7. Report Overview is the first thing you see when you open a DDR is. It's a table, with one row for every file you included in the report, and a column for each option you checked when you created the DDR. Each "cell" contains a link leading to more information. Each DDR lists the time/ and date of its creation, so you can tell if the report matches the current state of your database.

This file is the main report file, and it has URL links that bring up the detail pages. To view the DDR later, go to where you saved it and open the file called Summary, or Summary.html. (You also see a folder named for each file you selected when you created the DDR.)

On the overview page, the DDR tells you which elements you chose when you created the DDR. If you click a file name, you see the file detail page (Figure 19-8), with lots of information about that file. The links in each column go to the same file detail page, but each link scrolls you to the relevant section. On large databases, with lots of fields, this option can save you a lot of time scrolling through the page, looking for what you want. For instance, if you click the number in the Relationships column, you see the Relationships section of the file detail page.

Figure 19-8. When you click the File Name link on the Overview page, you see this page. The top link on the left leads back to the Overview page. All the others just scroll the page to various important parts. The report itself is also loaded with links. You can click any link to go to more details about that item.

Use the DDR to help you figure out what parts of your database can safely be edited or deleted. Since it's so easy to create tables, fields, and layouts in FileMaker, you may well end up with extras that you don't need when your database reaches completion. You can make your database easier to understand and more efficient by deleting these extra elements. But even if a database is the last word in efficiency, running a DDR is one of the best ways to trace the designer's thinking process.

To see ifand howa particular element is used, look at its detail. Suppose you have a bunch of fields you'd like to delete from your database, and you want to find out whether it's safe to do so. First, click the Tables linkfields are part of tables. You see a list of tables, with information about how many fields each table contains, along with a list of occurrences of each table in the Relationship Graph (Figure 19-8). Click the link for a table's fields, and you see a list of all the fields defined for that table.

POWER USERS' CLINIC
Getting the Most from the DDR

At first glance, you may not appreciate the true value of the DDR. It appears to tell you the same things you can find out in other FileMaker windows, like Define Database, ScriptMaker, and so on. But once you run your first DDR, you understand it has powers those flimsy boxes never dreamed of.

For example, the DDR information for a script helps you determine how the script functions in and interacts with the rest of the database. In a neat chart, you can see every field, layout, table, table occurrence, and custom function the script uses. More importantly, you can see every script or layout button that runs this script. That kind of information would be very hard to nail down without the DDR. You'd basically have to go to each layout in your database, click anything that might be a button, and see if the script you're interested in is attached to it.

If you've created Custom Menus , you'd have to check them individually, too. The DDR gathers up all that information for you; not just for scripts but for tables, layouts, value lists, and other kinds of database elements as well.

If you want even more power, consider acquiring a DDR analysis tool. One such toolAnalyzer from Waves in Motioncan let you search your DDR, quickly hotlink from element to element (with forward, back, and history), and print trace reports that show everything a script does, including the things its subscripts do.

It's also worth mentioning that some tools can analyze your database without using the DDR. They tend to be faster than DDR analysis tools, but somewhat less comprehensive. Two excellent choices are MetaData Magic (for Version 7 and 8 files) by New Millennium Communications (www.nmci.com) and Brushfire (for v7 only) by Chaparral Software (www.chapsoft.com).

Details appear in the Field Name, Type, and Options columns for every field. Comments, if there are any, show up in the Comments column. Any layouts or scripts that use the field are listed in the Layout and Script columns, respectively. You see the information in the Relationship column only if it's a key field. Fields used in layouts, relationships, and scripts are called dependencies of those elements.

---

Note: Even if you don't use a Go to Field script step for a specific field, a key field may be listed in the scripts column. Go to Related Record, or any other step that uses a relationship, also requires the use of that relationship's key field, so it also has that field as a dependency.

---

A DDR is also very useful if you're upgrading a file from a previous version of FileMaker. You can figure out which elements of the old database are crucial and which ones you don't have to bother recreating when you build your new database.

19.3.3. Finding Broken Elements with the DDR

Suppose you've deleted a field, unaware that it's a dependency for a script, as described in the previous section. Later, you run that script and notice an error, but you're not sure what you did wrong. The DDR is the best way to track down the errorwhich is the first step in fixing it. Take, for example, the "-find unbilled activity" script you wrote back in Chapter 15 (Section 15.5.5). It searched in the Expenses::Job ID field for all unbilled expenses…but you've deleted the Job ID field.

If you examine that script in ScriptMaker, you can see the words "" right in the script.

	Set Field [Expenses::; "==" & Get (ScriptParameter)]

You might be able to find the error, but then again you might not, because the "" error isn't bold, or red, or highlighted in any way. And as great as Script Debugger is, it doesn't lead you to the broken script step either. But a DDR can help you find all the trouble spots that arise from the deleted Expenses:: Job ID field.

To do this kind of sleuthing, search the DDR page for a character string, using your browser's Find tool (pressing Ctrl+F or -F does the trick in most programs). Type the text you're looking for in the Find field, then click Next (or whatever button your browser uses) to start the search. You see the first instance of your search criteria highlighted. Click the button again to find other instances.

The whole list of errors you might search for appears below. If you have any inkling what kind of error you're looking for, start with that one:

• <Missing Field>. Referenced field is missing.
• <Missing Table Occurrence>. Referenced table occurrence set is missing.
• <Missing Base Table>. Referenced base table is missing.
• <Missing File Reference>. Referenced file reference is missing.
• <Missing Layout>. Referenced layout is missing.
• <Missing Valuelist>. Referenced value list is missing.
• <Missing Custom Function>. Referenced custom function is missing.
• <Missing Script>. Referenced script is missing.
• <Missing Account>. Referenced account is missing.
• <Missing Privilege Set>. Referenced privilege set is missing.
• <Missing Extended Privilege>. Referenced extended privilege is missing.
• <Missing Custom Menu>. Referenced custom menu is missing.
• <Missing Custom Menu Set>. Referenced custom menu set is missing.

Once you find a broken element, return to your database and fix it manually. The DDR won't update itself to show your fix until you run another one. And since you can't mark the electronic version of your DDR, a good way to keep track of your work is to print it out, then mark off each item as you fix it. Then, when all the broken elements are fixed (or you've deleted all the unused stuff), run another DDR. This time it should be clean, but if it's not, you've got the tools to fix it.