Reciprocal Linkage

Challenge: in a multiuser document management system, facilitate the linking (and unlinking) of any document with any other. Make it clear which documents are linked to the current document, and make sure the approach is multiuser friendly — in other words, allow document records to be linked and unlinked even if one or both of those document records are currently being edited by another user.

Today we have two demo files, one using standard serial numbers and one using UUIDs. Except for serials vs. UUIDs, the two files are functionally identical, and in the interest of clarity, I’m going to focus primarily on the serial number version.

Demo files: reciprocal linkage & reciprocal linkage with uuids 

Additional goals:

1. Don’t allow a document record to be linked to itself
2. Don’t allow document records to be linked to each other more than once
3. Make the approach as simple and lightweight as possible

From a usability standpoint, the demo is bare-bones and straight-forward. From the “doc” layout, you can link and/or unlink the current document to any other document via the corresponding buttons. You can also click the “>” icons to navigate to other document records.

Typically links between database entities imply a parent/child relationship, but in this case the relationship is sibling/sibling… and links are always reciprocal (bi-directional), i.e., if doc A is linked to doc B, then doc B is also linked to doc A. And likewise for unlinking: a pair of docs are either mutually linked, or they aren’t.

So, how to model this? Since any document can be linked to any other, we are looking at a many-to-many relationship, and the standard way to resolve this is via a join table, which, conveniently, is named “join” in today’s demo.

One of the most familiar uses of a join table is in a business system to facilitate the many-to-many relationship between Products and Invoices (since invoices can contain multiple products, and products can appear on multiple invoices), e.g.,

In the above example Products and Invoices are two distinct entities, but a join table can also link different records from a single entity — in this case, Documents.

Let’s go under the hood. The table structure is extremely basic…

…here’s the portal that displays linked documents…

…this is the corresponding section of the relationships graph…

…and here’s the join table in browse mode.

The join table is where the magic happens, and you will note that…

• key_combo contains two document ids separated by a space
• key_combo facilitates the making and breaking of links
• key_mlk contains two document ids separated by a hard return (the “mlk” stands for “multiline key”)
• key_mlk is the foreign key that joins two document records
• in the serial version of the demo, id numbers correspond to document names — though you are welcome to edit document names of course
• the order of the ids in the “key” fields is not accidental — they are always in ascending order (this holds true for the UUID version as well)

So let’s say you’re sitting on Document 4 and contemplating linking it to Document 5…

…or perhaps you’re sitting on Document 5 (which currently is not linked to any other doc) and would like to link it to Document 4…

Either way, you’re going to click the “link” button in this portal…

…which is attached to the doc_doc table occurrence…

…this script will run…

…and a new record will be created in the join table with the 4 preceding the 5.

What happens when you decide to break a link?

This script runs…

…and when you reach this point, you GTRR to the indicated TO in a new window.

Why a new window as opposed to simply changing layouts and then returning to the original layout? In this demo there’s no compelling reason, but in a real-world scenario it sidesteps the possibility of disturbing the user’s starting context in any way… e.g., blowing out their current tab/slide panels (FileMaker doesn’t save/restore them automatically when you leave and then return to a layout — yes, you can program around this limitation, but that is beyond the scope of today’s article).

The precautionary “if” test at step 15 will, in theory, always evaluate as true, but a ) requires very little effort to implement, and b ) will prevent the unintentional deletion of a record in the Documents table if, somehow, the “join” layout is ever accidentally deleted due to developer error or some other unanticipated mishap occurs.

And now the join record has been deleted and docs 4 and 5 are no longer linked.

Finally, let’s wind up by looking at the hide logic for the “link” button. As you may recall, the button should not appear…

• for the current document
• when a doc is already linked to the current doc

…and this is accomplished via this Hide Object calculation…

…with help from this table occurrence.

And I think that’s about enough for today.

[Update 24 Oct 2017: see Reciprocal Linkage, part 2 for additional optimizations and refinements to this technique.]