Jump to content

Basic Many-to-Many question


Tar Ashland
 Share

This topic is 6472 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

  • 4 weeks later...

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. smile.gif

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

You didn't do anything wrong. The top portal is showing records from the master list (Table A or B) 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)

Link to comment
Share on other sites

This topic is 6472 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.