At least once a year, I like to run a Database Design Report on each database I am working on and look for Scripts, Layouts, and Fields that aren’t being used. This step by step guide was presented at the Philly FileMaker Users Group meeting in April 2013.
Reasons for doing spring cleaning:
- Smaller relationship graph loads faster when opening a Database.
- Removal of clutter makes it easier to find specific relationships.
- The Manage Database window will open and close faster.
- Helps to identify things that can be done better using new techniques.
How to create a DDR.
For this post, I will be using a modified version of the Invoices Starter Solution file that comes installed with FileMaker – it’s available the “New From Starter Solution” menu command.
For this you need to have FileMaker Advanced and you must open your solution using a Full Access password. If your solution has a multiple files, make sure they are all open. Then From the Tools menu select ‘Developer Design Report…’
The DDR creates a number of folders and files, therefore whenever I create a DDR, I always save it to a new folder and name the folder something with the date, for example ‘Analysis 061213′. Sit back and relax – it may take a couple minutes to create the DDR.
Basic parts of the DDR.
Luckily the basic structure of the report hasn’t changed much in the last couple of years. When the DDR is complete it opens to show you a summary of all the Files in your solution. If you have a multi file solution this will help you get a nice overview of all the files. Click on a FileName to to see the detail of that file. The DDR is divided into two columns. The one on the left is the navigation section. All the detail is in the right column. It can get wide.
Work flow for removing unused objects.
It doesn’t matter what you are working on, Table Occurrences, Layouts, Fields, Scripts you always want to follow the same general work flow:
- Make a Preliminary List of Unused TableOccurences/Layouts/Fields etc
- Search DDR for hidden references to object
- Notify Stakeholders
- Remove objects
When you open the DDR in a browser use the Find command to find hidden references to the object. It is extremely important to keep any other stakeholders in the loop and informed as to what you are doing. If other people are accessing your database via PHP, or OBDC they may be referencing layouts or fields that you are not aware of. You could inadvertently remove a critical field that someone else is using that you aren’t aware of.
How to identify redundant or unused relationships.
Over time as a solution becomes more complex, it is inevitable that a relationship will be created that will eventually be no longer used. Either because a scripting technique has come along and made a relationship unnecessary, or maybe the business logic has changed and rendered a feature un needed. The first thing to check for is the Relationship section of the DDR. Click on the ‘Table Occurrences’ link. This section lists out all the table occurrences and if they are used in any scripts, field definitions, value lists or associated with any tables. Scan for relationships that aren’t used in any Scripts.
Exercise caution – you can’t use this criteria alone to determine whether a relationship is in use.
As an example, I added a new relationship to our file, Products_Teaser.
It appears that our new relationship is not used at all. However, if you perform a find on the web page for ‘Products_Teaser’ you will find that this relationship is used on a button I added on the Invoice Detail layout. The GO button on this layout performs the Go To Related script step, using the Products_Teaser relationship. Deleting this relationship would break this button.
My programming style would be to use a script on all buttons, but I know many developers who would use a GTRR script step on a navigation button like this. The point is whenever you are using the DDR, it is a good idea to also to a manual find to make sure an object is not being used. Often I will find a ‘Goto Layout’ script step used on buttons, layouts called this way will also not be reported directly on the DDR, so you should do manual finds for layout names too.
Check for script triggers.
A script used in a script trigger isn’t represented in the DDR in the Script Detail section. A great example of this in the example file is the ‘Trigger | QuickFind Customers” script. A cursory look at the script detail section shows that this script is not called on any layouts and not used as a subscript in any other scripts. Not true! do a find for this script and you will see it is used as an OnObjSave trigger on four of the customer layouts. The DDR only shows what Scripts called on layouts as Buttons. This script appears to be unused.
If you do a find for the script name. You will discover it shows up on the object detail portion of the DDR. The author of the Starter Solution did name the script with a hint that it is used as a trigger. The developer convention at ITS is to add the trigger type at the end of the Script name. In this case it would be “QuickFindCustomers_onObjSave”.
More Layout Navigation.
One interesting gotcha with regard to layout navigation is the ability to navigate to a layout either by explicitly by layout name or by a calculation. This could be used where you want to embed some logic into the goto layout script step instead of breaking it out in code.
A developer might be tempted to take these 7 lines of code a combine them all down to a single Go to Layout script step, and then go to layout Name by calculation using this calculation.
There are a couple of issues with this type of scripting. Most problematic, it is dependent on layout names or position. If you (or another developer) changes the name or position of a layout, the script will break.go to layout by name/number. Another problem, is that this type of navigation is not reported at all in the DDR. If the developer navigates by layout name, then at least you can use the find technique previously discussed to find these layouts. If the layout is based on layout number, it is essentially untraceable.
How to determine if your solution is using ‘Goto Layout by Number’. The best way I have found to track down this gremlin, is to save the DDR as a XML document. In the XML document goto layout script step give more info regarding the method of navigation. However it is a bit harder to read without a third party program. In this example, save the DDR as an XML document, then open the ‘Invoices_DDR_fmp12.xml’ document using a text editor. You can do a find for either of these two strings;
If you find this string, that means you are using this method. My recommendation would be to replace any instance of these techniques with explicit references to layouts. Once you find this string, scroll up and eventually you will find a reference to the script it is used in;
<Script includeInMenu="False" runFullAccess="False" id="245" name="GotoCustomerList_byNumber">
Go back into your solution and re write the script. This will be a pain, especially to figure out the actual layout names from the layout number. However you will benifit in the long run by removing the obfication from your database.
Also be aware of users who select Layouts from the layouts menu and not via script. And users who select scripts from the scripts menu. I have also run into situations where power users manually navigate to layouts by going into layout mode, or select scripts from Manage Scripts… Always best to notify users/power users before deleting things. And remember to keep backups!
Like cleaning your room, keeping a database tidy is an iterative process. I find that running and analyzing the Developer Design Report is a great way to keep your databases tight. It will also afford an easy way to identify places to update your system to use current FileMaker tools. The file used in this article can be downloaded here.
In the couple weeks since I gave this talk I have decided to give InspectorPro another try. I have tried a number of these helper programs and have never really found that they were worth the trouble. However after using Inspector for the past couple of weeks, I can say I was wrong and don’t mind saying it. The interface for Inspector is very easy to navigate, and most importantly it gets me to my information in a way that is easier than using the raw DDR.
Case in point. To determine if a layout is being used is much easier with Inspector. With the raw DDR you need to first look at each layout. then you need to do an extra find to double check that the layout isn’t called in a GoTo Layout script step button. Using inspector, I can go to the layouts section. Do a find for all layouts that don’t have any references. Inspector returns a list of layouts that are not used in any scripts, and also are not being called via any buttons described above. It is much easier to create a found set of unused layouts than it is to wade threw every layout in the DDR and check to see if it is being used. In the coming months I will present a more in depth article on Inspector Pro stay tuned!