Composite Primary Keys in Filemaker

[mlenny]

I'm new to FM and coming from 9 months reading general DB theory - now hitting problems transferring theory into FM.

How can I implement a composite primary key in FM? In other words, several fields whose values singularly are not unique, but whose combined value must be unique, to be used as a match field in a related file.

An example is my current problem: a software database with developer, title and version fields, e.g. 'Microsoft' 'Word' and '5.1'. None of these values are necessarily unique. However, I want the three combined ('Microsoft Word 5.1') to be unique, so the same package does not get entered more than once.

I can make a calculation field using concatenation to combine them, but how can I then validate that result as being unique?

This might not even be the right approach, so all suggestions welcome.

[This message has been edited by mlenny (edited February 08, 2001).]

[This message has been edited by mlenny (edited February 08, 2001).]

[dspires]

One option, assuming FMP interface and not a CDML solution, is to concat the field as you described and create a self relationship (to the same database) based on the calculated field, call it 'unique'.

Create a field called 'alert_check' based on the relationship (doesn't have to be on the layout).

Create a calculated field called 'alert' as follows:

If( Count(unique::alert_check) > 1, "Your alert message goes here!!!", "")

Place this field on the layout with whatever notification message you want.

HTH

[balrog]

Is there a simpler way of forcing a concatinated field to be unique? If you set the field to be unique it wont let you enter a value that isnt unique but it will allow a calculated one.

[wildsicilian]

Have you tried something as simple as making a new field (call it unique) which is a text calculation and must be unique as follows:

unique=fieldA&fieldB&fieldC&fieldD&fieldE

This field would not have to appear on the layout but would alert you if the 5 fields when combined duplicated another record in the file.

------------------

Kathy Eldredge

the Wild Sicilian