How to write On Before Transform script to replace staging table field value with sys id?

[Ankita Kolhe]

Hi Community,

I want to load below record to staging table but before transforming this data to target table, I want to replace sub region value to sys id as Sub Region is reference field which referencing to region table.

I wrote below On Before Transform Script but not sure if it will work:-

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

    // Add your code here

    var region = new GlideRecord("x_kpm79_kpmgi_iogc_kpmgi_iogc_region");

region.query();

while(region.next()){

    if(region.sub_region.getDisplayValue()==source.u_sub_region){

        source.u_sub_region=region.sub_region.toString();

    }

}

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

Could someone please help on this?

Thanks,

Ankita

[Allen Andreas]

Hi,

Is there a reason you must do this before the transformation?

Instead, you could just handle this per the row as they're transforming and address this in your field map itself, just select your source and target fields and then use script and use something similar. Your GlideRecord query, right now, is not efficient as it pulls every single record from that table, then iterates through them looking for the display value to match your source. So, you could use what you have in your onBefore, but Instead, in transformation, you'd want to narrow down the possible records by addQuery to it and searching for the specific data like:

region.addQuery('name', source.u_sub_region);