Linking Companies to Contacts and avoiding record duplication

[london25]

Hi there,

 

As a complete novice on Filemaker Pro I am am encountering what I am sure is a very simple problem and was wondering if someone was able to help.

 

I have a list of companies with related contact names and details on an excel spreadsheet and I know how to create a database out of this, by importing and matching the fields from excel into Filemaker Pro.

 

However, I am trying to work out a way to be able to enter a new contact into the Filemaker database for a company that already exists, without having to re-enter all the related company information as a new record. Ideally, I would like a command that reads - "Enter new contact for this company..." etc, instead of "New Record"...

 

I'm sure this has something to do with setting up relationships between tables, and/or writing scripts but I am only a beginner when it comes to writing scripts and I haven't managed to get my head around related tables, especially when ours is not a normal order tracking facility where contacts are linked to products sold etc, but is basically a simple contact management system. Could someone give me some pointers as to the easiest way to achieve what I am trying to achieve?

 

I would be extremely grateful for any advice.

 

Thanks

[Databasic]

Hi

 

This is a basic starting point for relational databases - and easy enough for me to amswer.. You need two tables - ie A company table and a contacts table. The first field in each table s/be pk_CompanyId ( pk being fm talk for primary key) and in the other pk_ContactsId . These would normally be auto enter serial numbers so that a new unique number is generated for each new record. Then in the contacts table you need a field called fk_Contacts_CompanyID ( fk being foreign key ). The Idea is that the foreign key in the contacts table should match exactly the Primary key in the Company Table. Once you have established these fields open the relationship graph and draw a line between the two fields - ie the pk_CompanyId field and the fk_Contacts_CompanyID field -- you now have two related tables. Then put other desired fields into the respective tables . Now fill in about 10 or so company records - Once that is done the data from these records ( for eg Company name) can be placed directly into a LAYOUT BASED ON THE CONTACTS table -PROVIDED the fk_Contacts_CompanyID field matches the pk_CompanyID field exactly. In order to get an exact match you set up the Fk-Contacts_CompanyId field on the contacts layout with a drop down list developed form the companyID field in the company table. So if your first company was Qantas and its Company ID was say 001 and the Contact was J.Smith whose Id was say 004 ( in the contacts Table ) then in JSmiths record on the contacts layout if you enter 001 into the fk_Contacts_CompanyId field you could the populate the contacts layout with all the revelent fields from the Company table ie - Name address Phone number etc. When you place a field from the company table on the contacts layout it should look like (for example) Company_Name::Company. If you set up all the fields you want from the company table on the contact layout then once the fk field is completed all the other information will fill in automatically.. it looks complicated but it is really easy. Also make sure you sort out your data in excel before importing - Ie remove duplications etc. Good luck