Jump to content

Relational File Attachment


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

Recommended Posts

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?




Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6440 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

  • Create New...

Important Information

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