working with unique fields

[vovin]

Hello.

I'm new to Filemaker and I have a problem setting up my database.

I guess this is a silly questions to you.

Here is my problem.

I have two tables one of these tables is used to store a company name.

The company name should be unique so I added a Id in the table.

It just looks like this

Table Company

field1 company_name

field2 company_id

Company id is set up to automatically asign a number and as a unique field.

Company_name is also set up as unique field.

When I create a new record and type in a company that already exist. Filemaker complains that this must be a unique field.

I know that this is right but I want it to use the company and all associated data when it already exist.

When set company_name up as non unique I can add same company names which will have different ID's.

How do I have to set up the field to do what I want?

I tried the option auto complete from previous entries but it is the same error.

Can anyone help me?

[mr_vodka]

If you setup a field as unique, you will not be allowed to create a another record with the same value in that field. I am not 100% sure what you are tring to do, but if you want the fields to populate with the information from your last company with the same name, then you can use a self join keying on company name and the rest of the fields as auto-calculations. This will allow you to type in a new record the comapny name, and the address, phonenum etc will fill in automatically with the first record of the company.

However, I am wondering if your database structure is correct if indeed you want to have a unique company list, then you should have a seperate table with unique company names that you can pull the data from into another table that allows you to do whatever with.

[vovin]

Hello.

Thank you for your answer.

What I want to do is comparing products in our stores in different countrys. So I need unique country names that I can use.

Otherwise people could write it different or wrong and that wouldn't be good for the queries I want to run later.

Like you suggested I already created a seperate table for the countrys and that country table has a relation to the products table.

But I need this country field to be unique so I thougt adding a unique Country_ID field might be a good idea. But it isn't working the way I want it.

What I need the field to do is someting like

"If counry exists use it, if not add it".

I really don't know how to do it.

I tried to do it with a drop down list now but it is always adding the selection in the table

e.g. I got 4 times UK with different numbers.

In the end I want to run queries like:

Which products are in the store of country1 that country2 doesn't have and so on.

Any help is more than welcome.

[mr_vodka]

I think that a better way of doing it would be to still use a drop down list. This drop down list of countries will prevent users from adding their mispelled country names. Then you could have a button next to the field 'Add country to list' etc. This button can check if the country exists already (self join on country) and if it does not, it can add it to the country table, which in turn would add it to your value list.

[vovin]

Thank you for your input.

I will try to set it up like this.