variable foreign key relationship

[kenneth2k8]

hello:

I know my title may not be right, but basically I want to create a relationship using 2 value list fields. This is the setup:

I have a table named EVENTS. It has various events and their dates. There is also a field called "Interests" which is a checkbox with various value list values in it:

New Products

Seasonal Events

Instructional Events

just as an example. So let's say I make an event record called "Summer Slam" and I check the "New Products" and "Instructional Events" for it, since those are the Interests associated with this event.

There is another table called CUSTOMERS. It also has an "Interests" field with the same values.

I want to be able to join these two tables so that if a customer has interests in "New Products" and "Instructional Events" then those events will show in a portal, such as the summer slam. The problem I ran into with this is that people may check values in the field in different orders, making the key different than the key for the event.

For example in the EVENTS table the Interests field for summer slam would look like:

New Products

Instructional Events

but, in the CUSTOMERS table, someone might have clicked the values in a different order, so the field in CUSTOMERS could be:

Instructional Events

New Products

so in that case, the customer wouldn't have the associated summer slam event be related.

I hope I make sense with this example. If you need to see an example set of files I can do that. Also, this is fp5 so if there are any factors with that...

Many thanks for any help you can provide.

[comment]

Should a customer that's interested only in "New Products" see this event?

[kenneth2k8]

Hi, thanks for the reply

Yes, a customer only checked with "New Products" should see this event.

I realize that it's not what I would want, I would make it only one value per event, but this is kinda what the people want. They want events to be able to have multiple Interests checked.

Thanks again for the help!!!!

[comment]

Then I don't understand the problem: if you relate the two tables checkbox-to-checkbox, you'll get an OR match, regardless of the order of the values.

[kenneth2k8]

actually when I test it, it appears FM is smart enough to know that the value list items will appear in different lines but it can still handle this type of relationship. It seems to work ok.

Thanks but I guess nevermind since it appears to work! Thanks, I'm just not used to that in other dbms. Sorry to take up time on something silly!

Edited June 15, 2010 by Guest