need to validate calculated field is unique

[Thomas2syllables]

I have a field that calcuates a sum, but I need to validate that the sum is unique.

The calculation is based on four other fields. It is:

(FIELD1*100000000)+(FIELD2*100000)+(FIELD3*1000)+FIELD4

It generates a "unique" 11-digit ID based on the four separate fields, but as a regular calculation, it is possible to have duplicates, which I can't allow.

Even if I had to add a field that performed a logical test like "Is FIELD6 unique?" with a YES/NO as the answer displayed in a field next to the "unique" ID field.

Any suggestions?

Thanks,

Thomas

Edited March 1, 2006 by Guest

[T-Square]

To answer the question, you could create a self-join on the calculated field, and use validation to test whether there are entries on the other end of the relationship.

The deeper question would be to ask you why you need this calculated field to be unique. If you're using it to form relationships, you might be better off using a true unique serial number rather than some calculated value that might repeat.

David

[Thomas2syllables]

How does one create a self-join?

To answer your question about why is it unique, I have to use the separate fields to create the 11-digit unique number in the fifth field, because each of the four fields is something we sort on (using a separate program). Long story....

Edited March 1, 2006 by Guest

[Lee Smith]

Why not just do a multiple field sort based on all of the fields?

Edited March 1, 2006 by Guest