Creating powerful dynamic self-relationships in FileMaker

Filemaker can create flexible relationships which grow dynamically with the information fed in.

Filemaker's ability to create effective quick and dirty databases for all manner of purposes makes it a hard tool to give up. Moreover, the user's ability to mould it ever closer to need from such a rough and ready start can mean that no other tool even comes close as a database development vehicle of first choice.

One of its greatest virtues is to create flexible relationships which grow dynamically with the information fed in. I'll demonstrate by using the illustration beneath of a simple URL repository.

Illustration 1: concept view

Aside from creating a permanent, portable repository of links, however, the neat thing about this extremely simple solution is that as you add to it, the category list in the drop-down at 1 dynamically updates to provide the means by which information appearing in the 'portal' (in FileMaker parlance) at 2 is filtered and displayed.

A portal is effectively a window through to related data in another table, here related by the category of 'news'. If the selection here had been for music, the portal would have contained a list of URLs meeting that description.

At 3, clicking the 'view' link for the Sydney Morning Herald will deliver to the screen related to the record selected, as seen beneath under Illustration 2, edit view, whilst;

At 4, clicking on 'launch' for one of these records will launch the relevant URL in your browser.

Illustration 2: edit view

Illustration 2 shows what happens when you call forth a record from the portal by clicking 'view' to add your own comments or notation.

This application can be used to store any dynamically updating list, of course, not just URLs. It could be applied as a simple contact manager, for example, separating work from social acquaintances, and so on. And, likewise, if contact management were the use to which it was put, instead of launching a URL, you could dynamically generate an email to the selected recipient, launching your email application, straight from FileMaker's standard built-in list of pre-scripted commands.

There is an additional merit in this form of bookmark management in that it is secure: no-one else need ever know what you're keeping, and you can lock the file with an easily applied password.

This solution uses just one table, five fields, one easily created layout, a simple one-step script and a single relationship relating the table's records to itself. As usual, within FileMaker, it's possible to skin the cat in many ways, but these are the bare essentials for an entirely workable solution.

Ok, so here's how to build it.

Step 1: First create your table

Illustration 3: field creation

When you set up your table, FileMaker invites you to create its fields, most of which, shown above, are self-explanatory. The only one which perhaps needs amplification is gCategory, so named because it is a global field - that is, it holds one temporary value, and its purpose is to solely to make a relationship with other data, depending on which value is fed to it at that moment. On the layouts above, gCategory is simply labelled as 'Category'.

Step 2: Create the self-joining data relationship

Illustration 4: relationship definition

In the Relationships layout graph, we then link gCategory to Category by clicking and dragging from 'gCategory' in the left hand table to 'Category' above in the same table. This automatically generates the second 'table occurrence' pictured on the right, by default labelled 'URLs 2'.

Step 3: Create and format the single layout

Within its Layout Mode, FileMaker enables the creation of myriad layouts at the user's behest, each of which which essentially filters the view of data contained within a database. That is, we could define a list view to view all records simultaneously, or a layout to look at and modify one record at a time. In this layout, however, shown at Illustration 2, whilst looking at a single record, we are also viewing all the others to which it is related by its definition through its portal.

Illustration 5: layout content definition

In Layout Mode, then, fields and portals are simply dragged and dropped and formatted on the layout using the tools in the left hand menu.

The text for 'view' and 'launch' is added using the text tool, and these are defined as 'buttons' to launch their respective actions. The button actions are selected with a simple right-click, either to take the user to the current record, or launch the URL in an external browser. As these are pre-set options, there is no need for any further scripting to achieve the required outcome.

Illustration 6: simple script button definition

Step 4: Define the gCategory value list

Illustration 7: value list definition

At this stage we use the Define Value Lists command to define the Categories list. gCategories uses this list to filter our view, through the portal, of web site addresses contained within the database. This list presents its values as a single instance of every unique value contained within the Category field of the data table.

Step 5: Database in use: Create new records with existing category values

The simplest way of creating a new record which inherits an existing category value is simply to type within the Site Name field inside the portal using the next empty row. If the ability to do this has not been disabled, the record created will inherit the parent category of the value currently contained in gCategory.

Step 6: Create new records with new category values

Illustration 8: layout text added as script button

To create a new record and a new category value, we create a simple two-step script in FileMaker's ScriptMaker module. The layout text 'create record in a new category' is defined with a single right-click as the button which activates it.

Illustration 9: simple new record creation script definition

The 'Go to field' instruction ensures you end up in the right place to enter the new category when the script executes.

In Short

And it's as simple as that. Sure, there are many ways of embellishing and adding complexity and sophistication to this solution, but I've now used this in myriad ways in its elemental form, and continue to find new applications for close variants of it. I am sure you will find plenty more.

Editor's Picks

Free Newsletters, In your Inbox