FileMaker and Selector Connector
I’m blogging here about how I apply the principles of the Selector Connector method for relationship creation in my work as a FileMaker developer.
•So what is Selector Connector?
Selector Connector is a method for organising the FileMaker Relationship Graph and determining how each relationship will be managed.
•Why use Selector Connector?
Primarily? Ease - which in my book translates to efficiency which in the real world usually means less time spent doing the same thing. I like that. I like that a great deal. In my mind, Selector Connector relationships are easy to manage, allow a great deal of flexibility and they're a snip to update / refresh and that can be a particular boon when handling features that need frequent refreshing.
•So what method does it replace?
Selector Connector doesn’t really replace anything as it works well alongside other relationship methodologies.
The most common method for handling relationships in FileMaker is usually referred to as Anchor Buoy and, to be fair, there’s not much that’s wrong with it; Selector Connector is just another way of controlling / visualising relationships in FileMaker. That being said, Anchor Buoy and Selector Connector can coexist quite happily so there’s nothing stopping a developer from incorporating both methodologies (or indeed, others) in their work. In short, using one does not preclude the use of the other.
•How does it work?
If you have three tables in FileMaker (Red, Green and Blue) and want to link them using the Anchor Buoy method, you will probably link them like this:
Those equals operators could be match fields where a field in one table (eg a primary key field) matches a field in the other table (eg a related primary ID field). Of course, they don’t need to be match fields - I’m just making examples here.
To create the same effect using Selector Connector, you need this kind of approach:
Straight away, we can see that there are fewer relationships (7 relationships for Selector Connector rather than 12 for Anchor Buoy) however despite this, the Selector Connector configuration matches the Anchor Buoy configuration in terms of functionality.
•What is the Link table?
The Link table is a table that links most if not all tables in the database so that a relationship can be made from any given table to any other table (in the diagram above, you can see that the Link table is connected to everything). The Link table holds a single record of global fields and that’s a single record regardless of how many users you have or whether it’s a shared file or not.
As you can see from the diagram, the tables on the left are related to the Link table via a cartesian join (ie a match all relationship where the match fields automatically match regardless of the data either record holds). The cartesian join is essential here because the Link table only has a single record in it and if the fields aren’t globals, the single Link table record will keep being locked by users as they make use of the Link table (which would stop the Selector Connector model from working). Global fields can still be set regardless of the type of relationship between two tables and they can be set from anywhere which is of huge benefit to us.
•How does the cartesian join work?
The tables on the left are related to the Link table via a non-create / non-delete relationship formed by two quite unimpressive ID fields. I refer to these fields as link fields (ie a_LinkID_g).
For example, in the Selector Connector diagram above, there’s a relationship formed between the Red table and the Link table. The relationship in question would be based on two global fields, as follows:
Red::a_LinkID_g X Link::a_LinkID_g
With my naming convention, using the “a_” prefix allows the ID fields to be shown at the top of the table field list (presuming that you’ve got the correct alphabetical sort order on) which is handy. The “LinkID” part makes it clear that this is an ID field used for creating a relationship with the Link table. The “_g” is suffix indicates the global nature of the field. Lastly, the lack of spaces in the name makes them easier to read in any SQL statement, should you use SQL at all.
The a_LinkID_g fields are numeric by nature and always contain the value 1 (for the cartesian join, the value isn’t important - I just like to be consistent).
•How does the cartesian join relation work?
Cartesian join relationships in FileMaker are where a record in one table will match any / all records in another table. To create a cartesian join relationship, all fields used in the relationship must match via the X comparative operator rather than the more usual = symbol (hence their sometimes being referred to as cross join relationships).
Here’s an example of a cartesian join relationship between two link fields.
You can see that the UserDevice table is related to the Link table by two fields, both of which are called a_LinkID_g.
•So why have a value 1 in the a_LinkID_g fields?
Since we’re matching on anything, the value itself isn’t really important - you could have a UUID or a zero or whatever floats your boat; I use 1s.
•Refreshing the Link relationships.
When a value in a field (that forms part of a relationship) is changed, FileMaker automatically refreshes what records are available via the relationship. Since we’re using a cartesian join, it doesn’t matter what value you have in the Link fields; what matters is the act of entering a value into the field - this will have the effect of forcing the relationship to refresh. So, you can have two related Link fields, both holding a value 1 and you can ‘change’ the value to 1 and it will still force FileMaker to refresh the relationship.
Having global Link fields has a double benefit as you can reset any relationship from any table with a Set Field script step.
•How does this work in practice?
Imagine that you have two related tables, for example Orders and OrderItems. You might have a layout based on the Order table and on the layout, a portal to the OrderItems table. FileMaker can be a little temperamental with updating relationships, particularly in a networked solution and even more so when globals fields are used with portal filters. Rather than use the mountain-out-of-a-molehill approach of using a scripted “Refresh Window” step, just changing one of the a_LinkID_g field values from 1 to 1 will have the effect of refreshing the relationship without invoking the time consuming index rebuild associated with a Refresh Window step. Even though you’re setting the field to the same value, the fact that the set field step is called is enough for an unobtrusive refresh of the relationship.
Thus we have a mechanism for linking tables to the Link table that’s easy to maintain as well as having the advantage of providing a very simple mechanism for a gentle relationship refresh. Two birds; one stone.
•Since all of the tables have a_LinkID_g fields, is there an easy way to set all of them in one go?
I usually set Link fields via a script that runs on startup which is handy as you never need to update your startup script with each new table you introduce. To do this, I populate a variable with all of the table names in the database and then create a loop that goes through each of the variable values, setting the equivalent field equivalent a value of 1.
Set Variable [ $BaseTableNames ; Value: ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_Tables" ; "" ; "" ) ]
Set Variable [ $Counter ; Value: "" ]
Set Variable [ $Counter ; Value: $Counter + 1 ]
Set Field By Name [ GetValue ( $BaseTableNames ; $Counter ) $ “a_LinkID_g” ; 1 ]
Exit Loop If [ $Counter ≥ ValueCount ( $BaseTableNames ) ]
I run this on startup but tend not to use it elsewhere unless I need to force a full refresh as it's a bit heavy handed - far easier to just reset the Link::a_LinkID_g field again as it’s very quick and works well in a multi-user, networked environment.
•How do you use Selector Connector and Anchor Buoy?
Going back to our original selector connector diagram, I’ve described how to create a relationship from the left side tables to the Link table (that would be via global fields and a cartesian join). Obviously that’s only half of the story as we still have the relationships from the Link table to the right tables.
In the Link table, aside from the aforementioned a_Link_g field, there are also a series of global fields (one for each table in the database) which allow a great deal of flexibility when relating tables. For example, I might have a database with these tables in it:
In the Link table, I would create a global field for each of these tables, eg;
I’ll relate the Link table to the required table via one of these fields like this:
So, the Link::a_EventID_g field is linked to the primary key field in the Event table (ie Event:a__ID). Notice the name of the related table? It's Link | Event | EventID - for me this is a nice clear way of saying that the Link table is related to the Event table and that the primary field used to form the relationship is the EventID field.
Now whenever I want to view a specific Event record, I can populate the Link::a_EventID_g field with the required ID and it will create a match - a match that I can view from almost anywhere, just as long as I can get to the Link table (which I should be able to since I link every table to the Link table via one of those cartesian joins I described earlier).
For example, if I’m on a layout based on the Job table but want to see a specific Event record (related or not) I grab the required Event ID, put it straight into the Link::a_EventID_g field (remember, I can do this from anywhere as I’m related to the Link table via a global cartesian join) and that’s it - the data will be viewable.
But what if I want to see all of the Event records? That’s easy too - all I do is create another relationship which uses another cartesian join to match all records. Like this:
So, from the Job table, I can either show a specific record or all records, depending on the relationship I use. Once again, take a note of the relationship name. This time it's Link | Event | All showing that the Link table is related to the Event table and that all records are available.
It is this very easy to employ flexibility that is of such interest to me and that's why I employ it daily.
•Did you come up with the Selector Connector concept?
No and it would be difficult to say who did since there are so many developers around the world who have their own unique take on developing with FileMaker and who have been using techniques similar to this for quite some time. Saying that, Todd Geist and Jason Young (links below) are the two FileMaker legends who have been most instrumental in the naming, advertising and explanation of the Selector Connector principle. In my mind, without them, the ideas behind Selector Connector would have been relegated to just a few FileMaker developers around the world and even then, quite possibly, in a form which is not as polished as it is thanks to these two chaps:
Please be aware that I have no affiliation with Jason or Todd (or indeed the fictional Batman character Jason Todd) and certainly have no control over the sites that these URLs link to.
If you notice anything wrong with this blog, think that you can do it better or just want to discuss it, then please get in touch.