A Little [FileMaker] Relationship Advice and Anchor Buoy

In this article we will discuss some of the under-the-hood technical details about how FileMaker databases are created and then explain why this is critical information for the business owner and beginning developer to know when building a FileMaker solution.

Relationships are hard. No, I’m not talking about people. Unless those people are an entity that you track in your FileMaker database. How are those people related to companies? And events? And jobs? And invoices? And, and, and… See what I mean? Complicated. Luckily, FileMaker makes connecting those various tables easy, in a friendly interface called the “Relationship Graph.”

This shows how we might connect 2 tables, Contacts and Events, by linking the primary key field in the Contacts table to the corresponding foreign key field in the Events table. Database schema 101. Things seem pretty simple when we get started. But as you get more tables, and want to slice and dice your data in more ways to make it more useful, you end up with a lot of relationships. How do you keep them organized on the graph? When most people start building their FileMaker custom app, they don’t think ahead. They just start connecting tables and adding to the graph, where it fits. If your app is very simple, this might work fine. Unfortunately, when we get called in to help fix a FileMaker app or take it to the next level, we often find a graph that looks like this or worse:

This is only part of a graph that “grew organically” as it was developed (and it is not even the worst one we’ve come across). It would be like not having a plan to build your house. As you go along, you decide you want an outlet here, and then one over here. Now you need to put a bathroom in, so you run some plumbing for a sink here and a toilet over there. And you will probably want an outlet in here somewhere, so let’s run some more electrical. Oh, and it gets cold in the winter, so you need to run some heating ducts through the walls, too. You can see how this might lead to problems?

We need a plan

Good, bad, or otherwise, there are many ways to tackle this problem. FileMaker’s flexibility can be a wonderful thing, but it can also be the source of some troubles. There are many relationship graph techniques or standards. Ray Cologon’s white paper Approaches to Graph Modeling (PDF link) from 2008 is still a great read if you are curious about such things… or if you just like to overthink things. But the one standard that seems to be the most universal, is the one referred to as the “Anchor-Buoy” model. The running joke at DevCon for many years was “It doesn’t matter what convention you use, as long as you use one. …and as long as that one is Anchor-Buoy.”

Without trying to start a holy war in the comments, I’ll say that right now that Anchor-Buoy is not perfect. There are plenty of advocates for other techniques, and they all have their merits.

But Anchor-Buoy offers something for complex apps that no other standard can match: clarity. The biggest benefit we see is that apps developed with this standard can be opened and worked on by any of our talented developers with very little explanation. They can look at the graph and instantly know the purpose of each table occurrence. FileMaker is all about context: layouts, calculations, scripts, relationships, etc. all operate from a specific context. Anchor-Buoy makes context very clear. There are some deviations or personal preferences, but here are the basic “rules” for the Anchor-Buoy data model that we follow:

1. Each table gets its own Table Occurrence Group (TOG).
2. Each TOG starts with an Anchor table occurrence (TO), on the left.
3. That Anchor is based on the main table for that TOG and is named in ALL CAPS.
4. All layouts are based on the Anchor TO.
5. All calculations are based on the Anchor TO.
6. Relationships spawn off of the Anchor to the right, and these TOs are called Buoys.
7. Each Buoy TO is named as the path from the anchor, separated by underscores, with the based table in ALL CAPS and the other tables along the path in PascalCase.
8. When multiple paths to the same base table exist, a descriptor is added to the end, prefixed by two underscores.
9. Optional: color coding can be used to indicate TOs with the same base table.
10. Optional: table names can be abbreviated, but we find this harder to read, reducing clarity. For instance, does INV mean Invoices or Inventory?

Here is an example of the TO names for a simple FileMaker app:

• CONTACTS
• Contacts_COMPANIES
• Contacts_Companies_INVOICES
• Contacts_Companies_INVOICES__PastDue
• Contacts_DOCUMENTS
• Contacts_EVENTS
• etc.

Here we show part of a complex app, that uses the Anchor-Buoy model with the anchors and buoys indicated by icons. Everything is clean and organized, with clear TOGs for each base table. Each TOG has an Anchor TO on the left, and a few to several Buoy TOs extending to the right. Any data you need to see from the Anchor can be found in one of the Buoys to the right. If you are working on a team, or handing your FileMaker custom app over to another developer, you do them a huge favor when your stick to these rules.

Note: The relationship graph is NOT an ERD (Entity-Relationship Diagram). Though there are similarities in how the FileMaker relationship graph looks and an ERD, an ERD performs a different function in showing the overall connections between data elements without redundancy and is known as the logical database design. The ERD is an important part of your database documentation that helps the developer to understand how data elements are related. This video provide a quick example of how to develop an ERD. The FileMaker relationship graph we reference in this article is part of the physical database design and is collection of queries (searches) in FileMaker and depends heavily on context to define where you are at. This FileMaker Relationship Design video helps to understand this.

Beyond Anchor-Buoy

I mentioned that there are other techniques and standards out there. Some of them are variations or hybrids of multiple techniques. One that generated a lot of buzz in the last couple of years is called Selector-Connector. It was developed by Jason Young of SeedCode and Todd Geist of Geist Interactive. One of the great things about Selector-Connector is that it can be added on to existing standards, like Anchor-Buoy. It can cut down on some of the redundant nature of the strict Anchor-Buoy model. In his fantastic blog FileMaker Hacks, Kevin Frank discusses Life After Anchor-Buoy. We don’t agree completely with his assessment on this one, but he does make some good points, and offers some great discussion. In fact, we made a good try at using Selector-Connector but in the end found that it slowed down large solutions and made it more error prone. Since the advantages did not outweigh the disadvantages we have removed selector-connector use from our solutions.

Whatever standard or convention you use to organize your relationship graph, you will be glad you used one. And so will your developer. If you are getting started, contact us if you want some advice on how to organize your relationship graph. If your graph is already a mess of a spider web, don’t be afraid to show us… we’ve probably seen worse! And we can help you unweave the spaghetti.

What does this mean to my business?

Now that you know some of the technical details behind a FileMaker solution relationship graph, it’s important to understand what this means and how this might affect your business.

First, as expert developers, we usually have a good idea of the skill level and experience of your developer within a few seconds of viewing an existing solution’s FileMaker relationship graph. If your FileMaker solution was developed without a best practice like anchor-buoy, it almost certainly is more costly to support, maintain, and enhance. There is risk that if the developer who created the solution leaves your company or is no longer unavailable, support will be difficult because more of the application knowledge is in their head (if you’re fortunate) rather than obvious from a clear and standardized approach to development. Integration with any external system will be more difficult. One of the first things we might have to do is refactor or update your solution to make it possible to work on it reliably.

Our recommendation for beginning citizen developers is that they take the time and learn Anchor-Buoy. Then use it as a method to organize the FileMaker relationship graph as it is the most widely accepted and understood best practice in the FileMaker world. This investment will pay off in many ways:

• You will be building a solid foundation in your database solution for any future work.
• There will likely be less bugs to fix in your software and more reliable operation.
• You will have lower overall costs in support and enhancements to your solution.
• Other FileMaker developers will quickly be able to understand your solution and assist with enhancements and questions.
• Your business will be at less risk with a solution that is maintainable for the long term.
• You will gain the ability to more quickly connect your solution to other applications (integrations) to take advantage of off-the-shelf software like like WordPress, WooCommerce, MailChimp, and QuickBooks.

If your existing solution is large and built over a long period of time without the knowledge of this technique, it may be too costly to organize the relationship graph to anchor-buoy all at once after the fact. It may make sense to refactor the relationship graph and scripts over time as changes are required and enhancements are requested. Eventually, you’ll have a solution that will be easy to maintain, cost less to support, and can connect to external systems easily.