Relate all family members to each other?

[Grundly]

Hello folks!

Setup:  Our graph looks like this:   People >> FamilyMember >> People. The People table has a name field and a primary kp_PeopleID field. The FamilyMember table has but two fields: kf_PeopleID and kf_FamilyMemberID. The first relationship predicates are: People::kp_PeopleID = FamilyMember::kf_PeopleID. The second relationship predicates are: FamilyMember::kf_FamilyMamberID = People::kp_PeopleID. We have a portal on People layout, that looks at FamilyMember (and the second People table occurrence also, to grab their names). This is all fine and well to specify other People as Family Members, we have a scripted picker that creates related records in FamilyMember by dropping in the Family Member's PeopleID. No problems there. Everything works great. We have a bunch of records like this now.

Example data: Bob has 2 Family Members, Mary and Jane, they show up in Bob's Family Member portal. If you go to Mary's record, you will see, she has two relatives also, Sven and Thor. Also, if you go to Jane's record, she has a relative, Susan. So technically speaking, Bob actually has five relatives, according to the way we think, not just the two in Bob's portal.

But now the task is this: We want a script that'll fetch all Family Members, and relate them all to each other. We want to see Sven, Thor and Susan also in Bob's Family Member portal, in addition to the original Mary and Jane. Further, we want all of these six people's Family Members to have the other five people in them, too. We could trigger this script with a manual button, and maybe also tack it onto the end of our Picker script. I want to do this in an elegant way, and the only way I have come up with so far is a very large, very limited, very hard-coded and hairy script which stops at three levels deep, which is probably not the best way. It feels like it should be a LOT shorter and more dynamic than what I have now. Any suggestions? 

[comment]

The word "relatives" is misleading here. In real life, if A is a relative of B, then B must also be a relative of A. In your situation, it would be more fitting to talk about links or perhaps apply the terms of ancestors/descendants, since the relationships are not symmetrical. With that in mind, I suggest you have a look at:

http://fmforums.com/topic/38282-join-table-inverse-relationships

and also:

http://fmforums.com/topic/68142-hierarchy-help/

 

[Grundly]

I'm sorry if you are unable to understand my problem, I may not have explained it well enough. Would anyone ELSE like to take a stab at my problem? Please don't reply if all you want to do is talk about how good someone is. Only reply with technical answers about my problem. Thanks.

[comment]

Did I miss an episode here? I don't see how this connects to what I wrote.

[Rick Whitelaw]

8 hours ago, Grundly said:

I Please don't reply if all you want to do is talk about how good someone is. Only reply with technical answers about my problem. Thanks.

What are you talking about?

[efen]

Something like the attached? Or have I misunderstood your problem?

Link.fp7

[LaRetta]

I'm afraid you aren't explaining what didn't work in what Comment provided.  If you have been unable to explain it well enough that Comment can understand, then it is likely true that others won't be able to help you either.    He is one of the top rated developers in this business on deciphering someone's needs. 

Did you read the links he provided?  How weren't you helped?  Without some additional clues from you on how we missed the mark, how can we provide alternatives?

[Grundly]

Thanks folks you've been helpful. Efen, you got it, that file you created hits the mark. It looks like it was not trivial to create, so thank you for taking the time to put together such a thorough example. I like how the loop works. That's totally the piece I was missing. From this logic you've laid out, I should be able to adapt it to fit into the existing solution. Perfect, THANKS!

[bruceR]

Very unclear whether this works in a rational way.

I took one person who was related to nobody (Melanie)

Added Jane; who was already related to 5 people.

Now Jane is related to 6 people; and Melanie is related to only Jane.

Note also that there are many duplicate join (Family) records. So I don't know about maintaining this.

[LaRetta]

Before responding, I had spent an hour reviewing the post and the file, as well as reviewing the links Comment provided, and I had come to similar conclusion, Bruce.  :-)

Grundly, it is always helpful, when presenting a complex relational problem, to provide a file (even a sample file) showing the issues you face.  If nothing else, you could have taken ANY of the sample files provided in the links Comment referenced from which to pinpoint your problem.  As is, as Rick asks, what are you talking about?  Responding with the flip of your hand and saying 'next' - instead of providing additional input, simply isn't helpful and is dismissive and abrupt - not exactly encouraging to those trying to help you.

[Grundly]

On 10/22/2016 at 8:17 AM, BruceR said:

Very unclear whether this works in a rational way.

I took one person who was related to nobody (Melanie)

Added Jane; who was already related to 5 people.

Now Jane is related to 6 people; and Melanie is related to only Jane.

Note also that there are many duplicate join (Family) records. So I don't know about maintaining this.

Yes, after some additional testing, I've run into similar shortcoming in that solution, unfortunately. In your example, we would have wanted Melanie to also become related now to all 5 of Jane's other relatives, but that does not happen, Melanie is just left with Jane only instead of Jane and all of Jane's relatives. The structure in efen's file is the same structure I have right now in my real file. Not sure if this can be further enhanced via script, or if a change in structure is needed. At a loss, but feels like we are closer than before.

[bruceR]

I suggest you go back to Comment's replies; and take his evaluations seriously. He's very good at this.