How do you force the value of a field to be unique?

This assumes you have a field named CompanyCode as an editable field. It also assumes that you have a view called "Company Code" which has CompanyCode column.

Use the following input validation formula for CompanyCode:

  @If(
       CompanyCode = "";
         @Failure("You have to fill in Company Code");
         @IsError(
             @DbLookup("" : "NoCache";  ""; "Company Code"; CompanyCode; CompanyCode));
           @Success;
         @IsNewDoc = 0;
           @Success;
       @Failure("The company code you entered has already exist, please change it")
     )

This formula first checks to make sure a value is entered. If a value is entered, it then uses that value as a key to lookup into the Company code view for a document containing that company code; if no document with that code is found, an error condition is created, which satisfies the @IsError @Function, and returns @Success. If a document with that code is found, the code is returned, which does not satisfy the @IsError function, causing the @Failure message to be returned.

Applies to Notes Versions: 3, 4, 4.5, 4.6, 5