Many to many relationships require a third table, called a join table, that serves as the link between your account and vendor tables.
Vendors::__pkVendorID = Vendor_Account::_fkVendorID
Accounts::__pkAccountID = Vendor_Account::_fkAccountID
You can place a portal to Vendor_Account on the Vendors layout to list and select a Accounts record for each given Vendors record. Fields from Accounts can be included in the Portal to show additional info about each selected Accounts record and the _fkAccountID field can be set up with a value list for selecting Accounts records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
For a demo file that uses a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
FileMaker 12 users can open this file from the File menu to get a copy converted to the .fmp12 format.
Ok, I'm beginning to follow you, but could I do the reverse , as it's in each of the accounts record that I want to have a portal to add related vendors. Then, in the vendor table, have a portal showing related accounts. (And with the inability to add accounts via that relationship).
And, if yes to doing the inverse, would I reverse the link or does it matter? see ex below.
The links are the same and you use a portal to the join table on either a layout based on vendors or on a layout based on accounts. But in each case, you add fields into the portal row from the third table. So if you are putting the portal on the Accounts layout, you would add fields from the vendors table to the portal row.
Ok, I've done all of that, can see my portal, but I can not add any new lines or actual vendors. i have made sure they're going to the right tables, I have records in each to make the reference, but it won't allow me to add anything. What step did I miss?
In Manage | Database | Relationships. Double click the relationship line to open the relationship details dialog. Select "Allow creation of records via this relationship" for the join table.
Ok, I did that. Except that every time I add a record (ex - in accounts layout, on the vendors portal, add related vendor) it creates a new one instead of letting me select from a pre-existing vendor. I have the vendors from a value list, select from that, however every time it creates it like new in the vendor layout, instead of the pre-existing one.....
The portal should not be a portal to Vendors. It should be a portal to Account_Vendor--the join table. You should be creating new records in this join table, but doing so will not create new records in the vendor table.
And you would format the Vendor_Account::_fkVendorID field as a value list of Vendor ID's and Names such that selecting a vendor's name enters the ID into the newly created join table record and links it to the selected vendor record. This then enables any fields from Vendors--such as a Vendor name field that you may have placed in the portal row, to display data from the correct vendor record.
You may find it helpful to take a closer look at the demo file so that you can see some of these "nuts and bolts" details in action and compare them to what you have set up for your file.
Ok, I know I'm doing something right and something wrong. I have the portal working 95% properly in the accounts file. I can pull up related vendors - (I have it w/ the vendor id, and vendor name) , but when I insert corresponding fields (such as primary contact - it leaves it blank, for me to fill in. I have been studying the linked sample database - it's very helpful - but i'm really stumped.
I also can't insert a portal on the vendors file to show related accounts... (which i was just using the vendors- accounts table again) ... I know I must be doing something dum, but i just can't figure out this problem. I really appreciate the help so that i can finally get it fully working!
but when I insert corresponding fields (such as primary contact - it leaves it blank
Either the fields are from the wrong table occurrence of Vendors or your value list is entering the wrong value or into the wrong field and thus there is no link to the selected vendor record.
I also can't insert a portal on the vendors file to show related accounts...
How/where/when does this fail for you? This may also becaused by the same isse that is causing problems with getting data from Vendors to appear in your join table's portal.
Victory! I figured out what was missing in the table and it now works great! Thank you for all the help!