onComplete transform script is not working as expected

[d_kush]

Hi,

I am working on Helsinki version of Service Now.

I have a scheduled job which imports data using REST message from some URL and stores in a staging table.

Then I am using a transform map to transform this data into target table.

Here is the issue, I need to delete records from target table which have not been updated from this import.

For this I have given an onComplete() transform form script which should delete unupdated data based on some filter query. but this script is not working as expected. As my understanding it should delete the data matching to filter condition after all the data has been transformed from staging to target table.

inplace of this, this script is deleting data before transformation.

I don't know what is going wrong with this script.

[d_kush]

I tried both if and while.. but same response..

[bernyalvarado]

Right. That's a sign that your records most probably are deleted by something else... for instance, a business rule, acl, etc.. Either that or your import/transform is running multiple times on a very short period of time causing anything older than 2 hours to be deleted.

Thanks,

Berny

[josh_nerius]

Hi Deepti,

The first thing we need to do at this point is determine if your query is returning any records. Add an 'else' clause and some logging statements so we can determine where this is failing. Example:

var rec = new GlideRecord('cmdb_rel_ci');
rec.addEncodedQuery('u_source=Asset Insight^sys_updated_on<javascript:gs.hoursAgo(2)');
rec.query();
if(rec.next()) {
       gs.info('>>> Found Records in cmdb_rel_ci'); 
       rec.deleteRecord();
} else {
       gs.info('>>> Did NOT Find Records in cmdb_rel_ci');
}

If your query is returning records, the second thing you need to do is adjust the deletion logic. rec.deleteRecord() will only delete a single record, and is not what you want. As someone else mentioned, you can change the 'if' to a 'while', but there's another method called deleteMultiple() that you could use here. As with any script that deletes records, be very careful and make sure your query is returning what you expect before running any kind of mass-delete

Example script using deleteMultiple (untested):

var rec = new GlideRecord('cmdb_rel_ci');
rec.addEncodedQuery('u_source=Asset Insight^sys_updated_on<javascript:gs.hoursAgo(2)');
rec.deleteMultiple();

This will delete all records that match the query specified in line 2.

[bernyalvarado]

In regards the u_source, make sure you're using the value of the field within your encoded query and not its Display Value.

Thanks,

Berny

[d_kush]

Hi Josh and Berny,

Thanks for replying.

I am getting records which I want through that condition, but i think issue is not with the script, script is deleting data before it is transformed to target table, but onComplete should delete data after transformation .