Filemaker Best Practices
Concepts
- Never store two copies of the same data in a database. This means it is possible to have a conflict, and that you have to update MULTIPLE fields when you update data.
Do not store combined data. Have First Name, Last Name, and Whole Name fields. Whole Name should be a calculation field merging the First Name and Last Name field.
Keys
- You may want a surrogate key even if you have a BU provided natural key
- Sometimes you may need to set relationships based upon the BU key
- Always disallow duplication of keys in the database.
- Don't store duplicate data in your table, use relationships!
Relations
- In general, don't allow editing of related fields... it's too easy to get confused.
- Whenever possible, use relations that are included in imported data. That way tables don't need to be "hand stitched" together.
Value Lists
- Value Lists should ALWAYS be set by contents of a field, except for the case of simple codes.
- Value Lists should NEVER be manually set to large amounts of data.
- If you don't follow this rule, you get duplicate data.
Layout Widgets
Pop-ups
- Better for short value lists
- No type-ahead
- Has a "3D" look
Drop-Downs
- Better for longer lists
- Allows for type-ahead
- Looks like a text field unless you add the drop down arrow
Pop-ups and Drop-downs handle two-field lists differently:
- Pop-ups allow you so show just the description OR the description and the ID
- Drop-downs only show the ID, and must be combined with a text box to show the description.
Also, Drop-downs allow typing directly into the field after hitting ESC. Pop-ups allow pressing "other" and then typing into a window. If the key has meaning to the user, a drop down would be better, such as in the case of HEGIS codes.
See http://my.advisor.com/doc/18168 for details on the "sticky text" issue of pop-ups and drop-downs.
Field Names
- Use Inital Caps
- Use_Underscores_If_You_Need_Compatability
- Singular
- Use UID for UID, not ID
- The exception to these rules are when you are storing data straight from UIS. In that case, it is okay to use the UIS names.
Layout Names
- Use a word to describe what it does first... preferably a verb.
- Print - Label or Report - Active Users or View - Students
Value List Names
- Follow field names, but use plural
Table Names
- Use plural form - Rooms not Room
Required Fields
- Table_ID - autoincrement number
- Created_By - account name on creation
- Created_On - timestamp on creation
- Last_Edited_By - account name on modification
- Last_Edited_On - timestamp on modification
- Notes - Room for notes
Eventually, we will have revert capability - undo changes per record. This will require another field.
Working with UIS downloads and Excel files
- Don't throw away good information
- Avoid holding information that is best put in a related table - i.e. import UIDs, but not names. Use the UIDs to relate to the people table.
- Keep local data out of the table with the UIS data. Link with the UID instead.
