Unique combination of fields

[GertLievens]

I just began with developing in Filemaker and have some questions of which I didn't find an answer (also by using the search function of the forum).

For example, I have 4 tables:

1. Firm-table with FirmID

2. Firm_Heading-table with firmID and HeadingID

3. Heading-table with HeadingID and ThemeID

4. Theme-table with ThemeID

Relations are as follows:

Firm(FirmID) => Firm_Theme(FirmID)

Firm_Theme(HeadingID) => Heading(HeadingID)

Heading(ThemeID) => Theme(ThemeID)

So the table Firm_Theme is a table with 2 ID's (FirmID and HeadingID

The COMBINATION of these 2 values needs to be UNIQUE. One ID can be recorded more than once, because a certain firm can have different headings. BUT a firm can have a certain heading just once.

How do I make the combination unique?

Edited April 2, 2007 by Guest

[Inky Phil]

Your firm ID needs to be set to be an auto enter serial number and check it so that the user cannot change it. That way each time a new firm record is created a new number will be allocated to the record.

HTH

Phil

[GertLievens]

All my ID's already were "auto-enter seial" and unique.

I added a little screenshot of this part of my database. The most important table is FirmTheme (should be named FirmHeading). The firm needs to choose between several Headings. So if firm 3 chooses heading 2 we get:

firmID = 3

headingID = 2

The headings and firms are unique.

What do I want?

That firm 3 cant choose heading 2 anymore when it already has chosen that heading.

Sorry, quite difficult to explain but the solution has to be very simple. I just want that one firm can choose between various headings, but one heading can only be entered once with a certain firm

[Inky Phil]

Sorry but how does a firm 'choose' a heading ?

phil

[Søren Dyhr]

I think utilization of one of these CF's might do the dwindling job for you:

http://www.filemakerpros.com/FILTER.zip

...or

http://www.briandunning.com/cf/39

--sd

[comment]

See if this helps.

[GertLievens]

Thanks, you guys are really helpfull. I will try some of your tips tomorrow and let you know if it works.

Thanks

[GertLievens]

In reply to:

I just use a portal where a firm can choose theme's. This data is inserted in the firm_theme (should be named firm_heading) table.

My problem now is: I can choose a heading in the portal. But I can choose it more than once. That isn't what it's supposed to do.

So if firm 2 has heading 3 in the table, it cannot be inserted into the table anymore. (only in combination with another firm)

[comment]

You can choose between validation and a dwindling value list (or both).

The thread I linked to explains how to validate a unique combinations of fields. Using this method, the user CAN choose an existing combination, but it will not pass validation.

A dwindling value list, OTOH, will present only available choices in a drop down list or menu (in your case, only those headings that have not yet been assigned to the current firm). You can find several examples of this method by searching the forums. But it is more difficult to implement - not something I would suggest to a beginner.

[GertLievens]

Ok, thanks, I'll get to it after my break. :

Thanks