|
|
|
|
Field Forestry
|
Level: Beginner
Version: FileMaker 15
Category: General
Wednesday, February 22, 2017 |
|
|
|
|
|
|
|
|
|
|
|
|
It’s easier to type in all your fields at one sitting and never enter Manage Database again. However, most people can’t plan a solution that extensively and frankly, it really isn’t necessary. FileMaker is very forgiving and will let you add, change or delete fields and automatically update all references or even warn you if the field is used somewhere like in a relationship. With that said, it is still ideal to enter the bulk of your fields in one sitting, especially if your requirements document specifies them. Just don’t worry if you forget some data entry fields or need to add a calculation or summary field. Many developers plan to the nth degree but I’m here to tell you fields are easy to add any time and are very unlikely to disrupt your relational design.
Housekeeping Fields
One of the most important field types to include in all your tables are housekeeping fields. These fields will track your record history. They won’t keep a list of the changes, only the last occurrence but they are extremely easy to add and very useful at times. You don’t necessarily place them on a layout but when strange events start occurring, being able to trace when and by whom the last change was made can really help with your detective work.
Notice the account name is auto-entered and not the name. Auto-entering the name places the user name from the computer or FileMaker Preferences if overridden. Tracking the computer that made the change is not very helpful, although you could include it your housekeeping fields. The account name from the user’s logon credentials will track the user no matter what computer they are using and is far more useful in tracking down issues.
Some people wonder if the auto-enter data can update when the option for not allowing modification is checked. This option does not prevent the system from making changes, just users. It ensures the data stored inside these tracking fields is valid. Along with proper security, not allowing modification will guarantee the data you are looking at hasn’t been meddled.
I also prefer timestamps over date fields simply because they store more information. While a date is most often all you need, the time can often reveal exactly when a change was made and help identify the issue more precisely. A date can still be extracted using a simple calculation using the GetAsDate function if you just want to display a date value somewhere in the interface.
Once you have entered one set of housekeeping fields, use FileMaker Pro Advanced to copy/paste the fields to all tables. This will save time and prevent mistakes. In fact, I usually copy and paste the housekeeping from an existing solution. If you are really enterprising, you will have a template on which to base all your new solutions, of which, housekeeping fields will be one component.
I don’t often recommend third party plug-ins but this product solves a problem much better than FileMaker. With little modification to your database, FM Data Guard FMDataGuard will store all adds, edits and deletes for fields in a FileMaker table and not just the most recent like the comparable built-in FileMaker feature discussed above. It even allows for roll-backs. Think of roll-backs as an undo for deletes, even record deletions!
Key Fields
The next most important fields to create are primary and foreign keys. Every table should have a primary field with an auto-enter serial number regardless of whether it needs to relate to any child tables. Additional tables may be added or a script may need to gather unique values for a particular feature, making that primary key a wise choice. You never know how you will use a serial number to uniquely identify a record so add it early on to avoid requiring a replace to populate existing records.
Always make key field text values so they can be used with multi-key relationships, which will be covered later. For now, multi-key fields are return-separated lists of key vales that are very useful in creating OR relationships. The downside of using a text field is the serial number field does not sort properly. For example, numbers stored in a text field would sort in the following order:
To override this behavior, simply proceed your serial number with leading zeros:
While we are changing the auto-enter options for the primary key field, add three letters at the beginning to help identify the serial number out of context. Otherwise, serial numbers from other tables will look exactly the same. FileMaker will only increment the number at the right side of the letters and leading zeros. In addition, the number of characters will never increase unless you somehow add enough records to go beyond the number of zeros allotted in your serial number definition.
It is also important to safeguard the uniqueness of your serial numbers by prohibiting modification as well as validating for not empty and unique value. I recommend checking the option to always validate so uniqueness is guaranteed even on import. And of course, do not allow override during data entry. Along with proper security measures, the validity of your serial numbers are guaranteed with these settings.
Foreign keys do not need to be added to every table. Identifying which tables require foreign key fields is as easy as glancing at your ERD (Entity-Relationship Diagram). Every table connected by a crow’s foot will need a foreign key. For example, the classic invoicing solution will have an Invoices, Customers, Products and Lines tables. The Invoice table will need a foreign key to store the customer primary key and the Lines table will join Invoices and Products with two foreign keys. The Products table will not need a foreign key.
Data Entry Fields
FileMaker is very forgiving when compared to other database systems. Fields can be added at anytime but it's still best to get the bulk of the data entry fields entered at the beginning of a project. However, no data entry fields fields for the Line Items table will be created at this time. I prefer to save them till later so we can discuss their design in the context of the features being created.
A common mistake is creating a field with the wrong type, which can lead to all kinds of trouble. For example, if a date field is actually created as a text field, you can still enter dates without knowing. Data entry appears to be the same but sorting, finding and formatting will not behave correctly causing countless hours of troubleshooting only to discover a simple field type mismatch. For example, down the line you might be writing a script that performs a date range find. Since you can also perform a range find on a text field, you might be perplexed at the results. While the most common solution is the simplest, our brains tend to attribute complex issues as the cause like a corrupt index or an incorrectly written script. It could take hours to find out the issue is simply a field type mismatch. The moral of the story is be careful when defining field types.
It’s also a good idea to get in the habit of commenting fields as you create them. It takes a little extra time but is well worth the effort in some cases. Naming conventions for most data entry fields are good enough to identify their purpose. However, fields specially designed for scripts and other non-data entry fields will require comments to remind you of their purpose at a later date. Otherwise, comments are just a waste of your time if they don’t don’t reveal additional information. None of the fields created so far really need comments but keep an eye out for fields that do.
Indexing
The upside of indexing is speedy finds and other features. See previous blog entry In Depth Index for details. The downside of indexing is time and size. It takes time to create an index and space to store it. One index isn’t going to be noticeable but if you index every field, you may have speed issues or file bloat, especially if you index a notes field. It’s important to turn off the indexing for field that will never be searched by unchecking the option to “automatically create indexes as needed”. In addition, prevent access to these fields during search by turning off the Inspector option to allow access in find mode. You can even hide fields with the Hide Object feature but I’m getting ahead of myself. The key here is turning off indexes once the development process is over. While you may search fields during the development process, turn off indexing as needed when the solution is deployed.
Field Graveyard
I’ve worked on a lot of legacy systems in my career and the one thing that seems consistent is inactive fields. Amateur developers either forget to delete unused fields or get overwhelmed by the vastness of the project. The same is true for scripts, tables and layouts but I find extraneous fields especially perturbing.
While you are developing, it is very likely to create a field when attempting to solve a problem. Or, maybe a feature becomes obsolete, having been replaced by a more efficient solution. Whatever the reason, clean up your field list as you go. We all know what it feels like to try and clean our rooms once a month or our desktop once a year. It becomes an exponentially more horrendous job as time goes on. When I adopt a system that has unused elements, not only does it make it difficult to sift through the clutter but it is almost impossible to clean it up. You can try the Database Design Report in FileMaker Pro Advanced but it’s still a chore.
Conclusion
FileMaker is a very forgiving database design tool that can often lead to bad habits. Plan what fields you will be adding to your database. Make sure primary and housekeeping fields are in every table. Delete fields you are no longer using as soon as you don’t need them. Index only the fields necessary for users to complete their jobs. Treat Manage Database with respect and your solution will be a work of art that’s a pleasure to work with.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
Comments:
|
|
|
Gayle
|
04/03/2017
|
|
|
Keep on writing, great job!
|
|
|
JP A
|
02/26/2017
|
|
|
Great post Jaymo, but the link to FMDataGuard is broken. Please update ;)
Cheers.
|
Response by: John Mark Osborne
|
02/26/2017
|
|
Interesting... I tested just before publishing a few days ago. Oh well, what can you do. The link is fixed now. Thanks so much for pointing it out!
|
|
|
|
|
