|
|
|
|
Wrangling Relationships
|
Level: Intermediate
Version: FileMaker 16
Category: Relationships
Tuesday, November 7, 2017 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The relationship tab in Manage Database is not an ERD (Entity-Relationship Diagram). Sometimes it looks a lot like an ERD but it is so much more. An ERD represents the structure of your solution while the relationship graph contains structure as well as what I like to call techniques. Techniques are relationships that allow a feature to function properly, not provide structural integrity. You might create a filtered portal as an interface option or use a multi-key relationship and a script to drill down through records to create found sets or even a conditional popup menu that requires a non-structural relationship to operate.
Anchor-Buoy
Relationship techniques play a big part in my development work and can easily out number structural relationships. It can get quite cluttered if you don’t have an organizational methodology. When you first start working with FileMaker, all your tables are connected in the relationship graph. Many have described this as a spider web or spaghetti design. With a simple FileMaker solution, the spider web is easy to dissect and is a good choice. With complicated systems, that have dozens of tables, the spider web approach creates a dizzying array of criss-crossing relationship lines that are difficult to track.
A better solution for complex systems is the anchor-buoy organizational structure. People sometimes call it a squid because of the way it ends up looking in the relationship graph. The idea behind anchor-buoy is to split the relationship graph into groups, anchored by a starting table occurrence at the left. Each anchor represents a layout or a group of layouts. In the simple example diagram shown, the INVOICES, CUSTOMERS, PRODUCTS and LINES table occurrences are the anchors and the table occurrence from which all layouts will utilize.
What's the Advantage?
In the anchor-buoy system, there is a significantly larger number of table occurrences than the spaghetti approach. That's because of all the duplicate table occurrences required for the different groupings. For example, the table occurrence grouping for CUSTOMERS and PRODUCTS will both include a table occurrence representing the Invoices table. Otherwise, CUSTOMERS and PRODUCTS could not see Invoices.
So, what are the advantages? For starters, if all your table occurrences are connected in a spider web, one long list of table occurrences is displayed when placing a field or a portal on a layout, regardless of whether you may ever use all the paths that are created. The result is a single cluttered list of table occurrences that mean nothing from the perspective of the current layout, making it difficult to select the correct related table to complete the job at hand.
The anchor-buoy technique creates a nice division between related and non-related tables. In other words, the anchor-buoy system is really the natural way for FileMaker to be organized. Otherwise, why would this division in the dialogs showing table occurrences exist? Anchor-buoy makes more efficient use of relationships, avoiding unnecessary connections between tables occurrences. While a small solution doesn't need to worry about unneeded connections, a larger system starts to stretch the graph beyond it's design, requiring a better way of organizing relationships.
IMPORTANT: Not all table occurrence (TO) listings will display groupings. Only if FileMaker is clear on the context will TOs be segregated in relation to the anchor. For example, calculations in Manage Database, such as calculation fields, auto-enter and validation, Replace Field Contents and record level access in security will follow anchor-buoy organization because the table or layout is known. However, scripts, Custom Functions and conditional formatting won't know the context and therefore will display a long list of unorganized TOs.
Anchor-Buoy also makes it easier to read your relationship graph. This seems counter-intuitive with so many additional table occurrences increasing clutter. But, the anchor buoy system actually provides organization for your relationship graph. It allows you to focus on the part of the relationship graph that is pertinent to the layout where you are currently working. With the spaghetti approach, you have to look at everything, all the time. Think of table occurrence groups as tabs for the different areas of your relationship graph.
These advantages don't seem like much but when you've worked on a complex FileMaker solution, organization is key. Each little delay in locating something can increase the time required to perform a task. Multiplied over multiple tasks, small efficiencies translate into big savings. This translates into increased profits for the commercial developer and time for other tasks as an in-house developer.
GTRR
You may be wondering how the Go to Related Record script step (GTRR) fits into the anchor-buoy system. Fortunately, there are no operational or programmatic differences for GTRR when creating groups of table occurrences. That's because GTRR can do a thing called island hopping. For example, you could drill down through records, with GTRR, starting from CUSTOMERS and ending in INVOICES_Customers but display the found set on a layout based on INVOICES (see relationship graph diagram above). No special options, it's just the way GTRR works. If you're like me, drilling down through relationships is one of the key navigational methods so anchor-buoy better support it.
Naming Conventions
Naming conventions are also very important in the anchor-buoy system. I prefer to name the anchors with all capitals so they can easily be distinguished from non-anchors. Non-anchor table occurrences also include the source table name in all capitals, followed by the name of the anchor in proper case. This concise naming convention helps identify the source table and the table occurrence group at a glance. If more than one TO from a source table is used in a grouping, naming conventions get a little more complicated. I like to keep structural relationship names as short as possible as shown in the example graph. I like to add on an extra word or two describing the functionality, only when necessary. In other words, the longer the name of the TO, the more likely it is a technique rather than structural.
Finding an Anchor
With all these table occurrences, it can be tricky to find the grouping you need to examine. Colors can help but I find myself scrolling up and down a lot, often passing the grouping I'm trying to find several times. What I like to do is create a legend using the Text Note tool. I draw a little sticky pad representing a legend, listing each anchor with a corresponding number. Then, I draw smaller post-its next to each anchor with just the number from the legend. To find a grouping, locate it in the legend and type the number of the keyboard. The relationship graph scroll to the sticky note containing that number. Make sure your legend starts with something other than the first number of the legend, like the word "Legend". Also, place your sticky note number far enough below the anchor so you actually see the anchor TO on the screen and don't have to scroll any further.
Every Source has an Anchor
Some developers prefer not to anchor every source table because they may not need to provide an interface via a layout. A good example is the LINES table. It is unlikely you will ever show a user a layout displaying records from LINES. Sure, there will be lots of portals showing LINES but no layouts that a user will need to see. Still, I like to anchor non-interface tables because you never know when you might have to create an interface, such as a picker. And don’t forget reports. A common report would be how many products you sold last month or last week. Why not anchor all of your report table occurrences to LINES so it is nicely organized.
It's the same idea behind creating primary keys in every table. Even if the table has no children, I still create a primary key. You never know when you might need it. Maybe you need to collect unique value for a multi-key. Maybe you decide to add a child table. Why go through the hassle of adding and populating a primary key when it has so little overhead. Just apply the same concept to anchor table occurrences and create an anchor for every table.
WARNING: When deciding on the table occurrence to use for the anchor in the anchor-buoy system, always use the first TO created by FileMaker when the source table is defined. This initial TO is used as the default evaluation context for any new calculation formulas. I've made the mistake and had to constantly change the evaluation context on every new calculation field. What a pain! And, if I forget to change the context, I often have an even a bigger pain cause I might not notice a calculation is not behaving because of context. Arggghhhhh!
Start and End
The anchor-buoy system is really not hard to master. All you are doing is separating the table occurrences into groups based on an anchoring system. The perspective or context of a layout governs almost all actions in FileMaker so grouping doesn't change how you utilize relationships. Whether you are placing a related field on a layout or running a script that starts from a layout, context governs how that related field or script will function. The anchor-buoy system just makes it easy to determine functionality by identifying the start and end of a relationship more easily.
The best way to understand relationships is to identify the start and end of a relationship. The layout usually determines the starting point but not always. Calculations and value lists store the start and end within their definition, essentially ignoring the layout where they reside. In the case of scripts, context is determined by the layout where the script is initiated or by a Go to Layout step at the beginning of the script. Understanding how context affects your relationships is one of the most important FileMaker concepts. Anchor-buoy just makes context easier to recognize by organizing a large solution into smaller groupings. It's analogous to putting layouts and scripts into folders.
The ending point is specified by the related field, portal or script step in relation to the current layout. What related data displays or is modified is determined by the path from the start to the end. It’s seems like a simple concept but it baffles most people for some reason. Maybe “baffle” is the wrong word. Amateur developers forget that context or perspective when it needs to be at the forefront of their thought process. I am certainly guilty as well. How many times have I placed a field on a layout and selected the wrong ending point? The answer is endless times.
The anchor-buoy system makes it easier to select the end point because you can focus on a smaller graph that flows from start to end in a natural way. The spider web doesn’t really allow you to visualize the relationship path as easily with relationship lines going in all different directions. So, make start and end your mantra when developing so you don’t forget to consider it at all times.
Determining Cardinality
Cardinality is a fancy word for relationships. Do two tables have a one-to-many, one-to-one or many-to-many relationship. How crow's feet or cardinality are determined in the FileMaker relationship graph may surprise you. In an ERD, you draw the type of cardinality. And, if you're like me, you often draw it incorrectly (shhh). On the other hand, crow's feet are automatically created by FileMaker. It may seem you have no control but you actually do once you understand how it works.
If a field is defined as an auto-enter serial number or validated as unique, FileMaker will set the cardinality as a single-line. Otherwise, FileMaker will assign a crow’s foot. It’s that simple. This is also another reason why the relationship graph is not an ERD. For example, you may have a one-to-many relationship as defined by other restricting factors, such as a calculation field, but FileMaker may show it as a many-to-many relationship. Truly, the relationship graph is only a guide to cardinality and not a fact.
It’s also important to note one other indicator on the relationship endpoints. If a global field, unindexed field or unstored calculation is used as a key field, FileMaker will display what I like to call a stop sign. The indicator is simply a flat line indicating the relationship is one-way. Two-way relationships require indexed fields on both ends of a relationship. However, one-way relationships are not bad. In fact, they can be quite useful as will be explored in other articles at this web site.
Selector Connector
The big news regarding relationships is the Selector Connector relationship model. The idea behind the Selector Connector is to make everything available from everywhere in order to create generic pickers. While this sounds great, it's simply the renaming of the archaic spider graph where everything is connected to everything. While it is much more sophisticated than just connecting everything, it defeats the purpose of the Anchor-Buoy relationship method. The reason anchor-buoy was invented was to tame the long pop-up menus and relationship graph in complicated solutions. Selector Connector advocates claim it enhances anchor-buoy but in reality it negates it's usefulness. With everything connected, the long popup menus are back. I'm not saying you shouldn't use the Selector Connector, just make sure you marry the correct relational model with the correct project. For your enjoyment, here is a link to a blog from one of the inventors of the Selector Connector so you can educate yourself.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
Comments:
|
|
|
Mimi Morris
|
08/26/2020
|
|
|
I saw you present at DevCon 1998 (? - i should check my t-shirt) and your knowledge of FM was so impressive. I've been a fan of FileMaker for 20 years and continue to learn from masters like you!
|
Response by: John Mark Osborne
|
09/03/2020
|
|
Thanks for your kind words. I really appreciate them. It's people like you who remind me why I do this. Thanks for taking the time.
|
|
|
|
Charlie Holdener
|
08/17/2019
|
|
|
Great article, extremely helpful. I’m 2 weeks into learning and 1 week into using FileMaker, looking for foundational concepts so I’m working with it rather than against it. This article confirms and extends some things I had started to notice and wonder about. Thank you!
|
Response by: John Mark Osborne
|
08/19/2019
|
|
Glad you liked the article! FileMaker is one of the most underrated tools on the planet. Enjoy your journey!
|
|
|
|
John Mark Osborne
|
01/08/2019
|
|
|
With the release of FileMaker 17 and the Master-Detail Layout feature, the Selector Connector relational model is no longer necessary.
|
|
|
|
