One to many problem

[Chris Pink]

I have a table  parent and a table  child. And a join table child to parent because  one child can have two parents,  one parent can have many children

on the Layout using table parent I would like to display the other parent (if it exists).

For the life of me I can't work out how?

 

 

[comment]

First, a child cannot have two parents; that's against the definition of a parent-child (one-to-many) relationship. What you describe is a many-to-many relationship.

Next, it is not clear what you mean by "the other parent". If one 'parent' can have many children, and each child can have two 'parents`, then each 'parent' can have multiple co-parents - a different one for each child.

One possible method to show the co-parents is to define a calculation field in the child table that lists the IDs of the childs 'parents'. Then add another calculation field in the 'parent' table to aggregate these lists and use the result as the match field to another TO of the 'parent' table. You can filter out the current record either by substituting it out in the calculation or by filtering the portal.

Another option is to place another TO of the join table "behind" the child table and attach another TO of the 'parent' table to it. Then use a portal to this TO.

 

[Chris CBS]

Thank you. Just one note before I digest your answer  - and also the reason it was difficult to Google this issue. They are real parents and children 😉 I was trying to avoid confusion that in the real life scenario a child can have more than two 'parents' (carers) and each of these entities have their own database record.

18 minutes ago, comment said:

First, a child cannot have two parents; that's against the definition of a parent-child (one-to-many) relationship. What you describe is a many-to-many relationship.

 

Newbie alert.

Incidentally, what's a TO?

Edited October 28, 2020 by Chris CBS

[comment]

10 minutes ago, Chris CBS said:

They are real parents and children

Then perhaps you should consider a different data model:

Families -< Individuals

with a Role field in the Individuals table.

This is assuming each individual is a member of one family only - otherwise it would have to be:

Families -< Roles >- Individuals

 

Edited October 28, 2020 by comment

[Chris CBS]

1 hour ago, comment said:

Then perhaps you should consider a different data model:

Families -< Individuals

with a Role field in the Individuals table.

They are, conceptually, totally different entities (in the way that they interact with other elements in the database)

Filemaker's documentation example for many-to-many  is Student,  Class and Signup

in those terms it would, be on a Student's record, showing their Classmates which, as you are demonstrating is not as straightforward as it appears.

Programmatically: For this Student, show all Students who have a the same Classes. Doesn't sound complicated but how do I do it?

In my terms; For this Parent show all Parents who have the same Child(ren). 

For this Child show all Parents (on a Layout based on Child) : easy

For this Parent show all Children (on Layout based on Parent) easy

But to show all co-parents  (on Layout based on Parent) ?? Maybe a repeating field Parent::CoParent is the only way

 

Edited October 28, 2020 by Chris CBS

[comment]

1 hour ago, Chris CBS said:

They are, conceptually, totally different entities (in the way that they interact with other elements in the database)

I am afraid you lost me at this point.