FileMaker Relationship Graph – Anchor Buoy 2.0 – “Bridge”
Anchor Buoy is nothing new to the community and was a new concept when FileMaker 7 was released as a way to model the relationship graph to follow suit with the old school model of having multiple files from FileMaker 6 and earlier. Many years ago, while working for another developer, it was heavily engrained in me to the point that I no longer though about it. At that point I could see dead pixels. Actually, I could see if field alignments were off by a pixel without much thought. He was a great teacher. Thank you John.
Over the years as FileMaker has continually evolved with each new version. It brought about many new features that by taking advantage of them would allow for graph simplification. Two of which were game changers: ExecuteSQL and Perform Script on Server (PSoS). Having the ability to query data from another table regardless of the current context without a relationship by using ExecuteSQL or the ability to create a record by using PSoS caused me to rethink everything. For my team, while initially they thought I had lost my mind (again), with these new concepts (2016 when updating Jarvis CRM from version 2 to 3) they now see the value and have contributed to them. When it comes to on-boarding a new developer, it takes hours instead of days to acclimate them to our solutions. Everything works smoother and requires less training and explanation on how they work. Predictable consistency.
Time for the Shake Up
Traditional Anchor Buoy says that for every table in the solution there must be a Table Occurrence Group (TOG) for that table. That equates to a layout in FileMaker as well. Let’s see so a file with 60 Tables there are 60 TOG’s. Hmmmm, how many of those end up being used? Reality has shown me about 40%. Anchor Buoy naming conventions are also about order. The conventions keep thing showing in groups alphabetically, which is a great thing, but what about the 60% that we don’t need to see? It’s still there. I have found that the redundancy is unnecessary. Sorry to the hardcore Anchor Buoy traditionalists! It’s time for me to toss the conventions to the curb and simplify everything. Everything today is about being simple including short texts and emails, so why not the relationship graph?
Traditional Anchor Buoy
Here is a sample of the FileMaker relationship graph as I was taught in the standard Anchor Buoy model years ago. While it is clean and organized, there is a lot going on.
That’s a lot to take in, especially on a simple solution. Go back and look at all of the replicated tables (Payments, Invoices, Shipping, etc.) Do we really need them? The naming conventions are consistent, but the listing of table occurrences can be daunting. In reviewing the graph above and looking at solutions over the years I made a lot of decisions to simplify the graph in general while maintaining some of the things I liked about Anchor Buoy.
Presenting Anchor Buoy 2.0 – “Bridge”
Let’s Pause for a moment while you compare.
What? Really?
Yes, really. These changes reduced the FileMaker relationship graph on a massive solution by 60%. The concept is that a child table that is a directly related to a parent table should be directly connected to the parent on the graph. However the child table name maintains it’s first table occurrence name when it was added to the graph to maintain proper context for calculations. The other significant change was the addition of the Bridge table. This table is a cartesian join to all table occurrences on the graph that have a layout associated with them. By adding the Bridge table, it removed the need for having navigation and preferences related to each table occurrence. An added benefit was it made the navigation module completely modular.
Naming Conventions and Rules to Share
Table Naming Rules:
- Table names are to be plural and are to correctly describe the entity.
- Tables will have a 3 character abbreviation to correctly and easily identify the Entity.
- Explicit Child Tables will have a 3 character abbreviation that, when possible, will show some commonality to the parent.
- Join Tables will have a 3 character abbreviation that, when possible, will show some commonality to the tables being joined. Please note that the last letter in a Join Table is always a “J”.
- All table abbreviations will be followed by a single space.
Table Occurrence Naming Rules:
- All base tables that will have an interface (layout) will have a cartesian join to the Bridge Table.
- All Tables that are explicit child tables will be directly related to the parent. See INV and INL below.
- If a child table such as COJ is necessary in an additional TOG, then it will follow standard anchor buoy conventions. con_COJ. The character abbreviation of the table will always be in all caps.
- We strive to never exceed 4 tables deep from the Base table. This is for performance and organization. Use Execute SQL as much as possible when it will not impact performance.
- Other variants are used to show explicitly the purpose of the relationships, such as noting a billing or shipping relationship for an address.
Much More
While there are many more naming conventions to share, we have included that in an attached PDF along with some sample files to view the relationship graph.
See below for download file.
A Look Under the Hood in Jarvis CRM 5.0
We have never publicly released the relationship graph for Jarvis CRM. So this is it’s first appearance. For all that it does as a solution the graph is incredibly simple. There are a few differences from what we have learned from what we shared from above. Initially we had a rule that the Bridge table would only have 2 fields, a primary key field and a field “_x” for our cartesian joins. Over time I was persuaded to add a global and I decided to add a field called “_ka_one” that contains the value of ‘1’. This _ka_one field is used for sharing value lists across all tables. Such as Active Users, where a corresponding numeric field contains the value of ‘1’ or ‘0’. These types of relationships are now starting to go away since card windows appeared, but non the less they still have their vale to not clutter the graph.
We recognize that there is no perfect solution for naming conventions and graph organization. The most important one is the one that is used consistently in a solution, choose what works best for you and stick with it in a solution, modify and adapt as you go in future FileMaker Applications.
We hope this inspires you and helps you to become more efficient in your development processes.
Stop by our Booth #2 at #FileMakerDevCon next week at the Gaylord Hotel in Dallas, TX.
I’ll be glad to share more on these techniques or catch me at the FileMaker Developer Conference in London or Sweden where I’ll be presenting on this top in much greater detail.
We will be demoing features throughout the week and you can contact us in advance to schedule one.
DevCon Only Show Special 30% off from today until August 17th.
Use Code: DEVCON30
Looking forward to seeing you there,
