Restricting Duplication of record by Validation using Configuration:

Description:

Validating a record existance by  checking the combination of some defined fields, pops an error message if the record already exists with that field values.

Requirement:

An attempt to create a new ‘Contact’  should fail  if the provided values for ‘First Name’ and ‘Last Name’ for Contact  are same as that of any existing record. An error message should pop  up while trying to save such a record.

Configuration Steps:

1.       Create a New calculated field in Contact BC, where the calculated value contains the functionality with below search criteria.

Name: Test_Duplicate

Calculated: True

Calculated Value: 

BCHasRows("Contact", "Contact","[First Name] = '"+[First Name]+ "'" +  "AND" + "[Last Name] = '"+[Last Name]+"'" , "All")

BCHasRows function  is specified with 4 parameters like

BCHasRows( “BO”,” BC”, “Searchspec”, “Visibility” ).

The calculated value for Test_Duplicate is returned as ‘Y’ if the search specification is satisfied, else it returns ‘N’.

2.       Add the validation criteria and validation message to one of the  search criteria fields(Either First Name or Last Name) or the required fields of the BC, so that validation is checked before the record gets saved.

a.         Query for Last Name field in Contact BC.

b.        Update the record with the following values:

Validation : [Test_Duplicate] <> "Y"

Validation Message: Record Exists

If the Validation is satisfied, i.e. if the value for the Test_Duplicate field  is ‘N’ , the new record gets saved because the combination of the values entered for First Name and Last Name doesn’t match with any of existing records.

If the Validation is not satisfied, i.e. if the value for the Test_Duplicate field is ‘Y’ , error message “Record exists”  is popped up and the record will not get saved, until you give new values to ‘First Name’ and ‘Last Name’.