need to check uniqueness based on 2 or more field

[randy remark]

This one is frustrating me.

I need to automatically validate records for uniqueness but not based on 1 field but 3 or more.

For example, I have a customer field, a plot field, and a location field.

Each field can have any value but if all 3 values together can only be in the database once.

What am I going on about?

Example entrys

Customer Location Plot

Mr A john st london 2

Mr A john st london 3

Mr B paul st london 3

Mr B mark st london 3

The reason I need this is that I can input many many records, and its easy to double input records, but I can't have them, ideally a validation message would pop up and say that this cust/plot/location is already in the database

I thought the solution would be by creating a calculation field ie validation=customer&location&plot

But you can't use the unique validation on a calculation field, I've tried using autoenter fields but I can't get any to work

Any help would be greatly appreciated.

Cheers

[Vaughan]

Do a search in the TechInfo database for "validating calculated fields". It works.

TechInfo is at http://www.filemaker.com/

[CobaltSky]

I saw the link to that article recently. It is:

http://www.filemaker.com/ti/103792.html

Cheers,

[Mark Appleby]

I had the same problem a few months ago and Danjacoby gave me the following tip. It works...

"Create a self-relationship, based on the calc field. Next, create a calc field (call it "one") that returns the number 1. Finally, create a calc field that returns a warning if the sum of the related "one" field is greater than 1."

In order to help the person entering the information the calc field is...

if (sum related "one">1,"DUPE","")I then put a very large field on the layout which is transparent I made the typeface HUGE so that it covered the screen with the word DUPE. Only a dope could miss it.

[Vaughan]

This is a good suggestion.

However, the validation technique actively *prevents* the user from entering in data that will result in a non-unique calculated value.

[BobWeaver]

As I recall, Chuck posted a method of validating uniqueness on several fields in the development standards forum. It may be the same thing that is in the FMI tech article, but you may want to check it out.