Join table inverse relationships

[eJK]

Hi,

I'm working on a knowledge base / help system. It contains articles. I'd like to create links from one article to another as related articles, which show up in a portal.

Right now I'm doing this with a Join table that contains a pair of ArticleNumbers, one for each of the related articles. This relates to a second Article table occurence. So I can easily link Article 2 to Article 3, which is set up as an entry in the Join table.

The problem is this: I'd like the inverse links to automatically get set up, i.e. if article 2 links to article 3, article 3 should also link to article 2. Right now, they are just one-directional. Do I have to have pairs of links in my join table, one for each direction? Or is there a more elegant way to do this that I'm missing?

I'm sure this has been addressed somewhere else, but couldn't figure out good search terms for it, so a link to another thread would be great if you know of one.

Thanks for the help.

[Ender]

Hi eJK,

I have attempted this puzzling problem in the past, and I'm pretty sure that you will need to create two join records: one for each direction. This can be scripted, and if you use a script to create both, then it's pretty problem free. You can check out my demo in this thread on a similar subject:

http://fmforums.com/forum/showtopic.php?tid/135019/

[eJK]

Thanks Ender,

I knew some of you experts out there would have encountered this situation.

As you realize, the problem with scripting is that it takes away the ability to create a new relationship from directly inside the portal. Also, deleting one relationship doesn't delete the second, so deleting them would also have to be scripted somehow.

Guess I'll just have to live with it. Thanks for the feedback!

[SlimJim]

This is an interesting theoretical problem. You are creating a symmetric relationship between a table and itself and hence to define the relationship you do not need the full join table of ordered pairs of IDs. The join is sufficiently defined by unordered pairs (sets of two IDs).

To put this into English do not use a pair of IDfields in the join table use only one IDfield and drop the pair of related ID's into it separated by a carriage return. Use this as the match field link between the two occurrences of the table.

One more thing you have to decide: do you want to relate an article to itself in this scenario. If you do then begin the join field IDs by copying all the ID's of all the records in the table.

EDIT: Sorry ejK I missed you second post while writing this. I think your two points are answered by using a single ID field.

FURTHER EDIT: I should make sure that you have read the first sentence. This is a theoretical solution. I have not at any time tried to do this and haven't really thought about it before this post. So no guarantees.

Edited November 11, 2005 by Guest

[comment]

A simple solution would be to show 2 portals - one showing articles this article is pointing to, another showing articles pointing to this articles.

There could be a couple of ways to group both kinds together. Much depends on what this is going to be used for. It could be as simple as creating a concatenated field in the join table (= ArticleNumber1 & ¶ & ArticleNumber2), then filtering out the current record. Or you could use a relationship based on value lists, as shown in the attached file.

LinkRecords.fp7.zip

[Ender]

Hey Slim,

Your theory sounds like a multi-key. And while I'm a big fan of multi-keys, I don't think they would work well here. The trouble is you should be able to create a join from any record to another and also remove a join from either record. I don't see an elegant way to do this with multi-keys. If I've missed something, perhaps you can try to get a sample to work and share it.

[eJK]

Hmmm, SlimJim's concept is interesting... it kinda sorta works.

I set up an additional Calculation field in the Join table, that concatenates the two Article numbers with a Paragraph character in between. This works for pulling in the link to the portal for each records.

The new problem is this: I can't find a way to determine which of the two to display in the portal. Or rather, I always display one of the two, which is the same one for each record. In other words, if 2 is linked to 3, in 2 the linked record shows up as 3, and in 3 the linked record also shows up as 3.

So this is going to take some extra finessing. I'm trying to figure out a way that the related record can have a calculated value based on the record it's related to. Will keep you all updated if I figure something out.

[Ender]

Hi comment,

Looks pretty good. Although the process seems a little awkward to add records through a different portal than where the All appear.

[comment]

Maybe. It's more of an interface issue, and I have no information on workflow, so it could be solved in any number of ways. In a way, it makes sense to me that you cannot enter links TO this record - since this information is not available on this record.

I must say I am quite puzzled regarding the need for this: I believe an article should list its own sources, and COULD (in a way of boasting) mention other articles where this article was sourced. These two seem quite separate to me.

Edited November 12, 2005 by Guest

[SlimJim]

Ender

Yes it is a multi-key and as I said a theoretical solution. I don't have your experience at knowing what is and is not possible. Be that as it may I felt obliged to have a go at implementing it. Adding keys is simple enough (but I haven't implemented any checks for uniqueness), deleting keys is also simple but choosing the right one is not so simple!! As eJK as pointed out displaying is a problem also. It can be done to some extent. I am posting my effort but this is not to be regarded as a solution and it is definitely not pretty.

To move on and following on comments last post. I think another possible approach to the problem might be to use categories (and sub-categories etc if you wish). This has the advantage that data entry classifying an article can be done without a view of all the other articles. When you want to see related articles you use a simple self-join by category portal which requires no join table.

SymmetricSelfJoin.zip

[comment]

It could be as simple as creating a concatenated field in the join table (= ArticleNumber1 & ¶ & ArticleNumber2), then filtering out the current record.

Your file does the one, but not the other: see, for instance, Article 8.

[SlimJim]

Yes I have since looked at your posted file and made some alterations to enable the filtering. Refreshing seems to be a major issue. I decided to use an auto-enter for the link list, since both creating and deleting joins is scripted I could force a refresh, But refreshing after a delete didn't work. The script shows what I had to do to force a refresh. I feel that this should not be necessary.

OK. Here is the new version - it works somewhat better as a result of amalgaating comments ideas but the interface is still very sparse.

SymmetricSelfJoin.zip

[comment]

Refreshing is always an issue when a relationship is based on a key derived from another relationship. I find that using unstored calcs and Refresh Window [Flush cache..] works adequately - and the refresh can be incorporated into navigation.

[SlimJim]

Refreshing is always an issue when a relationship is based on a key derived from another relationship. I find that using unstored calcs and Refresh Window [Flush cache..] works adequately - and the refresh can be incorporated into navigation.

Right as usual. I have changed the linked field to an unstored calculation, Thrown some scripted refreshing into the create and delete join entries and that seems to be working fine. With a reasonable number of articles the list of unlinked articles is goin to get unmanageably long so I have thrown in an extra Category filter on the unlinked portal, and finally chucked in a bit of navigation.

I have left the refresh button in but I have a feeling that it is no longer necessary. I have bored you enough with this. As I said at the beginning it was a theoretical solution but I do think that this at least shows that it is possible to produce a symmetric self-join using only two TOs - the table and the join table. The remaining TOs are to do with implementation of the data entry not with the underlying structure.

SymmetricSelfJoin.zip

[eJK]

Great work guys! Got it running, works just as advertised. Can't add relationships through the portal, but that's well worth the functionality. Thanks a lot for the examples.

[T-Square]

I am jumping in at the tail end of this discussion because my primary educational and professional background is as a librarian and information scientist.

What you all are discussing is creating a syndetic structure--a set of links that join different concepts in a knowledge base. Basically, there 3 types of link in such a structure: Broader terms (BT), Narrower terms (NT), and Related terms (RT). The BT/NT links are two sides of the same link, while a related term is, as SlimJim says, symmetric.

Comment's solution implies a BT/NT relationship, since each portal shows one side of the relationship. That doesn't sound like eJK's situation.

It would seem to me that the metaphysically cleanest approach would be to script creating reciprocal RT link records (ID1/ID2 ID2/ID1) in the join file, and identify that the link is type RT. Then, you can also use this file to store BT/NT entries.

Cheers,

David

[Ender]

I'm not familiar with your terms, but you're right that there are two basic options here; Either one join record, which has no directionality implied, or two join records, where each can remember different information about the relationship in each direction. On the referred thread in my original response, it was important to remember different information in each side of relationship. It sounds like this is the case for your metaphysical thing too. It's still not clear to me which structure eJK needed (or used.)

[eJK]

I wanted a aymmetrical, one-to-one relationship, or what T-Square calls an RT link.

I agree one solution is to use double (inverse) entries in the link table, but then you have to deal with deleting (and creating) both of these entries, which didn't seem as straight forward as just listing the entries once. Which is what I got in the end, thanks to you're guys' help.

I like the cool little trick of using the Value List of related records from the Links Table to fill in the Links Field in the Articles Table. Very nice, never thought of that. Everything is working great now. Thanks again for all the feedback.

[SlimJim]

I haven't come across the notion of a syndetic structure so naturally I did a Google search and found less than 13,000 hits and the number of hits didn't change by removing ebay etc. So it would appear that you can't buy a syndetic structure on eBay! Many of the articles seem rather technical or somewhat vague and at this point something in the middle would be interesting.

I suspect like most people interested in computing I have my own knowledge base of articles (my classification is rather primitive with a simple three levels of categories) Would a syndetic structure help here? Is there any simple implementation of it which would make it easier to hone down onto appropriate articles, or are all the methods available more suitable for the British Library or Library of Congress.

[T-Square]

eJK--

If you script the creation of the reciprocal records, then removal *should* be manageable using relationship definitions--specifically, if you create relationships between:

1) ArticleID and JoinIDA, and

2) ArticleID and JoinIDB

and set each to delete the linked records in JoinTable when a record in Articles is deleted, you have no further delete maintenance.

SlimJim: take a look at the following:

http://instruct.uwo.ca/gplis/677/thesaur/main00.htm - for an overview of thesaurus construction, and

http://www.archivists.org/glossary/term_details.asp?DefinitionKey=1165 - for a definition of syndetic structure as I am using it here.

With regard to small knowledge collections, the best solution is your own brain. The main reason to use a classification scheme is that your collection is so big that you don't have any way to figure out what's in it (or where it is). You might check out the following for a discussion of the issues:

http://www.bayside-indexing.com/Milstead/about.htm

[comment]

I tend to see this more of a FileMaker issue than an IT issue. In FileMaker (v.7 and up), all links are already bi-directional. It makes very little difference whether the linking pair is ordered or not.

Adhering to a strictly symmetrical model, as you describe, will only bring you back to the problem as presented by eJK: how do you show all links in a single portal, and avoid showing the current article there as well?

[SurferNate]

I'm maybe a bit clueless here, but with the relationship, why not simply add a condition where primarykey<>primarykey. This would exclude the current record from the portal no matter what record you are viewing while all other related records would show up nicely.

I am reading into this as a simple problem where each record has a multikey to relate it to other records in the same source table. It could even be heirarchical with no trouble. As long as the calculation for the multikey does not become infinitely circular (ie. the first record in the trail cannot be dependent on the last).

Smack me if I'm wrong.

[SurferNate]

okay, I read it again and see that I am clueless. however, removing a relationship in a multikey can still be scripted using Substitute().

[BevvyB]

Does the link to SlimJim's last file example still work? When I try to download it for some reason it downloads with .html at the end of the file type.

 

Really want to see this as I need a solution along similar lines.

 

Thanks

 

Julian

[comment]

Please report the issue here:

[Lee Smith]

These files are over 10 years old, made up in response to a question using version 8. I’m not how relevant or helpful they would be to a question in version 12?

I recommend that you start your own thread and explain what you are trying to accomplish.

Lee

Edited June 16, 2015 by Lee Smith

[comment]

I recommend that you start your own thread and explain what you are trying to accomplish.

​@BevvyB did start his own thread. He was directed to this one by me.

 

These files are over 10 years old, made up in response to a question using version 8. I’m not how relevant or helpful they would be to a question in version 12?

This is about relationships. Nothing has changed since version 7.

[Lee Smith]

What has changed is that the attachments are not available currently.

[comment]

What has changed is that the attachments are not available currently.

​Yes, i am painfully aware of that. So what is your point here? Are you suggesting I should not refer to previous threads?