Relationships between records in the same table

[MauriceD]

Good afternoon everyone,

I have a table containing records of people and companies. Between the records in that same table there are all kinds of ‘connections’.

Examples: John ‘is husband of’ Sue. Sue ‘is wife of’ John. Andrew ‘is owner of’ Company A. Company A ‘is owned by’ Andrew. And so on.

The list of possible connections is quite long. Consider family ties (brother, sister, mother, father, aunt, nephew, etc.). But also professional ties (employs…; is employed by…; buys services from…; is gardiner for…, etc.).

Every connection has 2 descriptions. One from the point of view from the first record (is father of) and one from the point of view of the second record (is son of). I have created a table where both primary keys of ‘father’ and ’son’ are recorded, as well as both the description of the connection from both point of views.

What I want to achieve is the following. When looking at the record of the father, I want to have a portal showing the record of the son with description ‘is father of’. Alternatively, when looking at the record of the son, I want to have the portal showing the record of the father with description ‘is son of’.

How can I best achieve this? Thank you beforehand.

Sunny regards,
Maurice

[Torsten]

You have everything in place. Create a layout based on ‘Table copy 1’ (father). Place a portal based on ‘Linktable’ on the layout and populate the portal with fields from ‘Linktable’ (relation type) and ‘Table copy 2’ (entities related to ‘father’. This should too. The portal can be filtered i.g. to show only one relation type i.e. ‘is son of’.

[anon45965781]

I always start with getting the right data design and SQL to make sure I have the query right so it would work in any environment. Getting the right data design is key (no pun intended) and can really take the longest of all of it.

First, I created a table for this self-join with the self-referential key (RELATED_TO)

This is the entire table:

Then, I wrote the query to get this result and verify my table design:

–
By getting the query working first (outside of FMP), I made sure my data relationship was what I wanted (in this case, how I interpreted your posting) and that I could get the data I wanted from it.

HTH

[steve_ssh]

If I understand the above replies correctly:

@anon45965781 appears to be working with a model where the two descriptive relations between two distinct people are stored in two separate join records.

I am not, as certain about @Torsten, but I think it possible that the same may be true.

However:

When I look at the diagram in @MauriceD’s post, it suggests to me that both descriptions of the relation are stored in a single join record.

It might help to:

1. Clarify whether one or two records are used to record both descriptions for each relation.

2. Regardless of the answer to #1 above, consider the pros and cons of both approaches.

I personally feel that either approach could work, but often, UI display needs, and reporting needs may dictate why one approach is favorable over the other.

Further:

In the case where both descriptions are stored in a single record, you would likely be using a portal (as already described by @Torsten), but instead of applying a filter on the records, it is more likely that you will employ one of a few possible techniques to conditionally display the appropriate description. Many years ago, a FMP dev might have employed an unstored calculation for such a task. Since those times, however, techniques utilizing conditional hiding, or calculating display text via a button bar layout object (or prior to that, using calculated placeholder text) have become possible, and for many of us, these new options are preferable to the old-school** approach of using an unstored calc.

In the case where each of the two descriptions occupies its own separate record in the join table, then @Torsten has already given you a great description of how to approach this. The only thing that I might add to this would be using a filter on the portal may not even be necessary – a simple relationship could drive the portal, provided that the description stored in the join table is always associated with the key consistently stored in the same field in the join table.

Lastly:

I hope that I have not read too much into this and made it more confusing than necessary. It could be that I’ve second-guessed everyone wrong – I hope not.

Kind regards & HTH

-steve

**By “old-school” I am thinking back to my v.4 FMP days, though I suppose some of the techniques I used back then might have even lasted through my v.11 days – I can not quite recall.

[anon45965781]

Hey Steve,

My single-table data design based on my understanding of what the OP was asking was actually a single record where each record could point to one other record. You could have multiple records if say, “Bill”, had multiple relationships as he does in my screenshots above.

Same basic single-table idea could work, say, “Employees”, and “Departments”, though I would almost certainly opt for a 1:M relationship or M:M instead of a recursive single-table relationship like the one above unless it was absolutely necessary.

I may have misunderstood your posting above, but wanted to clarify anyway.

Thanks,

[steve_ssh]

Hey @anon45965781

Thanks for the clarification.

Yes, I believe that I may have misunderstood your intention/design.

Thank you.

-steve

EDIT:

And, I should add, that this expands the list of models to choose from to at least three different options…

[MauriceD]

‘Evening all,

I’m getting close to the solution of my question, but not quite yet.

Ok, my layout is based on Table copy1. The portal on that layout is based on the linktable.

From the viewpoint of the ‘father’ it works. Sons, daughters, employees etc. all show up in the portal. Great!

But now from the viewpoint of the records of the son, daughters, employees the father does not show. The portal remains empty.

Torsten suggested to filter the portal. Could you please explain how?

Thanks again,
Maurice

[anon45965781]

Did you post your code and data design?

How close is what your doing to the example I posted above?

Please export your table data or some non-private representative sample.

(After verifying my data and SQL) Here’s what I did in FMP:

Then, this layout:

–

The data I imported is this:

ID,NAME,RELATIONSHIP,RELATED_TO
1,Fred,Is Son of ,2
2,Alan,Is father of,1
3,Peter,Is a Friend of,1
4,Susan,Is a sister of,3
5,Bill,Is a Brother of,3
6,Bill,Is a Friend of ,2

[MauriceD]

Hello Fmpdude,

Thank you for your effort. I attach a simplified version of my file.
If you care to have a look at the data, I explain what I like to achieve. Let’s take the record of Patrick Vandervelden (pk 110). He is the husband of Dominique Vandervelden (pk 130), and he also employs Asiz Chana (pk 140). This all shows up in the portal. Sofar so good.

Now I click on the record of Dominique Vandervelden in the masterportal. Although her record is linked to that of Patrick, Patrick does not show up in the portal of related records as ‘is husband of’. The same goed when the record of Asiz Chana is clicked.

When I click on a record in the masterdetail portal on the left, I’d like all the relations this record has in the linktable to show up in the appropiate portal.

Kind regards,
Maurice
RelationSameTable.fmp12 (244 KB)

[steve_ssh]

Hello @MauriceD,

Thank you for uploading the sample file. It helps clarify things a lot.

Attached to this post should be an archive with three variations to your sample file.

RelationSameTableVariations.zip (216.9 KB)

Please feel free to ask questions about them.

1. The first variation on your file takes the example as you posted it, and makes the portal display all relationships for each party. Though it “works”, I do not favor this approach, because it required some small amount of complexity in order to have the portal show what I wanted. I feel that this extra work is indicative of extra work that may also occur elsewhere in the solution, as more needs for displaying the data arise.

2. The second variation of the file reworks the data into a structure which only stores one description per link record (not two, as in the original file). This means that twice as many link records are necessary to store all of the relationships, but it also more easily affords granularity when working with the data. I prefer this storage structure because, when it comes time to build user interfaces and reports, I believe that it allows me to build with greater simplicity. Note how in this file it is easy to render the desired portal on the Parties layout without the need of the multi-key field added to the link table, or conditional hiding (both used to make the first variation of the file work).

3. The third variation of the file is an update to variation #2, which factors the descriptive text out of the link table, and into its own separate table. This leaves the link table storing nothing more than a few foreign keys in each record. I like this approach the best, though I would understand if there were cases where another developer might not choose to take this normalization step.

I hope these examples help. Others may have alternate, and even possibly better, solutions to offer.

Please feel free to ask questions about how any of these work, or other possible variations.

Also note that none of the examples that I posted cover the concept that @anon45965781 has shared with you (above) – you have yet another option to consider, there.

Kind regards,

-steve

[Torsten]

From experience with a custom CRM solution I confirm that variation 3 provides good granularity with one descriptive text, as described by @steve_ssh. Easy to integrate in other parts of a solution.

[steve_ssh]

Thank you for chiming in, @Torsten.

I was hoping there could be a handful of opinions shared here.

Since starting with a good structure is so important, having a few developer voices on this is really valuable.

[MauriceD]

@steve_ssh

RelationSameTableVariations.zip

Goodmorning Steve,

Thank you very much for reworking my example file. Solution nr. 3 is indeed the best way to approach this. It doubles the number of records, but as it’s only 4 fields it wil nog weigh much. Question while on this table: we could even do without the primary key as it plays no part whatsoever in finding or indexing, correct? Any searching would only be done on the foreign keys.

What I meant to do from the beginning is to automatically assign the ‘counterpart’ of a relationship (hence the 2 description fields in the linktable in my original file). We could argue about ambivalence and use ’is spouse of’ instead of breaking down into ‘is husband of’ and ‘is wife of’. We could also use ‘has a family tie’ instead of breaking it down into all sorts of complicated family relationships like ‘is brother/sister/nephew/aunt/great-grandson of', etc. I shall need to rethink the necessity of this complexity in my solution, but let’s keep it in place for argument sake.

Apart from the really complicated automated situations (the party is a male and therefor can only be ‘is brother of - and all other male roles), let’s consider relations in the professional environment. I’d like a type of relationship described for party1 to be reflected automatically in party2. Examples: when José employs Jordi, I’d like the Jordi record to show ‘is employed by’ José.

What would need to happen is when creating a record in the Linktable with

link_party_fk1 = 150 (José) and
link_party_fk2 = 180 (Jordi) and
link_relationship_fk = 5 (is employer of)

another record in the Linktable is to be created with the ‘counterpart values’:

link_party_fk1 = 180 (Jordi) and
link_party_fk2 = 150 (José) and
link_relationship_fk = 7 (is employed by)

Is this the correct way of thinking? And if so, how could I achieve this? Switching the party’s primary keys could not be too difficult (although I would not know how to do this at the moment), but how could we determine the correct relationship ('is employer of' vs. 'is employed by’)?

Kind regards,

Maurice

[anon45965781]

Nice job, Steve! Lots of good ideas.

[steve_ssh]

Hello @MauriceD,

Some comments, thoughts, and opinions are below. I hope that they may be helpful.

As always, feel free to ask questions. Also as always, @everyone, feel free to chime in with additional thoughts, better advice, etc.

Question while on this table: we could even do without the primary key as it plays no part whatsoever in finding or indexing, correct? Any searching would only be done on the foreign keys.

While I agree with your assessment that there currently is nothing that would break if the primary key were removed in the link table, I would maintain a primary key in the table based on the following personal considerations:

1. With the possible exception of perhaps a FMP "globals" table, having a primary key in every table is a convention that I, personally, find difficult to break.

2. Though I applaud developers thinking about overhead consequences in all that they do, in every system where I have been up against a performance problem, removing a primary key from a table would not have appreciably helped -- there have always been much more glaring and important-to-address causes for poor performance.

3. I can imagine scenarios where having a primary key in the table would be useful.

What I meant to do from the beginning is to automatically assign the ‘counterpart’ of a relationship (hence the 2 description fields in the linktable in my original file). We could argue about ambivalence and use ’is spouse of’ instead of breaking down into ‘is husband of’ and ‘is wife of’. We could also use ‘has a family tie’ instead of breaking it down into all sorts of complicated family relationships like ‘is brother/sister/nephew/aunt/great-grandson of’, etc. I shall need to rethink the necessity of this complexity in my solution, but let’s keep it in place for argument sake.

Apart from the really complicated automated situations (the party is a male and therefor can only be ‘is brother of - and all other male roles), let’s consider relations in the professional environment. I’d like a type of relationship described for party1 to be reflected automatically in party2. Examples: when José employs Jordi, I’d like the Jordi record to show ‘is employed by’ José.

Great. The above two blocks make plenty of sense to me.

What would need to happen is when creating a record in the Linktable with

link_party_fk1 = 150 (José) and
link_party_fk2 = 180 (Jordi) and
link_relationship_fk = 5 (is employer of)

another record in the Linktable is to be created with the ‘counterpart values’:

link_party_fk1 = 180 (Jordi) and
link_party_fk2 = 150 (José) and
link_relationship_fk = 7 (is employed by)

Is this the correct way of thinking?

An excellent concrete example, and I agree with it entirely.

And if so, how could I achieve this? Switching the party’s primary keys could not be too difficult (although I would not know how to do this at the moment), but how could we determine the correct relationship (‘is employer of’ vs. 'is employed by’)?

Short answer: This would all be handled with scripting.

Elaboration:

In my own work, it's rare that I allow a user to directly create a new record. Instead, there is almost always a script that the user invokes which uses FMP scripting to create the desired record. Such a script not only creates the new record, it also ensures that any necessary related business logic is performed.

It is in such a script (or subscript of) where I would create both needed records, i.e. the record which represents the original relationship that the user (or some other system) has requested be stored, as well as the record that represents the same pairing of parties, but with the "inverse" relationship association.

At this point, I'll start by assuming the following, and, of course, feel free to correct me if I am mistaken:

1. You are ok with the idea of using a script to create the records.

2. You have some sort of mechanism in place whereby such a script can be supplied two Party primary keys, and and one of the Relationship primary keys.

3. Scripting the creation of the two records in the Link table is within your FMP skill set.

4. The only overtly remaining puzzle piece is to determine the second required Relationship primary key, based on the three supplied pieces of information which your script already does have available to it.

Given the above, I see two possible scenarios to consider:

1. Every Relationship is in a 1-1 relation with its inverse, for example:

   • Parent of <-> Child of
   • Sibling of <-> Sibling of
   • Is employed by <-> Employs
   • Grandparent of <-> Grandchild of

2. The above is not true, and some Relationships have multiple inverse descriptions, e.g.

   "Grandmother of" has multiple possible inverses: "Granddaughter of" and "Grandson of"

How I'd handle each of the above:

Case 1: Each Relationship has exactly one inverse, and we expect that this will always be the case.

In this case, I'd populate an additional field in the Relationship table, which stores the primary key of the related inverse Relationship record.

The Relationship table fields would look something like this:

• pk
• descriptive_text
• fk_inverse_relationship

Then, during the execution of the script that creates the two records in the Link table, given one of the Relationship keys, it is easy for us to consult the record in the Relationship table to determine the key that we need to create the second of the two Link records.

Case 2: It is possible for a Relationship to have multiple possible inverse Relationships.

In this case, I'd consider adding a table to store the relations between Relationship records.

(This would be a separate table beyond the Relationship table that we already have.)

The fields would probably look something like this:

• pk
• fk_Relationship_target
• fk_Relationship_inverse

Some sample data might look as follows:

Relationship table (pk, description):

1, "Employs"
2, "Is employed by"
3, "Is Grandmother of"
4, "Is Grandfather of"
5, "Is Granddaughter of"
6, "Is Grandson of"
7, "Is Brother of"
8, "Is Sister of"
9, "Is Cousin of"

InverseRelations table (pk, fk_target, fk_inverse):

1, 1, 2
2, 2, 1
3, 3, 5
4, 3, 6
5, 4, 5
6, 4, 6
7, 5, 3
8, 5, 4
9, 6, 3
10, 6, 4
11, 7, 7
12, 7, 8
13, 8, 8
14, 8, 7
15, 9, 9

Give the above two tables:

When our record creation script runs, it will have the wherewithal to determine a list of possible primary keys which would be appropriate for use when creating the Link record that stores the "inverse" Relationship record. The final selection of primary key to use might involve human intervention, or, in some cases it could possibly be determined programmatically based on other known information, e.g. the storage of gender data (as I believe you already mentioned).

Additional comments:

1. I've sort of sketched out how I might approach this sort of solution, but as I'll often acknowledge: these are not the only possible solutions -- they are just avenues that I'd consider first, knowing few of the other considerations that are part of the bigger picture of your solution.

   As an example: The choice to store inverse relation data in a table is a pretty traditional approach, but it is not the only possible solution -- certainly lists of possible inverse keys could be stored in a single field within a single Relationship record. Doing so might make sense in some situations. In (many/most) other situations, I'd prefer to split such information out as rows in a table (as shown above).

   And again: My acknowledgement that there are other ways should be taken as an open invitation for anyone else to chime in with experience, thoughts, better solutions, etc..

2. I haven't included any details about how to look up data from a table (as would be needed to implement the scripting that I've touched upon). I only mentioned that it should be easy to do, and left the details to you to consider. If some elaboration would be helpful there, please let us know. I neither wish to post something which underestimates nor overestimates your experience with FMP and its world of scripting, context, relationships, getting at related data, etc..

3. I have not touched upon the topic of how I might proceed if I were unsure of which of the two above cases applies (regarding whether inverses are 1-1 or possibly 1-Many). I have a few thoughts about this, but am withholding them for now, as they may not be relevant or helpful at this time. I'll be happy to follow up and share them, if that might be helpful.

I think that's all for now. As always, I hope this is helpful.

Kind regards,

-steve

[Cecile]

@steve_ssh

I can imagine scenarios where having a primary key in the table would be useful.

I concur. In the starter solution « Estimates », they didn’t use a primary key for items. When there are a limited number of items to pick in a drop down it works fine. However, when my client entered its extensive product list, this selection method wasn’t practical even more so when he asked for the system to automatically put the bulk price if quantity was over a certain amount. At this point i ended up having to rebuild the relationship graph and related scripts because the match field on item name was to restrictive. I prefer strategies that can allow for even the most unlikely unforeseen growth

[Torsten]

@steve_ssh, you covered the topic, nothing to add. Your statements and comments can adequately be described as best practise.

[harvest]

@steve_ssh

Is employed by <-> Employs

Hi Steve,
great post, one thing I would add when starting to build relationships that are not only family related, are dates: isValidFrom, isValidTo to build a history in e.g. employment...
Holger

[MauriceD]

Good morning Steve,

Thank you again for a continuing and very insightful followup to my questions. It’s highly appreciated.

About leaving out a primary key.
It would go against all instructions and advise I received from kind teachers like yourself. On the other hand I was also taught to always design tables as lean as possible, although this comes from an era where storage capacity and network traffic intensity were issues to be considered But your point is taken: I’ll leave in the pk.

About scripting.
I agree that actions like creating, editing and deleting records should never be under direct control of users. At the moment I am the only one developing an using this solution. But due to increasing business, I need to make the solution available to my team. This means I need to foresee every possible contingent (if that is at all possible) and to create scripts to handle data altering occurrences.

To react to your assumptions.

1. I am more than ok with using scripts, although my understanding is basic. I can voice what I like the solution to do, but do not have the skills (yet) to translate this into scripts (see point3).
2. My pk’s are issued on the basis of using the auto-enter options.
3. I must sadly admit that scripting the creation of 2 records in the link table is not in my FMP skillset.

Anyway, this is my first reaction to your extensive explanation. I shall revert to the rest of the message later as I need to carefully read it and it is a lot to take in. Thank you again.

Kind regards,
Maurice

[steve_ssh]

Hello @MauriceD,

Just a few comments this time...

On the other hand I was also taught to always design tables as lean as possible, although this comes from an era where storage capacity and network traffic intensity were issues to be considered...

Designing lean tables is still very much a great practice, and I wholeheartedly encourage you embrace that ethic. The most common bottlenecks that I see in FMP systems come from design which led to situations where too much data must be pulled across the network too frequently. So -- your thinking and consideration of network is great. But, I am, admittedly, drawing a line when it comes to the pk.

I must sadly admit that scripting the creation of 2 records in the link table is not in my FMP skillset.

My hunch is that you will be a quick learner, and the scripting that you need to learn will come to you easily enough. There will be more to learn than just scripting, of course, but I predict that you have some really fulfilling learning ahead of you. And -- I assert that you have one of the most generous developer communities available to you to help you as you learn.

With respect to what topics you prioritize in your studies, one opinion/suggestion:

Prioritize developing a solid understanding of how "Context" works in FileMaker. It underlies nearly everything you do in FileMaker, and a solid understanding the hows and whys of what data is available from a given context will make everything you do more clear. (As an example: The answer to the original post about how to get the desired rows showing in the portal has its roots in how context works.)

Kind regards,

-steve