planning relational database

[wimverbe]

I am sort of stuck. I created a contacts database, with lots of functionality, search scripts, automatic mailmerge etc. then they asked to adapt it "a bit"...

each record (company name) has 1 up to 4 contact persons, each with their own set of fields. the question is to be able to print labels and letters to 1 or more contact persons from the same company.

e.g.

company A has contact persons a1 and a2

company B has contact person b1

company C has contact persons c1, c2 and c3

I have markers next to each name, to define if they should receive mail or not.

Now I want to mailmerge to persons a2, b1, c1 and c3, so I need following labels:

company A

attn. a2

company B

attn. b1

company C

attn. c1

company C

attn. c3

I tried to do it within a single database, but couldn't figure it out, especially with the labels. I found it impossible to print more than 1 label from 1 record.

then someone in the Scriptmaker forum suggested I make a relational database, the second one having one record per contact person, and printing from there.

technically, it does not seem so difficult, but conceptually it drives me nuts:

what should be the match field? (2 companies can have the same name)

when creating a new record, how will the new 1-4 records be created in the related file?

how do I transfer all the existing fields with contact names etc. to separate records?

when scripting the print command, (which is done from the master file), how do I make it continue running in the related file?

these are all questions I could not find an answer for in the manual, and I did not find an example file that did exactly what I need.

thanks,

Wim

[BobWeaver]

It would be a one to many relationship from the company file to the employees file, since one company can have many employees. The common relating field would be the company ID number.

[Ugo DI LUCA]

Do not consider my post as the best answer, but I just finished the job on my own contacts file with the help of the forum, and here are some points I can give.

You should have three files :

Company.fp5 with Unique Random ID

Contacts.fp5 with Contact Id

A Line Item.fp5 with both Ids in it, where you would process all the reports, printings, mails...

Set 2 relations in LineItem.fp5 :

1.contactid::Company.fp5-CompanyId

2.companyid::Company.fp5-CompanyId

Set relations in Company.fp5

3.CompanyId::LineItem.fp5-companyid

Set relations in Contacts.fp5

4.ContactId::LineItem.fp5-contactid

Then Create records from both files using portals set to create relative records in the LineItem file using the respective relation.

To make it simple, you would use these portals to select the companies/contacts to assign. Therefore, a line item would have contact id and companyid filled and the relationship would be on the way for editing, viewing,... from Contacts.fp5 or Company.fp5

This structure allows you to have one unique contact for 2/3 companies, have an "illimited" number of attachments, and to show respective records.

Imagine John X, being

Manager for Company A, adress a1, Zip Code z1, City c1, Tel t1

Manager for Company C, adress b1, Zip Code z2, City c2, Tel t2,...

In Contacts.fp5, you could have a filter portal to select the specific records you want to use for mailing, either John,Company A or John,CompanyB.

As a unique contact could have many e-mails and many adress, I would therefore not use labels in the contact file, but better attached it into the line items.

A lots of relatinships could be created in the three files to execute all what you want from one file to another, the best one is to create a gContactId in Company.fp5 and a gcompanyid in Contact.fp5.

Setting relations gContactId::ContactId and gCompanyId::CompanyId.

As an example, you could check my "TEST" File in Database Theory (posted it yesterday-use second attachment). Don't look at ugly interface please...this is a test file.