How to find out what triggered a multikey relationship?

[Daniel N]

Hello,

I have a relationship that looks like this:

Direct link: http://img366.imageshack.us/img366/2411/relationship.jpg

Basically what these relationships do:

- When looking at a Contact (the contact table is named People) on the Contact layout, you can see what Films and Companies he is connected to.

- When looking at a Company on the Company layout, you can see what Films and People the company is connected to.

- When looking at a Filmon the Film layout, you can see what Companies and People the Film is connected to.

Fields named "p_", "f_" and "c_" are multikey-fields.

So as I wrote above, when you're on the Contact layout you can see the films and companies connected to that contact. What I want to add now is a list of people connected to the Contact ie. if you're looking at Contact you should be able to see a list like the one I added on the right on the current layout:

Direct link: http://img191.imageshack.us/img191/2289/example1l.jpg

(If you click on for example. Lord of the Rings in the layout, you would come to the profile of that Film. Looks like this: http://img194.imageshack.us/img194/3192/example2p.jpg )

I know how to show what people are connected to the Contact. I could just do this with a self-join relationship. So I could basically get a list like the one I've done on my example.jpg, however you would only see the people related and not HOW they are related ie. the list would look like this:

People related to this contact through films:

Person1

Person2

Person3

Person4

Person5

Person6

People related to this contact through

companies:

Person1

Person2

Person3

Person4

Person5

(Notice how the Films and Companies are missing on the list)

The self-join relationship will trigger when the Contact has one of his films/companies in common with another person. This is how the multikey fields look like:

Direct link: http://img262.imageshack.us/img262/2382/multikey.jpg

My question is: How can I find out what triggered a multikey relationship and show it beside the result of the triggered relationship?

For example:

If Peter Jacksson and Joss Whedon both have worked on a Film called Eternity, Eternity will exists on both Contacts P_FILM multikey-field and it will trigger the relationship and Joss Whedon will show up on Peter Jacksson contact profile, Without any way to see how they are connected. The trigger itself is what I want, the trigger is how they are connected... and I just want to write that trigger out beside Joss Whedon's name.

People related to this contact through films:

Joss Whedon (Eternity)

/ Daniel

Edited August 27, 2009 by Guest

[David Jondreau]

Your structure is way to simple. Mutlikeys are rarely a good idea. You should be using join tables instead.

I bet a Jackson/Whedon production would be amazing.

[Daniel N]

Thanks for you answer. To late to go back and change something that profound in the system now unfortunately. Anyway, I guess by your answer that there are no way to find out what triggered a multikey relationship?

[David Jondreau]

I suppose there are ways it could be done. Grab the list of Film IDs from one Director and compare it to the other. Since you don't have a version of Advanced and can't create custom functions, you'll have to script it.

But you're going to have more problems trying to fix the flaw in your structure that I don't see the point in patching it up.

It's like saying, "We've invested in these square wheels but the cart just isn't rolling on flat ground. We can't change the wheels, though, can you help?"

You need to get rid of the multi-line keys.