Transform script is not working

[Venkataramudu A]

Hello,

I have created transform map to load data into target table. before loading doing some kind of validation in control table. while executing scheduled file import, throwing below error message. can someone suggest any code fix.

Thanks in advance.

transform script:

(function transformRow(source, target, map, log, isUpdate) {

var number = source.u_number;
var control = new GlideRecord('sn_compliance_control');
control.addEncodedQuery('number=' + number + '^state!=retired');
control.query();
if (!control.next()) {
source.import_error = "Import details:" + 'no control ID/ incorrect control ID or control ID is retired:' + number;
log.warn('For Control Number : ' + number + ' cannot find any corresponding control in non Retire State');
ignore = true;
}

})(source, target, map, log, action === "update");

Import Error: 

Import details:no control ID/ incorrect control ID or control ID is retired:

Note: Source file and target table having proper control numbers

[Robbie]

Hi @Venkataramudu A,

On first review, the code and syntax looks correct.

Have you tested the same code via a bacgroudn script to ensure all works ok.

You may also want to ensure you print out and check the source number data (source.u_number).

The use of perhaps a .trim() method may help in case the source data has unintended spaces etc.

Eg: var number = source.u_number.trim();

or 

var sourceNumber = source.u_number;

var number = sourceNumber.trim;

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Kudos.

Thanks, Robbie

[Dexter Parre_o]

Hi @Venkataramudu A,

Since you are testing whether there are records returned by the query or not, can you try changing the line below:

From: if (!control.next()) {

To: if (!control.hasNext()) {

Also, if you are loading (not updating) data, you don't need to specify the number since it will be automatically generated when records are inserted into the table. There's the risk of numbers not being in sequence or having duplicate numbers (if someone is creating a new record simultaneously) if you do that.

Let me know if this helps. Thanks.