FileMaker Pro is fantastic in that users can be instantly productive without much of a learning curve and no Database Design understanding. The FileMaker Pro platform is, however, an enterprise-grade database software platform, and requires database design understanding when developing mission-critical databases.

One of the biggest problems we usually have to address when inheriting systems developed by others are improperly structured relationships. Although it works, text fields with lengthy relationship keys can bring an application to it’s knees, especially when hosted on FileMaker Server over a network.

As we learned in database theory and application classes (database 101), relationship keys should be numeric. This makes sense when you think like a computer… it is easier to state that “1″ = “1″ in a relationship as opposed to “foreign key” = “foreign key” as you will need to break each text character into numbers anyway, adding work to the process.

Do you experience “Coffee Cups” when, say, adding a note to a Contact or Client with many related records in a portal? If so, the most frequent source of these inefficient workflow disturbances are improperly structured relationships.

FileMaker pro changes the cursor into a Coffee Cup when it is performing a task that will take some time. Frequently, it displays due to the extra work required to manage text based relationships. In a properly designed FileMaker Solution, coffee cups should only show when crunching massive numbers for reporting and in complex scripting.

Suggestions in developing Scalable, Properly Structured Relationships:

• Build Value Lists based on Tables with Numeric ID and Text Description.
For example, lets assume that each Purchase Order record is marked as “Open” or “Closed”. From a dashboard view, we want to be able to see Open or Closed PO’s.

Instead of having a text field in the PO table containing “Open” or “Closed”, we have a numeric flag field named “_flg_PO_Status”. We will have a separate table with 2 fields, “_kp_ID” and “Description”, and two records containing:
“_kp_ID” “Description”
1 Open
2 Closed

We will build a FileMaker Value List, “PO_Status_ID”, based on these two fields, display only second field “Description.” We will set the numeric flag field as a “Pop-Up Menu” displaying our new Value List “PO_Status_ID”. From our dashboard table, we will then created a numeric key field “_kf_PO_Display”. Create a relationship between “Dashboard::_kf_PO_Display” to “dashboard_PO::_flg_PO_Status” (More discussion of FileMaker Relationship graph management and a strong lobby to use the Anchor Buoy model with naming conventions in posts soon to come) Set the field “Dashboard::_kf_PO_Display” as a pop-up menu displaying “PO_Status_ID”.

Now, lets assume that there are 100,000 PO’s in the system and we only want to display the “Open” PO’s (500), it is much less work for the system to match numeric key fields both containing a “1″ instead of matching the letters of “Open” to the letters of “Open” in 100,000 records.

If there are only a hand-full of PO’s, the difference is harder to notice that when there are 100,000.