How to restrict duplicate records entry in a table using business rule?

Shubhashis · ‎02-17-2022

I have a table:'x_snc_aob_test1_request_baseline_input' .There have 3 fields -Account name(reference field) , Onsite/offshore(choice field) , Baseline input (integer field) .All are mandatory. Here an user can not create another record which has same account name and same onsite/offshore of any previously created records.At that time an error message should be shown -"Invalid insert". So ,how to do?

Murthy Ch · ‎02-17-2022

@Shubhashis

If you want to do with BR only then do with before insert

Sample logic:

(function executeRule(current, previous /*null when async*/ ) {

    var grP = new GlideRecord("table_name");
    grP.addQuery("field_name", current.getValue("field_name"));
    grP.addQuery("field_name", current.getValue("field_name"));
    grP.query();
    if (grP.hasNext()) {
        current.setAbortAction(true);
    }

})(current, previous);

Hope it helps

Thanks,

Murthy

Thanks,
Murthy

View solution in original post

Raghu Ram Y · ‎02-17-2022

Hi @Shubhashis ,

It is not possible to set Unique field as combination of two fields. However, you can make use of Before business rule to validate the entry of the new record.

var validateRecord = new GlideRecord("table_name");
validateRecord.addQuery('fieldname',current.fieldname);
validateRecord.addQuery('fieldname2',current.fieldname2);
validateRecord.query();
if(validateRecord.next())
{
current.setAbortAction(true);
}

I hope it definitely helps you, if so please mark my response as both helpful and correct.

Jan Cernocky · ‎02-17-2022

Hi,

I was doing similar thing the other day. Here is what I did - using your values (the fields may differ a little bit based on your actual values).

(function executeRule(current, previous /*null when async*/) {
    
	var exceptionGR = new GlideRecord('x_snc_aob_test1_request_baseline_input');
	exceptionGR.addQuery('account_name',current.account_name);
	exceptionGR.addQuery('onsite_offsite', current.onsite_offsite);
	exceptionGR.setLimit(1);
	exceptionGR.query();
	if (exceptionGR.next()) {
		
		var URL = exceptionGR.getLink(true);		
        var msgText = 'Record with similar account and offshore already exists ';
        msgText +=  '<a href=\'' + URL + '\' target=\'_blank\'>HERE</a>'			
				
		gs.addErrorMessage(msgText);		
		
		current.setAbortAction(true);		
	}		

})(current, previous);

Nice feature is to use the getLink to point user to existing record.