Best Database Structure

[Dr. Don Levan]

Hi All,

What would be the best structure to use when you want to create a portal that

shows records from the same table which are related. I know you can do a

self join, but I believe this is more complicated and I have become quite

confused.

I am trying to set up a recipe database which as part of its function identifies

ingredient pairs which are good matches. I intiially set up a filemaker seven

database which several tables, two of which were "ingredient" and

"flavorPals". I created a relationship in filemaker 7 beween the primary key

field in the "ingredient" table ("zk_ingredientId_p") and the foreign key field

("zk_ingredientIds_f" in the "flavorPals" table. The foreign key field was a

multi-line key which was set by a script to place the key of ingredient1 on the

first line and ingredient2 on the second line.

I then set up a third TOG called "flavorPals_info" which was a representation

of the "Ingredients" table and created a relationship from the "flavorPals"

table using a relationship identical to the one described above.

I set up a portal on the ingredient layout to show the flavor pals. My intent in

doing so was to display info about all the ingredient's related to the one

being displayed. Unfortunately, it seems I have set up the sturcture wrong,

because it does not seem to be working right.

What I want to have happen is to show the other ingredient when any of the

ingredient's are showing. For example, if I have thyme and basil, I want to

show basil as a match when thyme is selected and show thyme as a match

when basil is selected.

Any suggestions would be very much appreciated. Thanks in advance.

Don

[Ender]

Welcome Don!

This sounds like an interesting project. Structurally, it is the same as a Node-Path model I have played around with, where a Path is defined between two Nodes, and you want to see the reverse path show up upon creation.

For a relationship to work from "Basil" to "Thyme" and from "Thyme" to "Basil", two join records must be created in FlavorPals, one for each direction. Unfortunately, creating one related FlavorPal record cannot automatically create the second FlavorPal record needed for the reverse relationship.

What I have done to make this work is use a global data entry field for the To node, and used a script to populate both join records at the same time.

[Dr. Don Levan]

Hi Ender,

Thanks for your suggestions. I will try that.

Don

[Dr. Don Levan]

Ok, one addtional question,

I can see how that would work great, the only problem is what if you want to use a script to delete the match? For example, I have a button in the portal next to the match to delete the related record. How do you get the script to find both copies of the match to delete them?

Don

[Ender]

Check out the attachment in my last post in this thread:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=135019

It uses the same Node-Path structure for creating relationships between people.

[Dr. Don Levan]

Hi Ender,

I wanted to check back and say thank your for your help.

Don

[Ender]

I'm glad you found it useful.