Object-influenced design in FileMaker Pro

In the past I've experienced some dilemmas in creating [http://www.filemaker.com 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 [http://www.wccnica.org 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 [[w:microfinance]] through its [http://www.wccnica.org/nica 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:

http://www.fmforums.org/forum/showtopic.php?tid/196508/

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 ))
  Freeze Window
  Set Variable[$id; Value:Contacts::ID Number]
  Go to Layout ["Foundations" (Foundations)]
  New Record/Request
  Set Field [Foundations::ID Number; $id]
  Go to Layout [original layout]
End If

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"]
    End If
  End If
End If

Permalink

Nice post. I'm also looking into object oriented paradigms for FileMaker and have, in my own way, stumbled across a similar working method. So, just a thought, but you could manage creation of your related Foundation record without the script trigger. If the Foundation status field exists in the Foundation table, populating it on the Contact layout would automatically create the related Foundation record.

In reply to by Anonymous (not verified)

Permalink

Many people don't need all these bells and whistles and never end up taking advantage of them. But by offering tons of extras that almost no one will use, some web hosts can jack up their rates. So its important that you know what services you require, shop for them by scrutinizing and reviewing the web hosts you come across. tampa seo firm

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer