Problem with many to many

[Johhhn]

Hola! Great site here,, I've done some searches but haven't found what I needed yet, so here it goes:

Creating a database to keep track of 'People' and their relationship amongst each other.

Example:

Johnny Smith, son of John Smith and Jane Smith, nephew of Bob Smith, friend of Hillary Dvorak, brother of Jill Smith, etc.

This part I have done already with One table for People and another Table for Relationships. I go to Relationships table, enter the different IDs for two people and specify relationship.

The difficulty I have figuring out is how can I, then, simply go to John Smith and have it show Johnny Smith (his son)? I don't care to show his wife, brother, etc.

Conversely, if I were to, instead, go to the relationships Table and 'swap' the ID #s around and enter Son, it'll show Johnny Smith as his son, but it won't 'automagically' show John Smith as father if I look at Johnny's record (unless, of course, at this point in time, I enter both relationships twice in the relationship table.

But, I want to avoid that, AND, avoid having to write a script that'll automatically enter another record in the relationships database with the IDs swapped. IE, I want to avoid duplicating information- I figure if I already enter -1- relationship into the database, I should be able to figure out its 'inverse' relationship with the 2nd party.

Here's what I have so far: (attached)

Thanks for any help!

Update: I was playing around again just now with it, and I made a new layout showing People 2 'table' and it showed the relationship of the father to the son, but now, the son, doesn't show the father in this case.

So, the big question is, how can I somehow 'combine' both of these 'tables' (i say 'tables', because they're the same table but one is an 'instance??' of the main table)?? I feel like I'm soooooooo close :)

Edited May 11, 2006 by Guest

[Ender]

Welcome John!

We had a good discussion about this a few months ago. Check out this thread and the related links and see if any of those ideas make sense:

http://fmforums.com/forum/showtopic.php?tid/171427/

Let us know if you need clarification.

[Johhhn]

Thanks Ender,, it seems that I'll need to create a script to 'create' the other 'half' of the relationship- this is what I was afraid of. I was hoping I could avoid this.

hmmmmm... Will have to play with this some more! :)

[Ender]

Since you're working on creating the inverse real-world relationship for the second join record, you might see if the attached demo helps. Unfortunately, this solution does use a couple custom functions (one recursive) to determine the reverse relationship. This would require FM7 Developer or FM8 Advanced to access and install. Though I suppose a scripted approach could also work.

ReverseRelation.fp7.zip

[Johhhn]

That definitley works! hehe as you already know...

I've been talking to a couple buddies of mine that use mySQL and they say that all I would have to do is lookup the appropriate column and have it show the 'other' column- to identify who that person is related to. It seems this is akin to having another TO that I made earlier that I mentioend in my 'update' in my previous post. I assume, with mySQL, you can perform some fancy query to be able to show both relationships no matter where you 'created' it?

So there's nothing like this equivalent in FM?

Thanks for your example database Ender, hopefully when I get back next week I can work on my DB further!

[Ender]

I'm sure you saw from that other discussion, there are differing thoughts on the best underlying structure. I favor the two-join record approach (one for each direction of the relationship,) which the above demo shows. I can't tell what your SQL buddies have in mind, or whether it translates into something that works in FileMaker.