Scheduled Script - Check For Duplicates???

[Mikolz]

Hi Everyone, is it possible to check for duplicate records in a scheduled script job? I am almost to the end of this time card project - there are just a few loose ends that need to be tied. Heres the scheduled script...

var string = "roles=itil^department=f8411c2f78a75c00910063ad31c0bf9c^active=true";

var GG = new GlideRecord('time_card');
var HH = new GlideRecord('sys_user');

HH.addEncodedQuery(string);
HH.query();

while(HH.next())
{
GG.initialize();
GG.week_starts_on.setValue(gs.nowNoTZ());
GG.user = HH.name.toString();
GG.u_total_hours = 0;
GG.state = 'Pending';
GG.insert();
}

Its creating a timecard every Sunday for 256 users. In order to prevent the system from creating duplicates (if a user created one ahead of time), I have the following business rule...

var gr = new GlideRecord("time_card");

       gr.addQuery("week_starts_on",current.week_starts_on);
       gr.addQuery("user",current.user);
       gr.addQuery("sys_id","!=",current.sys_id);
       gr.query();

           if (gr.next())
           {
                                   current.setAbortAction(true);
           }

This business rule is causing unexpected behaivor. If I run the script with it off, 256 are created. When it is on, if I manually create a card, then run the script, it wont create a duplicate card; however, it only creates 255. It skips a user. The user seems to be random each time. I am not sure what the exact issue is. I was hoping I could perform the check in the script. Any guidance is greately appreciated!

[geoffcox]

I was able to totally reproduce your problem on the Demo Site (It's still there on https://demo11.service-now.com/navpage.do).

We scratched our heads over this problem for a long time, and finally dug into the system logs. What we found is that it wasn't random at all. Every time you aborted on insert, the next insert (or two) failed also. The more interesting thing is that on the insert that gets aborted, the .insert() call returns null (lower case). The next insert also returned null (lower case). But the 3rd one always returned NULL (upper case).

I think the right answer is to test for the duplication in your script that creates the time cards, rather than blocking the creation with an abort in a business rule. In otherwords, .setAbortAction(true) is not a healthy operation.

However, just for fun, I changed the code to brute force the insert by having it try the insert up to 10 times until it worked. That did solve the problem.


do_create_time_card();
function do_create_time_card() {
var GG = new GlideRecord('u_time_card');
var HH = new GlideRecord('sys_user');
HH.orderBy('name');
HH.query();
var x = 1;
while(HH.next()) {
if (HH.department.getDisplayValue() == 'HR') {
GG.initialize();
GG.u_week_starts_on.setValue(gs.nowNoTZ());
GG.u_user = HH.name.toString();
GG.u_user_number = x.toString();
GG.u_total_hours = 0;
GG.u_state = 'Pending';
var y = 0;
var sid = 'null';
while (((sid == 'null') || (sid == 'NULL') || (sid == null)) && (y++ < 10)) {
sid = GG.insert();
gs.log('GBC: Creating record for ' + x + '/' + y + ': ' +HH.name.toString() + ', response = ' + sid);
}
x++;
}
}
}


[Mikolz]

I really appreciate the response. Its funny becuase I have had some odd behaivor with current.setAbortAction() before this. I have since then tried to do the check in the scheduled script itself, but have yet to figure it out. I have gotten an infinite loop on some occassions. Would you mind possibly leading me in the right direction? Again, I really appreciate the effort you put into figuring that out.

What concerns me is that, I originally ran the script with 0 records in the time_card table. I have released the pilot to certain individuals and the second week of creation, instead of 1 being skipped, there was 3 or 4. I worry about with the increasing amount of entires in the table each week, will each weeks creation yield less cards being created?

[geoffcox]

This code would seem to achieve the desired result (and is currently available on demo11):

do_create_time_card();
function do_create_time_card() {
   var GG;
   var HH = new GlideRecord('sys_user');
   HH.orderBy('name');
   HH.query();
   var x = 1;
   while(HH.next()) {
      if (HH.department.getDisplayValue() == 'HR') {
         GG = new GlideRecord('u_time_card');
         GG.addQuery('u_week_starts_on',gs.nowNoTZ());
         GG.addQuery('u_user',HH.name.toString());
         GG.query();
         if (!GG.next()) {
    GG = new GlideRecord('u_time_card');
            GG.initialize();
            GG.u_week_starts_on.setValue(gs.nowNoTZ());
            GG.u_user = HH.name.toString();
            GG.u_total_hours = 0;
            GG.u_state = 'Pending';
            sid = GG.insert();
            gs.log('GBC: Creating record for ' + x + ': ' +HH.name.toString() + ', response = ' + sid);
            x++;
         }
      }
   }
}

[Mikolz]

Thanks for your help. I had to make a small modification since the nature of the fields in the demo instance were different from mine. instead of the following

GG.addQuery('u_user',HH.name.toString());

I used the following

GG.addQuery('user', HH.sys_id);

Since my user field on the time card is a reference and I have users from AD that have the same names (different user ID's), so the sys_id will be the best unique piece of information. I think I am going to take this a step further and combine a business rule that is populating the card with the pre-defined tasks that we have layed out. Thanks again!.