many-to-many relationship problem

[susansan]

I'm using the inventory template. The problem is that I have a many-to-many relationship that I don't know how to handle in Filemaker (I'm new to FM, but have database design experience). Each product in my Inventory table can be assigned to more than one category. A category can be assigned to many products. So, I built a bridge table called Inventory_Category to hold a concatenated key of the InventoryID from the Inventory table and the CategoryID from my Category table. Now when I'm in form mode and I'm entering a product, I need to be able to click a button (or do something) so that I can add multiple categories to it. How would I build this and have it insert the current InventoryID and the CategoryID I specify into my Inventory_Category bridge table? The CategortyID being entered must exist in the Category table. Any help is appreciated!

[John Mark Osborne]

Here's an example I built for someone else of a many-to-many relationship using an invoicing example. You'll see how it uses two foreign keys rather then one in the join table.

joinprint.fp7.zip

[susansan]

Thanks! Your file seems like what I need for my solution, however, my export needs are different. If I have an item record that has multiple categories, I need it to export with the redundant data as follows:

Sterling Silver Ring 19.99 all-rings

Sterling Silver Ring 19.99 stoneless-rings

Sterling Silver Ring 19.99 pave-rings

etc.... for all my products. Is there a way to do this?