Documentation: External

Oops. There was a small mistake in last month's article. Near the beginning I had a few field names. Underneath I explained that one of them was a related field; but I got the name of the relationship wrong. It said:
*(Self_Service being the name of a self-relationship of the Job ID)
That sounds more like a car wash. It should be, and hopefully you figured this out:
(Self_Job ID being the name of a self-relationship of the Job ID)

Last month I wrote about how to "comment" your work within FileMaker Pro. Now I'm going to continue with a discussion of various ways to document your work outside the database files themselves.

There a few reasons why you would want to do this:
If you were developing for someone else, they'd certainly want some external map of what you'd done. In an extreme case, it might be the only way to rebuild badly damaged and unrecoverable files.

For me, the main benefit of external documentation is that it is "searchable." In the case where I just couldn't find what a certain field did, or whether it was obsolete, I could read and compare printouts or search for it.
You can print the Field Definitions (all of them) and the Scripts (all or individually) directly from the Print dialog box. You can't, however, print the Relationships. If I went to the trouble of creating a spreadsheet of all these items, I could then search for a particular name to see all instances where it occurred. More on this later.

I must admit that I rarely do this. For one thing it's time-consuming to create the documentation. I usually just bip madly between the layout, Field Definitions, Define Relationships, and the Script I might be writing until I somehow figure it out.

Another problem is the old adage that you can't write the documentation until the development is finished; because as soon as you change anything the text is no longer accurate.

Despite all that there are some times when having a comprehensive external record is the only solution to clearing up a complicated mess, or for finding the needle in the haystack.
There are programs out there which are designed to help, most even create a separate database detailing the structure of your files. The easy-to-use ones cost money, but there are free methods available.

Commercial Programs
(I haven't used most of these programs, but I've heard good things about them. Check out the websites for extensive screenshots and demos.)

The first program I'll mention is Analyser by WavesInMotion http://www.wmotion.com It has a very clear tabbed interface, with all the various parts listed at the top of the main window: Files, Fields, Relationships, Scripts, Passwords, etc.. You can get a great deal of info about items, especially Fields; its definition, other fields that refer to it, relationships that refer to it, scripts that have it in them, etc. It appears to get everything, even the steps of each script. The cost of the program is $159; it requires version 4.

They also have a smaller program, DocuScript, that just works on scripts, allowing you to perform searches for a word. It's $59.

ClickStats by ClickWorks is similar http://www.clickware.com. It has a "versioning" feature that allows you to compare one session to another. You can run several "sessions" on the same file(s). Then you can choose any two and ask for a comparison to see a general count of what's different. It appears to lack some detail on this, however. It doesn't get script steps, for example, just the names. It is similarly priced at $149, and also requires v.4.

SmartTools, at http://www.cetacean.com is a little different. It is not so much a file analysis tool as it is a souped-up template library. It can save your commonly used layouts, fields, relationships and scripts, ready to use again at the touch of a button.
You can choose from the list of fields, then click a button to have them all created in your new file, complete with definitions.
It also has a "redirect" relationships tool that fixes broken relationships, due to file name changes, etc.

It costs $99. It doesn't require v.4, but it does require a clean version which has not been altered with Developer Dialogs to have larger windows (which mine have). It includes a OneClick runtime engine extension, unneeded for OneClick users. The odd thing about it however is that only a beta version is available. The final version is promised in September--of 1998! I imagine it works fine, since FileMaker's file format hasn't changed since v.3, but I'd make an inquiry before buying.

Free (or dirt cheap) programs I have used

The first is a free AppleScript, FileMaker DB Lister v2.0, by Tony Williams, http://www.moreinfo.com.au/tonyw/fmp
It goes through the front database file and gets its Layout names, Fields & Definitions and Script names.

It's main use, for my purposes, is to get the list of which fields (including ones you may have made invisible) are on each layout; there's no easy way do this within FileMaker (other than one at a time in the Overview submenu, File Menu).
It outputs a text file in tabbed format, which can be open/pasted into a spreadsheet. It's a good start.

Next is Print2Pict by B. Raoult (that's all he has in the ReadMe; hard to find). ftp://ftp.amug.org//pub/amug/amug-files/publish/o-p/print2pict-3.7.1.sit.hqx This is an amazing tool, and many of the other programs listed here use it. It's actually a Chooser Extension. It shows up in the Page Setup dialog box just like a printer driver. It "prints" to a file from any application, and allows you to choose the format of the file: PICT, GIF, TIFF, or, in this case, text.

I use it to "print" the Field Definitions directly from FileMaker to a text file. FM does a great job on these, including all the info, especially for lookups. Unfortunately, the output is just text. The parts are separated by spaces, not tabs; or even worse, by returns. There's no way to paste it into a spreadsheet as is, so some time must be spent in a good text editor (BBEdit Lite is an example) to clean it up into something more useful.
Then paste them into the earlier spreadsheet, over the ones the AppleScript did; these are better.
You can also print the Script definitions in the same fashion, at least the tricky ones; then clean them up and paste into the SS. (The script name is printed after the script steps, for some inane reason).
The last item to get is the Relationship definitions. Though it's small, it's the hardest to get, as it can't be printed. You need another tool.

Text Capture FKey by James Walker (author of the original Text Editor Patches, the very useful OtherMenu and others) http://www.jwwalker.com
This tool is also amazing. It allows you to do something that you intuitively know you should be able to do, but otherwise can't.
With a key combination, much like the screen capture FKey, you can copy practically any text and put it on the clipboard--as text. This includes non-editable text that is inside dialog boxes, which is where most of FileMaker's scripting happens.
You can set it to capture only "selected" text, using a cross-hair to make a rectangular selection, or the entire window (which gets text off the right of the screen; neither gets text below the screen, however; you have to do it again, after pasting).

You can either put the FKey in the system (not so easy), use something like ATM or Master Juggler to load it, or use the free application FKeyDragger, which makes adding FKeys as simple as drag-and-drop. http://hyperarchive.lcs.mit.edu/HyperArchive/Archive/cfg/fkey-dragger-10.hqx
(It comes with some cool ones, including Desktop Notes, which lets you type or copy/paste a quick note from within any application and then save it as a text clipping.)
Caveat: I don't know how all of this works with the latest OS versions. I've also read that the next Mac OS will include this FKey functionality. It's about time.

What we need Text Capture FKey for in this case is to select and copy the Define Relationships dialog. You may not be able to get them all at one go if you have a lot. Paste the ones you get into the spreadsheet, then go back and get the rest. You'll still have to add tabs, one after the name of the relationship, and one after the name of the fields. This allows you to adjust the columns and see it all aligned properly.

Search the Spreadsheet
The whole point of all this is that now you have everything in the spreadsheet. You can use the Find function to search for a particular field name. Then search again, until you see every place that it occurs or is referenced.
If you have commented your scripts thoroughly (read last month's article), then you can also search for external script names from other files.

Troubleshooting a Problem
If this all seems like too much trouble (and it is) and you don't want to spend the $150 for an analysis program, there's a lesser approach which I usually use: You can use Text Capture Fkey to just copy the relevant info into a text editor, then analyze or refer to that.

It's often confusing when there are too many fields to look at, so just copy the ones involved straight out of the Define Fields dialog.
Rearrange them however you want to compare them.

You can copy a short script the same way, or you can use Print2Pict to print it as a text file.
If there is an external script involved, you can paste it directly after the local script that calls it, to really see where things are going. If you've commented it, the name will be there; otherwise label it or you'll lose the jump between the scripts. Print out the script now and read it over, like a story.

I've sometimes gone so far as to create a special layout and paste some of the field definitions, relationships, and scripts on it, just to have them handy when I come back with the same confusion later (duh).

Copying Scripts
Once you've got a script as a text file, it's a lot easier to recreate in another file. I just keep the text file open, then bip back and forth between that and the ScriptMaker dialog box (as you can't have two ScriptMaker dialogs open at the same time).

There are also small commercial programs for copying scripts.
Both use Print2Pict to get the scripts out, then recreate them as best as possible. There are some limitations. Both are inexpensive.

One is ProMaker Utils, by Peter Baanen of Troi Automatisering (also a prolific plug-ins author), http://www.troi.com
Another is AutoScript.FM, by Gregory Charles Rivers,
http://www.gnb.com.hk/autoscript.fm/index.html

It also uses a OneClick runtime engine. He's just come out with an updated version with "interactive" field naming and drag-and-drop script step replication for full or partial scripts.
Now that would be useful. I've put off using these tools primarily because without the ability to re-target script steps to new fields or extract only parts of a script, they wouldn't be much faster than recreating the script from my text file copy.

If you're upgrading soon, FileMaker Pro version 5 will include the ability to import scripts from another file. It also will have a few of the layout template library features; but not many. I only know this from rumors (screenshots at AppleInsider http://www.appleinsider.com). By the time this article is out, there may be a public announcement.

Entity Relationship Diagrams
Say what? Known to their few friends as E-R diagrams, these can be fairly simple drawings, with boxes for files (tables in other programs), and lines joining them (relationships). They afford a visual overview of your entire database.

If you are serious about databases, I highly recommend the book Database Design for Mere Mortals by Michael J. Hernandez. It's not about FileMaker. But he writes about relational database design in a way that transcends all applications, in plain everyday language. He also has some instruction on drawing these diagrams. Altogether a great book, especially for those with little knowledge of relational design (my hand's up).

It's all in your head
Let me just say in conclusion that even though some of these tools are useful and occasionally essential to making databases, it is your mind that creates the fields where the data resides, the connections between them, and the layouts to display the results, transforming raw data into useful information.
You have to think about them intensively while you are working, and often in the background when you're not. The solutions to problems will often come to you when you least expect it: while taking a shower, taking a walk, in the middle of the night (not so good). But they will come.

It is also essential to learn from others. Examine example files others have made available on the Web. Read a FileMaker mailing list:

Dartmouth List: Very friendly, a good place to start; somewhat archaic List Server Put this in the Subject line (I think?) of an email:
SUBscribe FMPRO-L
email to: LISTSERV@LISTSERV.DARTMOUTH.EDU)

Blueworld FileMaker Pro List: Developers, all levels. Just click this when online: fmpro-on@blueworld.com >
To search the list archives (awesome):
http://listsearch.blueworld.com/fmprotalksearch.lasso

To search TechInfo for articles go to the FMI website
http://www.filemaker.com
select Support button
Select Search TechInfo from the list on the left side of the screen

Websites with example and utility files:
jim40er FM http://www.halcyon.com/jim40er Mailbrowser, Eudora2Text, Archive files
Database-systems http://www.database-systems.com Example files
FileBits Pro http://www.elmapp.com/fmp/fmbits.html
DW Data Concepts http://members.home.net:80/dwieland/dwdctips.htm
FileMaker Power! http://www.Filemaker-power.com/info2.html
FILEMAKER PRO ADVISOR Magazine http://www.advisor.com/wHome.nsf/wPages/CFmain >
FileMaker Pro Examples/Tutorial http://www4.ncsu.edu/unity/users/j/jpscott/www/bin/FMPSlides/index.html
FileMaker Pros: John Mark http://www.best.com/~jmo Lots of examples, Scriptology
FileVille http://www.fileville.com
HABMarketing http://www.habmarketing.com/HOME.HTM
ISO FileMaker Magazine http://www.iso-ezine.com/magazine.phtml
Jeff Hopkins http://www.amug.org/~hposoft (Tutorial files)
Steve Wilmes Consulting, Inc. http://www.swconsulting.com

Fenton Jones
manavesh@mymac.com