Object-influenced design in FileMaker Pro
In the past I've experienced some dilemmas in creating FileMaker databases, but I think the release of Filemaker 10 provides tools for solving most of them.
I set up my first FileMaker database to manage contacts and donations for Working Capital for Community Needs (formerly the Wisconsin Coordinating Council on Nicaragua). Over the years, it has grown in complexity as the organization's needs have evolved. Originally it was a flat-file database (which was all FileMaker could do when I first started using it back in the 1980s). Then FileMaker added relational database capabilities, I started having one table for "contacts," one for "donations," and other related fields (for example, a "letters" field that is used to generate and keep track of correspondence with WCCN's contacts).
In the 1990s, WCCN became involved in microfinance through its NICA Fund. This meant some added complexity in what we mean by "contacts." We still had members and donors, but now we also had investors. Actually, WCCN has several other types of contacts, including board members, staff members, grant-making foundations, news media, and government regulators in each state where the NICA Fund needs to register to comply with local securities laws. All of these contact types share some common characteristics. Each has a name, address, phone number, etc. However, each contact type also has attributes which are specific to that type alone. Only media contacts, for example, need fields specifying things such as circulation size and whether they are radio, TV or newspaper. Similarly, we only need to keep track of amounts invested and maturity dates in our investor records.
Initially, I tried to handle this by creating separate fields for all of these pieces of information within a single contacts table. I then created separate layouts for each contact type. The media layout displayed the fields for circulation size and media type, but didn't display the investor-specific fields. Likewise, the investor layout only showed the fields it needed and left out the others.
That worked, more or less, but it wasn't ideal. For one thing, it meant that the number of fields in the database profilerated to the point that it became harder and harder for me to keep track of them all. At present, WCCN's "contacts" table has more than 100 fields. Our related "investments" table has 149. For another thing, keeping all of the contacts in a single table meant that searching and browsing records is less intuitive that I'd like for end users of the database. If someone wants to search for all of our foundation contacts, the natural thing for them to do would be to go to the "foundations" layout and execute the "show all records" command. However, this ends up displaying all of the contacts in the database, not just the foundations. You can explain this to users and train them to work around it, but it's not ideal.
I've decided that a better solution, therefore, is to emulate the example of object-oriented programming, in which objects can be subclassed into more specific objects. Under this model, "investor" and "foundation" and "media" would all be subclasses of the class "contact." FileMaker's relational database methodology doesn't directly support object-oriented design, but it is possible to approximate it. Here's a discussion thread I found which discusses one way of doing this:
This method entails creating an "inner" table for contacts, with separate "outer" tables for its subclasses such as "investors," "foundations," etc. The inner table is connected to each of the outer tables through one-to-one relationships. The method described in the URL above uses the "allow creation of related records" feature in FileMaker to automatically create the related records, so end users don't even notice that they're dealing with two different tables. When they go to the "foundations" layout and execute "show all records," it only shows them the foundations.
I also used the new Script Triggers feature in FileMaker 10 to automate the creation of relationships between the contacts table and records in its subclasses. The contacts table has a checkbox field called "status" where users mark whether a contact is a foundation. I added an OnObjectModify trigger to the status field. Whenever someone modifies the status field, therefore, the script is triggered and automatically creates the related foundation record if that option has been checked. Here's the script:
If (Position(Contacts::Status ; "Foundation" ; 1 ; 1) > 0 and IsEmpty ( Foundations::ID Number ))
Set Variable[$id; Value:Contacts::ID Number]
Go to Layout ["Foundations" (Foundations)]
Set Field [Foundations::ID Number; $id]
Go to Layout [original layout]
For symmetry, I could have made the script also delete the related Foundations record when users uncheck the "Foundations" option, but I didn't want to make it easy for users to accidentally delete information. This means that the Foundations table may end up with some records that don't have have the "Foundation" option checked. I wanted a script that would try to address the issue automatically and also call users' attention to the discrepancy. I came up with the following script, which is triggered by OnRecordLoad in the Foundations layout:
If [Get (FoundCount) > 0]
If [IsEmpty ( Contacts::Status )]
Set Field [Contacts::Status; "Foundation"]
Else If [not Position Contacts::Status ; "Foundation" ; 1 ; 1 ) > 0]
Show Custom Dialog ["Foundation Status Check"; "This is a foundation right?"]
If [Get(LastMessageChoice) = 1]
Set Field [Contacts::Status; Contacts::Status & ¶ & "Foundation"]