How to make a script-populated field (software_model) act as a coalesce field in Transform Map

[Supritha Jose]

Hi all,

I’m working on a Transform Map to load data into the Software Entitlement table, and I need help with making a script-populated field (software_model) act as the coalesce field.

Here’s my scenario:

• I’m importing data from a spreadsheet that contains Publisher, Product, and a few other fields that exist on the Software Entitlement table.
• I already used the Publisher and Product data to create Software Models.
• The Software Model field on the Software Entitlement table references the Software Product Model table and uses the Display Name (a combination of Publisher + Product).
• Since my import file only has separate Publisher and Product columns, I used a Transform Script to query the cmdb_software_product_model table and set the software_model field dynamically.
• This part works fine — the script correctly populates the software_model field during import.
• However, I now want to make the software_model field act as the coalesce field, so that:
• If a matching record already exists, it should update it.
• If no match is found, it should skip/ignore (not insert a new record).
• The challenge is that since software_model is being set in the script (not directly mapped in the Transform Map), I’m not sure how to make it act as the coalesce key.

Can anyone suggest the best approach to achieve this — so that I can use a script-defined field as coalesce, and update existing entitlement records without inserting new ones?

[Suneesh]

Hi @Supritha Jose ,
You can use below script in transform map.
Best practices to be followed are as follows:

• Reference Fields: If the software_model field is a reference, your script should set the sys_id.
• Performance: Complex coalesce logic in scripts can affect performance—test with real data.
• Debugging: Use logs (gs.info) to validate your script.

(function transformEntry(source) {
    var model = new GlideRecord('cmdb_software_product_model');
    model.addQuery('name', source.model_name);
    model.addQuery('manufacturer', source.manufacturer);
    model.query();
    if (model.next()) {
        target.software_model = model.sys_id.toString();
    } else {
        target.software_model = '';
    }
})();

Please mark my answer correct and helpful if this works for you

Thanks and Regards,
Suneesh.