duplicate checking for multiple fields using business rule

[Kajal16]

Here i want to avoid duplicate values  for six fields using business rule and  applied this code .Here for one field it is working correctly but for two or more than that it is not working. It simply takes the values in table and also showing" invalid insert message" it shows this message and also gets inserted in the table 

Here is the code that i applied 

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

// Add your code here
var grAst = new GlideRecord('tablename');

grAst.addQuery('name', current.name);
grAst.addQuery('roll', current.roll);
grAst.addQuery('address', current.address);
grAst.addQuery('city', current.city);
grAst.addQuery('school', current.school);
grAst.addQuery('pincode', current.pincode);
grAst.setLimit(1);

grAst.query();

if (grAst.hasNext() && grAst.sys_id != current.sys_id)
{
gs.addErrorMessage("Serial number already exists");

current.setAbortAction(true);

}

[Ankur Bawiskar]

Hi,

Can you update code as below and check once

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

// Add your code here
var grAst = new GlideRecord('tablename');

grAst.addQuery('name', current.name);
grAst.addQuery('roll', current.roll);
grAst.addQuery('address', current.address);
grAst.addQuery('city', current.city);
grAst.addQuery('school', current.school);
grAst.addQuery('pincode', current.pincode);
grAst.setLimit(1);

grAst.query();
if(grAst.next())
{
if(grAst.sys_id != current.sys_id){
gs.addErrorMessage("Serial number already exists");
current.setAbortAction(true);
}
}

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Ronak3]

Hi Ankur,

This one worked for insert but not for Update. Since my record contains a field "Status" which contains Draft, published values. When I open an existing record and change the status from published to Draft (which will enable editing fields) , I can select a field value which already exists and can save. How can I avoid this? 

Can I achieve this with before insert/update business rule or do I need to create one for before insert and another for before update?

[The SN Nerd]

When you say it doesn't work on update, can you elaborate?

With setAbortAction, when it returns to the record, the fields your changed still show on the form but the record hasn't been changed.

You might want to add this line before current.setAbortAction

current.setValue('state',previous.getValue('state'));

[Ronak3]

Hi Paul,

So when I try to insert a new record with same three field values it is aborting to insert. Which is working as expected. But when I open an existing record and change the state to draft and try to change one field value to something that already exists with the combination of same other two field values,

Ex. Record1: req no. = 1213, code no.= 6567, group=1

      Record2: req no. = 1213, code no.= 6766, group=1

So now if I change the Record2 code no. to 6567 it is allowing to update which should not happen as there is an existing combination of record. I do have other field on the form but these three fields are important to avoid duplication.