The best relationship to three tables...

[edudna]

Let we have three tables,

Table one is groups that only have six records display six groups of costumers. There are two more tables one for subgroups and one for customers. One group may have one, two or three subgroups. A costumer belongs to one group and to one subgroup. For example a costumer may belongs to group A and to 1 subgroup called A1...

Table 1

GroupID

GroupName (there are only, a,b,c,d,e,f)

Table 2

SubgroupID

SubgroupName

GroupForeignID

Table 3

CostumerID

First name

Second name

SubgroupForeignID

GroupForeignID

How can I do the best relationship in order the three tables work together in the right way?

Pascal

[Ender]

The ER Diagram is simple enough (see attached). The loop implies your actual relationship graph will need two table occurences of at least one of the tables (probably Subgroup).

[edudna]

I built the relationship according to your diagram. Unfortunately the relations show are many to many. I do not know what this happens.

I put an attached file to see.

Groupsandcostumers.zip

[Ender]

FileMaker shows a many-to-many because it doesn't yet know which is the parent side and which is the child side. When you make the primary keys auto-enter serial numbers (or defined as Unique) they will change to one-to-manys.

You'll still need to add another TO (table occurence) of the Subgroup table and link that Subgroup TO to Customer via SubgroupID.

Also, you have your primary key names starting with a "g". Field names starting with a "g", like "gMyField", is a naming convention that indicates the field is defined with global storage. In this case, your primary and foreign keys would not be globals, so you should probably fix those names.

[edudna]

As I understand I have started Filemaker in the opposite way.First I built the database, then I have to learn the software how it is works... I am learning fast I think since I have started before twenty days. But I like Filemaker anyway. Thank you for your response.

Pascal

[comment]

A costumer belongs to one group and to one subgroup.

Perhaps I am missing something, but it seems that once you assign a customer to a subgroup, the group is known. So there's no need for a direct relationship from Customers to Groups, and the field Customers::GroupID is redundant.

From the point-of-view of user interface, it might be convenient to select the customer's group first, then choose from the available subgroups. That would require a second relationship from Customers to Subgroups:

Customers::GroupID = Subgroups 2::GroupID

But this a purely auxiliary relationship. Moreover, one should take care to avoid a possible contradiction between the selected group and the subgroup of a customer.

[edudna]

When a user in the costumers' record indicate the group of the costumer, the subroups of the particular group should be shown automaticaly in the subgroup field and not all the subgroups. How can this be done? When a costumer indicate the subgroup it should indicate the group at once and it does not need of the user to complete both fiels. I hope you understand.

Pascal

[comment]

See the attached example.

example.fp7.zip

[akfruit]

How do you add more continents then countries within this attached file?

Thanks

Martin

[comment]

Go to layout 'Groups' and add more records. Then go to layout Subgroups, add more records there, and assign them to Groups (by GroupID).