Enforce unique record with multiple criteria

[Marcel H_]

I'm still relatively new to scripting and while learning pretty well from examples in my dev environment and from great information here, sometimes I hit some snags. Right now I'm trying to modify an OOB script to enforce unique account numbers on the itfm_gl_accounts table, but I want to expand the criteria to check multiple fields, since we have several LLCs that are under our umbrella for Finance, and they share similar account numbers, but there is a company and company code designator that would make them unique.

Below is the OOB script, and I'd like to also do a check in addition so that the account_number AND the u_company_code field together will determine if the value is unique.

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

// Add your code here
var gl = new GlideRecord('itfm_gl_accounts');
gl.addQuery('sys_id','!=',current.sys_id);
gl.addQuery('account_number',current.account_number);
gl.query();
if(gl.next()) {
  gs.addErrorMessage(gs.getMessage('Failed creating general ledger account, duplicate account'));
  current.setAbortAction(true);
}

})(current, previous);

[martygrinstead]

Hi Marcel,

It sounds like you can just add another "AND" condition to your query:

gl.addQuery('sys_id','!=',current.sys_id);

gl.addQuery('account_number',current.account_number);

gl.addQuery('u_company_code',current.u_company_code);

gl.query();

When you string multiple addQuery functions, they have the affect of "AND"ing the conditions.

Marty

[Marcel H_]

Thanks Marty. I tried that initially and it didn't work, still throwing errors on import/submit stating "Unique Key violation detected by database (Duplicate entry '6920' for key 'account_number')" even after adding the additional line in the script. I'm not sure why, or if there is another business rule in the system that tries to enforce a unique key as well (though I don't think so since it's specifically calling out the account_number field).

[martygrinstead · Accepted solution]

Hi Marcel,

The duplicate entry error is happening at the database layer, because the table has been configured in the dictionary to have a "unique" requirement on the "account_number" field.   Your script will detect if the combination of account number and company code is not unique, however, this will not affect what the dictionary will enforce.

If you navigate to the "Tables" module, then search for itfm_gl_accounts, under the "Dictionary Entries" related list, you can use the cog to modify the list, and add "Unique".   This will show that 1 field has the Unique attribute set to true.....Account_Number.

I'm not sure how you want to proceed from here, but the error is not from your script, but from the way the table is defined.   This is out-of-the-box (mine looks the same as what you are reporting).

[Marcel H_]

Thanks Marty, I knew I had seem something like that somewhere, and that's exactly what it was. I really appreciate the responses and helping me get over this hurdle.