Business Rule to create a record on custom table if a reocrd doesnt alredy exist

jonathangilbert
Kilo Sage

Hi community.

 

I am working on an enhancement to business rule script that creates an entry on a custom table called "u_audit" when a record is viewed. The problem is, when the record is refreshed after making an update it adds another entry to the custom table, which I dont want.  What I was thinking is to put an additionl query into the business rule to look up if there is an record for the logged in user and the record being viewed that was created in the last 5 mins. If there isnt then the business rule should create a new entry, but if the script found an existing record, it should do nothing.

 

This is the standard business rule (on display) that creates an entry every time the record is viewed:-

 

var auditRecord = new GlideRecord("u_audit");
auditRecord.initialize();
auditRecord.u_username = gs.getUserID();
auditRecord.u_viewedrecord = current.getUniqueValue();
gs.log("audit record" + current.getUniqueValue());
auditRecord.u_parent = current.getUniqueValue();
var gdt = new GlideDateTime();
auditRecord.u_usertime.setDateNumericValue(gdt.getNumericValue());
auditRecord.insert();


}})(current, previous);
 
 
 
This is my revised script, but it doesnt create any entry at all:-
 
(function executeRule(current, previous /*null when async*/ ) {

var count = new GlideRecord ('u_audit');
count.addEncodedQuery('u_usernameDYNAMIC90d1921e5f510100a9ad2572f2b477fe^sys_created_on<javascript&colon;gs.beginningOfLast15Minutes()');
count.addAggregate('count');
count.query();
if(duplicate.next()){
var countfinal = count.getAggregate('count');
}


if (countfinal<1) {


var auditRecord = new GlideRecord("u_audit");
auditRecord.initialize();
auditRecord.u_username = gs.getUserID();
auditRecord.u_viewedrecord = current.getUniqueValue();
gs.log("audit record" + current.getUniqueValue());
auditRecord.u_parent = current.getUniqueValue();
var gdt = new GlideDateTime();
auditRecord.u_usertime.setDateNumericValue(gdt.getNumericValue());
auditRecord.insert();


}})(current, previous);
 
 
 
Can anyone explain where I am going wrong?
 
Many Thanks
1 REPLY 1

Brad Bowman
Kilo Patron
Kilo Patron

Add some logs to see what is happening throughout the script.  I'm guessing countfinal is null, if it's making it that far.  You are probably going to want to match u_username with you are creating on the records, aggregates are always in ALLCAPS, and you are using an incorrect GR name with the next() method, so this will get you closer: 

 

var count = new GlideRecord ('u_audit');
count.addEncodedQuery('u_username=' + gs.getUserID() + '^sys_created_on<javascript&colon;gs.beginningOfLast15Minutes()');
count.addAggregate('COUNT');
count.query();
if (count.next()) {
    gs.info('GA script: in record');
    var countfinal = count.getAggregate('COUNT');
}
gs.info('GA script: countfinal = ' + countfinal);