Every custom app we build is a complex DNA strand of tables, fields, layout object and scripts, security privilege sets, conditional formatting, data-formatting options, relationships, calculations, indirection sources, and other attributes. The web these pieces creates starts simple in the first stages of development and we may even be able to keep it all in our heads. But as the custom app grows in functionality, data-storage, user-interfaces, it is easy to lose track of some part of it. I might have created a field called “zz_AccountName_g” and used it throughout, but months later, I cannot remember where I used it or even if I ever used it in a script or on a layout or in an privilege set. The FileMaker Database Design Report helps us to find that which we forgot.
FileMaker Database Design Report (DDR)
The FileMaker Database Design Report is meant to expose the DNA of a custom app, clearly identifying the locations and uses of fields, calculations, layouts, themes & styles, scripts, and other pieces. After running a DDR and processing it in some way, I can find the exact places the field “zz_AccountName_g” are used (if any). This exposure is essential for a developer’s sanity, efficiency in tracking down issues, and lean & well-organized custom apps.
The DDR gives us the ability to find any part of our custom app. People use the DDR to do any of the following:
- Find out where the field “zz_AccountName_g” is used throughout the system: what layouts it is on, what scripts use it, and any calculations (field definitions, conditional formatting, hide-conditions, etc.) that rely on the value.
- Discover unreferenced fields or scripts/layouts that aren’t called from anywhere.
- Find errors. This includes commented-out script steps or calculations, etc.
- Read a script outside of the script workspace
- Find indirection sources
- Review the themes used and local styles.
- Check the accounts that use the “EditOnly” privilege set.
This list is very incomplete. So remember this: you can find any part of your custom app using the DDR.
Start at the beginning.
FileMaker Pro Advanced users have access to an additional menu: Tools
Here is where the Database Design Report is found.
The dialog that comes up is fairly simple.
In this dialog box you have:
- Available Files: These are all the files you have currently open. Those files checked will combine into one DDR, so make sure there’s no extraneous ones lurking about. With a multi-file solution, however, you’ll want all those included in the DDR. Just make sure each file is open before running the DDR.
- Include Fields from Tables: The top-right box allows us to deselect some tables we don’t want included in the DDR. This might come in handy, but I’ve rarely done this. Of course it depends on the size of the file; if your goal is to see only where one field in one table is used, you can only select that table.
- Include in Report: The middle box lists what to include in the report. Again, it seems worth it to leave all these checked except for rare circumstances.
- Report Format: This choice describes the kind of output you want. There are two: HTML and XML. Both are valid and useful, but the XML is MUCH more useful than the HTML format.
- File Handling: I rarely immediately open the file, especially if I’m using the XML format.
When I click on Create, FileMaker produces a Summary file and a file for each FileMaker file included in the report. Any use of these files (opening, importing) starts at the Summary file.
The HTML format
The HTML format looks like this.
It is an HTML page that includes a long barely-formatted table of all the components of the DDR. Each column contains a helpful link to take you to its details. When I click on “31” under the Tables header, I am taken to this:
Now I can navigate anywhere in the report, to fields, to scripts that use a certain field, to all the relationships in which a certain table is used. Anything.
Run an HTML DDR on one of your files and click around in there. Go ahead. I’ll wait.
The HTML report can be useful, but navigating through the HTML document can get unwieldy. I personally never use this report. Instead, I create my DDRs in the XML format.
We FileMaker developers are interested in completing a task in the fewest clicks, and that includes finding a field in the complex DDR. Database analysis tools such as Base Elements and Inspector, and Realtime Developer Intelligence tools such as FMPerception allow us to find a field or calculation or conditional formatting logic extremely quickly. And these tools use the XML DDR. So this is the format I recommend (and most all developers use).
After creating the DDR in this format, you can use the tools mentioned above and either import the Summary.xml (Base Elements & Inspector) or just open the Summary.xml in FMPerception. Here’s what a DDR looks like in FMPerception.
FMPerception and the other tools mentioned provide much easier access to every last byte of your custom app. They provide searches, drill-downs, reporting functions, and other organization tools.
Organization & Frequency
FileMaker developers lean heavily on the DDR and the tools that help them read the DDRs. These folks run DDRs on a regular basis
Before Development begins:
If a developer takes over the development, she will run a DDR and bring into a tool before any work begins. She’ll review the DDR to see its current state, looking for errors, unused elements, and overall structure. The reporting tools help her see the basic current state.
I’ve known developers to run a DDR on a regular basis: every hour, every week, every day, at the start of their work day, or before a work sprint. This DDR reflects the current state and shows the changes made recently. Tech leads use the daily DDR to keep an eye on errors or large structure changes. Developers use it to look up where a certain field is used.
After the custom app has gone through a development cycle and then has been released, a DDR is run to capture its final state. Again, post-work analysis can be done with the DDR. This DDR becomes documentation on the current release. This has an advantage too of helping us to identify and changes made by the client after they started working with the file.
Organization & Frequency
Running DDRs and importing them into the analysis or intelligence tools will cause you think about organization of the files since each DDR overwrites the previous one if you let it. So think about how you will organize these files.
I typically set up a “DDR” folder in my overall project folder. Inside this DDR folder is a folder for each day the DDR is run.
This method keeps all the DDRs organized proper. You can, at anytime, find a previous DDR, open it up again and compare it to the current one.
Rely on the FileMaker Database Design Report
The FileMaker Database Design Report is an essential tool for all FileMaker Developers, and all FileMaker developers can use this tool to better understand their custom apps. As you start using it, you’ll find that you can’t live without it.
Also published on Medium.