Blank records created during transform map

[Nestor Padilla1]

Hello Experts

I have research to avoid inserting a record into the target table if X field is empty in the source record.

For example, in the source record, there is a group ID field and several other fields. The group ID is the coalese field (it has the sys_id).

Whithin those I have two fields that I validate as follows:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here

    if (source.u_field1_name.nil() && source.u_field2_name.nil()) {
       ignore = true;
    }

})(source, map, log, target);

The idea is that IF those two fields are empty, the record is NOT inserted into the TARGET table.

But, what actually happens is that the record is CREATED, just ALL the fields in the TARGET are empty for the matching criteria set in the script (all other records that do not match are created normally as expected).

See the screenshot:

I mean, even the Group ID ends up blank for the records that match the script criteria. What I really need is that those records are NOT inserted in the Target table.

I have not been able to accomplish this. Your help will be greatly appreciated.

[Maik Skoddow]

Hi

can you please wrap your code in a try-catch-block to see whether any code issue lead to that result:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
    try {
      if (source.u_field1_name.nil() && source.u_field2_name.nil()) {
         ignore = true;
      }
    }
    catch (e) {
         gs.error(e);

         ignore = true;    
    }

})(source, map, log, target);

Maik

[Nestor Padilla1]

Thank you

No errors, yet, another script that runs ON START did not run. That script will set all records in the target table to false and that works OK (up until this error catch wrapping).

[Narsing1]

Hi,

Please try like this. Also, try with OnBefore script.

Other way is make u_field1_name and u_field2_name as coalesce = true so that you don't need to provide any condition.

if (JSUtil.nil(source.u_field1_name) && JSUtil.nil(source.u_field2_name)) {
       ignore = true;
    }

Thanks,

Narsing

[Nestor Padilla1]

Hi Narsing,

Thank you so much for looking at this.

I considered that, but, sometimes field1 will be blank while field2 will have data or vice versa, I don't want to skip those records, ONLY when BOTH are empty I need to skip the insert.

I came across an onComplete script that will DELETE those records after the transform finishes, but I know it is not the best solution. I should be able to skip the INSERT when both fields are empty and, based on all the links I have read, that should do it. Anyway, I will give it a shot to your idea.