Family Relation Inverter

[Ocean West]

Have a 3 tables Guardian, Child, and a join table.

In the join file there is a "relationship" filed.

The child and the parent also have a "gender" field.

I am trying to come up with a custom function that will calculate the inverse

family relationship depending on my context view or what was entered in to the relation field

Example...

From the Guardian record looking at a list of joined child

the relation in the portal should be to that of the guardian and the user

would type or select from the list (depending on gender)

Guardian: Mike Brady

Child: Greg Brady Relation: Son

Child: Peter Brady Relation: Son

Child: Bobby Brady Relation: Son

Child: Marsha Martin Relation: Step Daughter

Child: Jan Martin Relation: Step Daughter

Child: Cindy Martin Relation: Step Daughter

now for the inverse, the context of the child looking at the join portal

Child: Greg Brady

Guardian: Mike Brady Relation: Father

Guardian: Carol Brady Relation: Step Mother

Guardian: Alice Nelson Relation: Housekeeper

Or

Child: Cindy Martin

Guardian: Mike Brady Relation: Step Father

Guardian: Carol Brady Relation: Mother

Guardian: Alice Nelson Relation: Housekeeper

The relation here would be auto calculated. Or if the user enters this here the other

inverse on the other portal would be auto calculated.

I would consider as much of the family tree as possible. Uncles, aunts nieces, nephews, cousins, other relationships. (with "step" or "adopted" they could be prepended to the relation as that would be the same for each individual)

Thanks

[RalphL]

Don't you need to have 2 relationship fields? One field would be visibile to guardiann the other visible to child.

[Ocean West]

probably so. They would both be auto enter and recalculate based upon the entry in it's counterpart.

Depending on if you type in Father because the current child is a girl the relation for the child would be daughter.

on the other hand the inverse would be true if you typed in Daughter and the Guardian were Male it would translate to Father.

[Ender]

Hi Stephen, This sounds like a fun project.

I would think this could be broken down into two problems: 1. Setting the reverse database relationship (via auto entered calcs or something.) 2. Creating the reverse real-world relationship of the individuals.

Unless I'm missing somthing, the solution to the second problem could be a fairly straight forward case statement. Roughly:

relationParent =

case(genderParent = "Male";

case(relationChild = "Son" or relationChild = "Daughter";"Father";

relationChild = "Grandson" or relationChild = "Granddaughter";"Grandfather";

relationChild = "Stepson" or relationChild = "Stepdaughter";"Stepfather";

...

);//case relationChild

//otherwise genderParent = Female

case(relationChild = "Son" or relationChild = "Daughter";"Mother";

relationChild = "Grandson" or relationChild = "Granddaughter";"Grandmother";

relationChild = "Stepson" or relationChild = "Stepdaughter";"Stepmother";

...

)//case relationChild

)//case genderParent

In this, xxxChild and xxxParent refer to the database relationship from the perspective of the data entry portal.

You can add the other cases, like brother, sister, friend, and neighbor. I don't know what the reverse relationship for Housekeeper would be (Housekept?)

Regarding the first problem, I'm worried that it won't be possible to do this type of reverse database relationship creation from both perspectives (the Parent and the Child,) without the use of scripts. I attempted this structure a few months back for tracking nodes and links, and ended up using scripts (though this was back when FM7 was new to me, so I could have missed something.)

[Ender]

Unless I'm missing somthing, the solution to the second problem could be a fairly straight forward case statement.

I thought of an algorithm to do this that would handle most of the varieties of step-whatevers, great-great-grand-whatevers, and whatever-in-laws that might come up, without needing a zillion case statements.

I've got to sleep on it, but I may have something tomorrow.

[Ender]

Hi Stephen,

Enclosed is a custom function that returns the reverse relationship of individuals. Given the relationship from one individual's perspective and that person's gender, it returns the reverse relationship. It's set up for most types of famliy relationships.

The way it works is by breaking the relationship down into the prefix (great, grand, step), the suffix (in-laws), and the main part (the 'nuclear' part.) Since the prefix and suffix would be the same in both real-world relationships, those are applied without changes to whatever transformation is needed for the main part. The main transformation is still a big case statement, and adding other types of relationships requires adding to this case statement and modifying the list of nuclear types.

I'm still not sure about the problem of creating the reverse database relationship automatically. Maybe something will come to me when the turkey wears off.

RelationshipTree.fp7.zip

[Ender]

I've updated my sample file to show a scripted approach to creating the reverse database relationship. Though it would be nice if the relationship could just be auto-entered when you add the first one, I believe an additional join record must be created in order for the reverse database relationship to work. Because of this, I'm using globals for the data entry fields. The script then adds the first relationship and the reverse relationship.

See attachment.

RelationshipTree.fp7.zip

[dadudette]

Hi Ender,

I've checked out your relationship tree file and it does just what I want a file to do. I'm wondering though how you set up that list of relationships and how I can create one to suit my needs. Can you help? I have figured out that simply entering a new relationship on the individuals layout creates that, but how to create the inverse relationship?

da

Edited November 17, 2006 by Guest

[Ender]

Ho da!

It's hard to believe it's been nearly two years since I worked on this.

To view/edit a particular relationship in the Relationship Graph, double click the relationship line. This brings up the Edit Relationship dialog. In there you can change the fields or relational operators. Look at the sample file to see what the relationships are in this case.

This particular example (and the point of this thread) is about the Custom Functions that are used to make the inverse real-world relationship show up. You would need access to FM7 Developer or FM8 Advanced in order to install these custom functions in your solution.

If you understand the basic algorithm that I talk about, you may be able to work out an equivelant scriped method that doesn't require custom functions.

[Stuart Taylor]

What happens in cases of insest ... or did anyone see that episode of Fururama when Fry becomes his own Grandfather...

hehe

Please just ignore me ... it seemed funny at the time!

[Ender]

Here's an updated version that includes a couple bug fixes, including fixing some syntax errors (how'd they get in there?) and FM8.5 compatibility (no more using "list" as a parameter, folks).

RelationshipTree2.fp7.zip

[Stuart Taylor]

Just added a Generational Sort Order to your update ...(the beginnings of a tree if anyone wants a go)

Love the Michael Moore relationship hehe

RelationshipTree2Sort.fp7.zip