Basic Many-to-Many question

[Tar Ashland]

Let me admit up front - I am new. Please be kind.

I have been hunting the forums and haven't found a solid description on dealing with a basic many-to-many relationship in version 7, so I fall on you, the expert to PLEASE lend a hand.

TableA has a serial as the PK and a text field.

TableB has a serial as the PK and a text field.

TableAB has two number fields, the PKs from TableA and TableB

So we have A -=< AB >=- B in the relationships graph.

I want to add a record in TableA, type something in the textfield and select one or more records in TableB that relate. When adding to TableA, I need to see the textfield from all records in TableB.

I think that means that, as a record is added to TableA, TableAB getting one to many records added - the new TableA_ID with each of the TableB selections.

Here is the kicker: If the item doesn't exist in TableB, I need to be able to add it.

I am attaching a file with the basic setup. Masters of FM, I hope that you can help!

FileMaker Version: 7

Platform: Mac OS X Panther

M2M.zip

[Reed]

here is your modified file I have added two portals to each of the primary table layouts. The bottom portal in each case just shows all of the items in the other table using the "x" cartesian procuct relationship. These are selection portals where clicking on one of the rows will add the join record with the appropriate ID values. I made table A people and B makes of cars. The top portal in each of the two layouts is the one that shows which car makes are owned by which people and vice versa.

The bottom selection portals could be filtered somehow if there were many choices, and you could add a couple of steps to check for the existence of a join record before one was added.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

M2M.fp7.zip

[Tar Ashland]

This is a perfect example. I am going through it to see that I understand each of the steps.

I noted that you added two scripts that manage the inserts into the join tables.

Thanks for taking the time to assist!

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[spk]

Reed, excellent example, thank you. I have borrowed it to fit a project I am working on. I was wondering if you could clarify what the transparent button that you used to add items to the table is grouped with, because I've been unable to figure it out.

Tar, my apologies for hijacking your thread.

[Reed]

It's not grouped with anything, it's just a transparent button that calls the scripts to add records to the join file. I placed the button on the portal so that simply clicking on a portal row will use that row in the script.

[spk]

Yeah, I figured this out after checking in further. Reed, one last question and I promise you'll get your thread back, Tar.

I've attached my version of the file and noticed that if I delete items from the portal row up top, it deletes items from the master list of items. Did I do something wrong when I made my changes?

//EDIT: Would have been nice if I added the file, eh?

shelves_options.zip

[Reed]

You didn't do anything wrong. The top portal is showing records from the master list (Table A or it is just filtered through the join. If you want to show the join records, just make the portal show records from table AB instead. (I think I posted this modified file in another thread, I can't remember where though)