Avoiding Duplicate Entries in Table

[hociman]

I have two (2) tables. One table is for company information, and the other is for contact (individual person) information.

In my contact layout, I have the company field configured as a drop-down value list, referencing the company field in the company table. I have a one-to-one relationship between the company fields of both the company table and the contact table.

When I go to create a new contact in the contact table, I choose the company in the value list that the contact is associated with. So, let's say I'm creating a new contact and want to associate that contact with company X.

When I go back to the company table, and search for company X, it now has a second entry. The act of choosing company X as the company associated with my new contact is creating a duplicate entry in the company table.

In the edit relationship dialog, for the Company table, I have to check off the "Allow creation of records in this table via this relationship" option, or the drop-down value list of companies does not appear in my contact table.

Is there a way to keep the drop-down value list of companies in my contact table without creating duplicate entries of the companies that are chosen from that list in the company table? My first instinct was to disable the option noted above, but then I do not even get a drop-down value list in my layout.

Any insights would be appreciated. Thanks for reading!

[SlimJim]

From your description of the "symptoms" I would say that the company field in your contact layout is the related company field from the company table, whereas it should be the company field from the contact table.

[if you do not check off creation of records then that field cannot be entered which is why the drop-down list disappears; if you allow creation of records then whenever you put company data in that field, by any method, it will create a new company record]

Incidentally the relationship matching company to company between company - contact is almost certain to be one - many not one- one.

[Raybaudi]

Hi

the "allow creation of records" must be checked only in the Company table

BTW the value list comes from related table with no "allow creation" too. (be sure to check: include all values)

Company.zip

[hociman]

From your description of the "symptoms" I would say that the company field in your contact layout is the related company field from the company table

You are correct.

whereas it should be the company field from the contact table.

If it were that way though, then how would I keep company data separate from contact data? My goal, conceptually, is to only have to enter company data once, but be able to use it in more than one table (without creating duplicates).

[if you do not check off creation of records then that field cannot be entered which is why the drop-down list disappears;

Thanks for the info! I wish I knew that earlier.

if you allow creation of records then whenever you put company data in that field, by any method, it will create a new company record

So then is it impossible to select data from a table and not create a duplicate in the process of selecting what you want from said table?

Incidentally the relationship matching company to company between company - contact is almost certain to be one - many not one- one.

Yes, conceptually I can see this. However, in my relationships graph, it is drawn as one - one. Is there a way to change how it is drawn (make it one - many)?

Thanks for your response!

[SlimJim]

Hi Hociman

Let's look at the basic structure of your database. You have a companies table - it should contain an ID number (auto-enter serial is fine) and then whatever details you want to store for a company. Your contacts table should contain two ID numbers - contactID (auto-enter serial is fine) and CompanyID plus whatever details you wish to store about a contact. The relationship between company and contact is matching companyID.

If you are looking at a contact layout and you wish to select a company then you will need the CompanyID field somewhere. (There have been lots of discussions as to how to do this so I will suggest a straightforward method and you can read and change if you wish) Make a value list of company ID numbers (show all) and show Comapny Name as a second field. Choose to sort by second field. As you are using v8 you can choose to not show the ID field in the value list. Set up the companyID field with this drop-down list. When you select a company its ID number will go into the field. If you want to see the company name on your layout then put the related field comapny::company name on the layout. This will show up as soon as you have chosen an ID number. If you allow creation of company records via the relationship then leaving the companyID field blank and entering a name in the company name field will create a new company record (you will see its ID number pop up into the companyID field.

I have made a few alterations to Daniele's sample to illustrate.

Company.zip

[hociman]

Danielle,

Thank you for the example file.

SlimJim,

Thank you for modifying it and explaining what was going on.

Thanks to both of you, I now have what I desire in my database!