Script Include Best Practice

[wdvorak]

I'm running an onStart script include for a Transform Map to determine first, the number of records in an Import Set and second, if a particular field is empty (null) for a threshold check.  I was first calling a GlideRecord for the ISET to get the rowCount(), then adding a criteria for addNotNullQuery and issuing another query() and checking the getRowCount() again.   Is this okay?  Should I do a separate GlideRecord query() for each getRowCount()?  Or is there a way to reset the criteria of a GlideRecord query?

Example code;

NetMRI.thresholdCheck = function() {
var updateThreshold = gs.getProperty('netmri.update.threshold');

var nut = new GlideRecord('u_netmri_interfaces_import');
nut.addQuery('sys_import_set.number', import_set.number);
nut.query();
var isetCount = nut.getRowCount();
logIt('Import Set = ' + import_set.number + ', and Count = ' + isetCount);

nut.addNotNullQuery('u_cable_id');
nut.query() ;

var nutCount = nut.getRowCount() ;
logIt('Cable ID row count = ' + nutCount);

if (nutCount < updateThreshold) {
logIt('NetMRI_onStart- import set Cable ID count is less than threshold ... abort transform');
error = true;
}
};

[Jace Benson]

getRowCount should be avoided.  In its place you should use a GlideAggregate.  The code will be longer but will be much more efficient.

[wdvorak]

Yes, another developer for our group has also heard this.  But the questions still stands (with revised code); okay as is?  two separate Glides/vars?  a way to reset a Glide reference?;

NetMRI.thresholdCheck = function() {
var updateThreshold = gs.getProperty('netmri.update.threshold');

var nut = new GlideAggregate('u_netmri_interfaces_import');
nut.addQuery('sys_import_set.number', import_set.number);
nut.addAggregate("COUNT");
nut.query();
nut.next();

var isetCount = nut.getAggregate("COUNT");
logIt('Import Set = ' + import_set.number + ', and Count = ' + isetCount);

nut.addNotNullQuery('u_cable_id');
nut.query() ;
nut.next();

var nutCount = nut.getAggregate("COUNT");
logIt('Cable ID row count = ' + nutCount);

if (nutCount < updateThreshold) {
logIt('NetMRI_onStart- import set Cable ID count is less than threshold ... abort transform');
error = true;
}
};

[Shishir Srivast · Accepted solution]

I think, you script should be like below,

NetMRI.thresholdCheck = function() {

var updateThreshold = gs.getProperty('netmri.update.threshold');
var nut = new GlideAggregate('u_netmri_interfaces_import');
nut.addQuery('sys_import_set.number', import_set.number);
nut.addAggregate("COUNT");
nut.query();
if(nut.next()){

var isetCount = nut.getAggregate("COUNT");
logIt('Import Set = ' + import_set.number + ', and Count = ' + isetCount);

var nut1 = new GlideAggregate('u_netmri_interfaces_import');
nut1.addNotNullQuery('u_cable_id');
nut1.addAggregate("COUNT");
nut1.query() ;
if(nut1.next()){

var nutCount = nut1.getAggregate("COUNT");
logIt('Cable ID row count = ' + nutCount);

if (nutCount < updateThreshold) {
logIt('NetMRI_onStart- import set Cable ID count is less than threshold ... abort transform');
error = true;
}
}
}
};

But why are you trying to get the Count of Import Set records? If you do not need that then you can just the GlideAggregate() to get the count of records where u_cable_is not null.

[wdvorak]

If the database I'm importing to contains 100,000 records, I may want to change the behavior of the (transform) import depending on; if the import sets contains approximately 100k records, or less than 1k.  An import with 1K records and no cable ids maybe an update to remove the cable ids from those matching records, but a 100K record import with no cable ids (or less than whatever I set as the threshold) is bad data, and the import needs to abort.

I see by your using two GlideAggregate calls to get both totals, I take it that that is preferred and to not first poll a GlideAggregate for a count then add a filter (addNotNullQuery), and poll the same GlideAggregate for the second count.

My code above is a simplified example of one piece of a transform import that runs automatically nightly.  Normally, the whole of the record set is imported each night and the records in the database are updated accordingly, and once in the past, the data file contained no cable ids (bad data), thus the (new) onStart threshold check.

The threshold check is to prevent the import of bad data, but the same transform could be used to import data sets manually.  Maybe I manually import a data set with 10K new records that have no cable ids -- I would create another system property that determines whether the threshold check is used or not (true/false), and alter the onStart behavior accordingly (don't abort but make sure these are new records and not updates).  Maybe I have two threshold limits; one that indicates bad data, and another that indicates the records with no cable ids are going into a "Withdrawn" state, and above the second threshold any records with no Cable IDs are corrections and their states goes to "Available".  (The second threshold would most likely be based on the total import set count, and probably represent a percentage of the whole.)  After the initial threshold check in the onStart, the behavior in the onBefore or onAfter scripts could be altered depending upon the numbers that appear in the onStart.