Attached is my attempt at making a many-to-many relation - question are:

1. What is a good way to make a unique Id other that having incremental numbers

2. Does anyone have any ideas for making the adding of a company to a contact more slick?

3. Any other comments/improvements?

Thanks

Simon

FR.zip

Using the build-in serial number is an excellant way to generate unique ID's. You don't have to start a 1 and when you make it text you can add a few characters to help make it unique to a table. Comp000001 and Cont00000001 as starting points for your file.

I think you should stick with a join file between your 2 main files. This is the more conventional way to many to many. The join file can hold additional data.

I would make a value list from the names in the contact file to use a pop up in the company file.

I only use the multi-keys for special conditions.

>>3. Any other comments/improvements?

It's hard to advise without knowing more about the context. I can envision why a company has many contacts, but why is a contact associated with many companies? Are they account managers or something?

Just trying to see where you're coming from here.

A number of the clients we have work at or are associated with more than one company hence the need for many-to-many relationship.

The current database one-to-many has about 30k of contacts and 10k of companies so a value list dropdown isnt really appropriate.

Has anyone got an example of a basic join file?

The most basic join file would be:

A file "CompCont.fp5" with the following fields:

fk_CompanyID_n (number field, stored; fk means foreign key)

fk_ContactID_n (number field, stored)

Then you need two relationships (to show the portals in COMP.fp5 and CONT.fp5):

in COMP.fp5: "CompCont by ID" (ID <=> CompCont::fk_CompanyID_n)

in CONT.fp5: "CompCont by ID" (ID <=> CompCont::fk_ContactID_n

Now, if you want to associate a contact to a company, you actually create a new record in CompCont.fp5, which holds the ID of the approriate Contact and Company. If you want to dissassociate, you delete the record.

For a (little bit) more advanced join file (to show the same data in the portals as now) you need additionally in "CompCont.fp5":

two relationships:

"COMP by ID" (fk_CompanyID_n <=> COMP::ID)

"CONT by ID" (fk_CoontactID_n <=> CONT::ID)

four fields:

Company Name: calculation field (= COMP by ID::Company)

Contact FirstName: calculation field (= CONT by ID::FirstName)

Contact LastName: calculation field (= CONT by ID::LastName)

Contact Phone: calculation field (= CONT by ID::Phone)

Place these in the portals in COMP.fp5 and CONT.fp5

I attach a sample, which shows one way how to script the adding and removing of associated records in the portal.

ManyToMany with Join.zip