Note: Leaner does not always mean faster, especially if record counts will be large… in which case I would recommend going with the approach discussed in part 2.
[3 Jan 2018: demo updated to version 3.2 as per Jens Rasmussen’s comment below.]
[2 Nov 2017: demo updated to version 3.1 as per my exchange with Jonathan Mickelson in the comments section below.]
I thought after part 2 that this technique had been thoroughly discussed and dissected, but Geoff Gerhard of Creative Solutions writes to point out that, if the goal is to have as few moving parts as possible, the table occurrence (TO) count can be further reduced, from four to three…
…and he has provided a demo file which I am sharing with his permission.
Demo file: reciprocal-linkage-GGv3.2.zip
On the main layout the Linked Documents portal, which was formerly attached to join_doc, is now pointing at a second instance of doc_doc, filtered like so:
Also, the Hide Object logic on the “make link” icon has been streamlined:
I asked Geoff about the above, and he replied, “I like the look/logic of a single test. I think the explanation that the hide condition applies to the portal’s instantiation of the current record PLUS any/all linked records clarifies the logic of this construction.”
Additional changes: the join_doc TO has been removed from the Relationships Graph, and the navigation logic on this button has been updated.
Love the series, and blog, as always!
PLATFORM: FM16 Mac
PROBLEM: I’m not getting the same behavior on the V3 file as the Ver 2 files.
If I go to Document 1 and link it to all other documents, and then navigate to the other documents the link to Document 1 does not display on them. Whereas, on the earlier v2 versions the reciprocal links did display for the linked document as well as the original doc I linked from.
Am I missing something?
Thank you and good catch Jonathan.
Looks like in this version we need to refresh the linked docs portal when navigating between records, so I added an OnRecordLoad script trigger to do just that — see updated demo file (https://kevinfrank.com/fmh/reciprocal-linkage-GGv3.1.zip).
Regards,
Kevin
Great update. But I experience an unwanted cascading delete! Deleting current record makes them all go away…
Thank you Jens. Demo file has been updated to v3.2 to remove the unwanted deletion dependency between doc and doc_doc.
I just used this technique to link people to each other, but the connection actually has data that I’d like to include in the portal, such as whether the connection is active and what type of relationship the people have.
I don’t see an obvious way to do this. I’m about to look into using `ExecuteSQL` to get the connection record. Have you done anything like this with the technique?
Thanks,
Chuck
Hi Chuck,
The purpose of this technique is merely to link two entities, not to describe the relation between them. I’ve done other systems where describing the relation was important, and then the question becomes can a single word describe the relation bi-directionally (e.g., “colleague”), or do we need separate words (e.g., “employer/employee”)?
Incidentally, I would probably go with the technique in part 2, as opposed to the one in this article, in terms of performance as record counts get larger. I don’t have any opinion re: SQL for this technique as I have not tested.
Regards,
Kevin
Kevin,
I’ll take a closer look at the second technique and see if it would work well. I went in another direction because I was spending so much time trying to figure it out. The portal now shows a standard relationship and the join table has a global field that is populated with OnRecordLoad and there’s a second relationship on the other side of the portal to the other record using that global field as a “not equals” predicate.
I’m not a huge fan of it because I don’t like script triggers unless there’s no other way, but it works for now. I’ll experiment with your much cleaner technique down the road when I have more time.
Thanks,
Chuck
Would it be possible to include a field to describe the relationship between two records in the same table? The solution is very good, but I am unable to add this field. Thank you very much for your work, it is very helpful for research.
Thanks,
Santiago
See my 22 May 2018 response to Chuck Ross.