Relationship between a table and itself

There is no barrier in Ninox preventing the creation of multiple relations between the same set of tables, or even between a table and itself. While it is always a good idea to rename the reference fields to give them names that indicates their purpose, it is especially important when several relations exist between the same set of tables.
Suppose, for example, that the "RELATIONSHIP" table is used to record parents-children relations. When creating the first N:1 reference in "RELATIONSHIP" to "PERSON", the reference field will be named, by default "PERSON". If you create a second one, it will be named, by default, "PERSON2". So, which is which?
I would thus suggest, after creating the first reference in "RELATIONSHIP", to immediatly rename the reference field "Parent". Then open the "PERSON" table and rename the newly created 1:N reference field, named by default "RELATIONSHIP", "Children". Open the "RELATIONSHIP" table again, create the second N:1 reference to "PERSON", and immediatly the name of he reference field from "PERSON" to "Child". And finally open the "PERSON" table and rename the newly created 1:N reference field, named by default "RELATIONSHIP" again, "Parents". Of course, other names should be chosen if another kind of relation is being represented.

I'm struggling at this same point. I can create the simple relationship above. How do I expand it? I need to know parent-child, husband-wife, uncle-nephew, etc. By trying to duplicae the above with the PERSON and RELATIONSHIP tables I'm getting loops and linking is going haywire.

Hi all. First of all it's not quite as simple as it may seem. It is a many-to many join with Person - Relationship - Person so you may have some data that looks like this

Peter - Father/Son - David

David - Brother/Sister - Jane

The trouble is, when you want to see all the relatives of David, his father is on the left but his sister is on the right and so you can't have a single view showing both. It might be better to have two joins for each

Peter - Son - David

David - Father - Peter

David - Sister - Jane

Jane - Brother - David

Now we can have a single view showing both relatives of David. So now the question is, how do we create and maintain this and I would suggest that maybe a version of the first model should generate the second.

Now to model it!

Regards John

So this works. The fields were hard to name, so sorry if they don't make immediate sense.

I have added a reference to link the pairs in the Relationship table. That way when one is updated the button will create or update the other. Set Composition to Yes and deleting one will delete the other as well.

Regards John

Many thanks for your response. I don't really understand what the code is doing. Nor which relationship is composite, but thank you. I hadn't realised that this would be so problematic. I am not a coder so this is beyond my skillset.

Having made one relationship

Peter - Son - David

and noting the that reverse relationship is Father (the Reverse field) the button is creating or updating the second

David - Father - Peter

Lines 1 - 6 copies the data to variables

Line 6 checks to see if the other half of the pair has been created

Line 7 creates the other half

Lines 9 - 12 copies the data from the variables

Lines 13 and 15 pair up the two records so one can always be found from the other

Line 16 from here on, a pair already esists

Line 17 finds the other record of the pair

Lines 19 - 22 updates the data from the variables

Regards John

Thank you