Can i change the source field Value using onBefore Transform Script?

RamjiP611320747
Tera Contributor

3 weeks ago

1. I have a scenario where i have to remove field value which contains "not availabe" to transformed as empty, source script for every field could not be the best scenario.

2. i wanted to change the source field itself from "not available" to "", can i do this using onBefore Transform Script.

function removeNotAvailable() {
        var naLookup = {
            'NA': true,
            'N/A': true,
            'NOT AVAILABLE': true
        };
        var sourceGr = new GlideRecord("u_test_supplier_data");
        sourceGr.get(source.sys_id);
        gs.info("Updated source field: "+ source.sys_id)
 
        var value = (source.u_diversity_status_temporary).trim().toUpperCase();
       
 
        if (naLookup[value]) {
            gs.info("FOUND THE NOT AVAIALBLE: " + value)
            sourceGr.u_diversity_status_temporary = "hello";
            sourceGr.update();  
            gs.info("Updated source field: "+ sourceGr.u_diversity_status_temporary)
        }
    }
 
    removeNotAvailable();
0 Helpfuls
  • 833 Views
7 REPLIES 7

yashkamde
Giga Sage
In response to RamjiP611320747

3 weeks ago

might be field maps are applied to target first, then onBefore runs, then the target is inserted or updated.

 

So try Cleaning the target instead.

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

    var NA = { 'NA': true, 'N/A': true, 'NOT AVAILABLE': true };

    var fields = (typeof target.getFields === 'function') ? target.getFields() : target.getElements();
    var len = fields.size ? fields.size() : fields.length;

    for (var i = 0; i < len; i++) {
        var el = fields.size ? fields.get(i) : fields[i];
        var name = el.getName();

        if (name.indexOf('sys_') === 0) continue; // skip system fields

        var val = target.getValue(name);
        if (val && NA[String(val).trim().toUpperCase()]) {
            target.setValue(name, '');
        }
    }

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

 

If my response helped mark as helpful and accept the solution.

0 Helpfuls

palanikumar
Giga Sage

3 weeks ago

Hi,

Like everyone suggested, It is recommended not to update the Import set table values.

If you have a specific requirement to update this table then I recommend to use onStart script and update all the records so your transform map will have updated records while processing.

You can use the below script as onStart transform map script.

Note: The updatemultiple function may not work in certain cases, if it is not working for you then convert to while loop and use update() function as mentioned in the comments

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    // Get the import set id. This will help to filter only the import set records of current execution
    var currentIsetSysId = import_set.sys_id;
    var sourceGr = new GlideRecord("u_test_supplier_data");
    // This condition will narrow down the the import set records to use only records of current execution
    sourceGr.addQuery("sys_import_set", currentIsetSysId);
    // this is case insensitive. So no need to convert the case
    sourceGr.addQuery("u_diversity_status_temporary", "IN", "NA,N/A,NOT AVAILABLE");
    sourceGr.query();
    // Use updatemultiple to do optimize the code. In case this is not working, you can convert to while loop and update records individually using update() command
    sourceGr.setValue("u_diversity_status_temporary", "");
    sourceGr.updateMultiple();
})(source, map, log, target);

 

Thank you,
Palani
0 Helpfuls

DrissAO
Giga Contributor

3 weeks ago

Hi @RamjiP611320747 ,

The approach from @Yash9 is correct: source.setValue() in an onBefore script works. Here's what to check, since it still looks like it's failing:

 

1. Check the target table, not the import set table.
source.setValue() changes the value in memory only, during the transform. The staging row in u_test_supplier_data will always still show "Not Available". Open the target records to check the result.

 

2. Enable "Copy empty fields" on the Transform Map.
Without it, a blank source value does not overwrite an existing value on the target. That means updated records keep the old value.

 

3. Make sure nothing overrides the value.

  • The onBefore script is Active and is on the correct Transform Map.
  • The Field Map for that column has no Source Script (it would read the raw value).
  • The field isn't a coalesce field.

4. Use this script. It also handles hidden characters from Excel and logs what it changes:

 

(function runTransformScript(source, map, log, target) {
    var NA = { 'NA': true, 'N/A': true, 'NOT AVAILABLE': true };

    var els = source.getElements();
    var len = els.size ? els.size() : els.length;

    for (var i = 0; i < len; i++) {
        var name = String((els.get ? els.get(i) : els[i]).getName());
        if (name.indexOf('u_') !== 0) continue;

        var val = source.getValue(name);
        if (!val) continue;

        var norm = String(val).replace(/[\s\u00A0]+/g, ' ').trim().toUpperCase();
        if (NA[norm]) {
            source.setValue(name, '');
            log.info('Cleared ' + name + ' (was "' + val + '")');
        }
    }
})(source, map, log, target);

 

5. Test.
Run the transform, then check:

  • The Import Log for the "Cleared …" lines. If there are no lines, the values didn't match.
  • The target records.

Hope this helps!

0 Helpfuls