COALESCE fields in transform maps

[indrajeetma]

So I have a requirement where in the sheet there multiple columns I am taking 3 columns here for that I need Help
1. name

2. site ID

3. company
In transform map I have selected COALESCE true for site ID and company field this is check at the start if both are matched for other record exisiting it would update it

But other than that after if site ID and company field is not matched we want to check name and company field if both are matched then also the exisiting record should be updated? how can I add this logic

[indrajeetma]

answer = (function transformEntry(source) {

    var compSysId = '';

    var shortDescription = source.sys_import_set.short_description;

    var jsonMatch = shortDescription.match(/{.*}/);

    if (jsonMatch) {

        var companyId = JSON.parse(jsonMatch[0]).Company;

        compSysId = companyId;

    }

    var locGR = new GlideRecord('cmn_location');

    locGR.addQuery('u_site_id', source.u_site_id);

    locGR.addQuery('company', compSysId);

    if (locGR.next()) {

        return locGR.getUniqueValue();

    } else {

        var locGR1 = new GlideRecord('cmn_location');

        locGR1.addQuery('name', source.u_name);

        locGR1.addQuery('company', compSysId);

        if (locGR1.next()) {

            return locGR1.getUniqueValue();

        }

    }

    return ""; // return the value to be put into the target field

})(source);
I have tried this but its not even inserting records

[Fabio Fernandes]

I think the issue is related with the company ID. the source.sys_import_set.short_description returns empty. if the company id is a value in the document, I think you are getting it in the wrong way. 

[Shivanya Velmur]

Hi @indrajeetma 

You can’t set two coalesce combinations in one transform map, but you can handle it with an onBefore Transform Script.

Try this:

(function transformEntry(source, target, map, log, isUpdate) {
// Check Site ID + Company first
var rec = new GlideRecord('your_target_table');
rec.addQuery('site_id', source.u_site_id);
rec.addQuery('company', source.u_company);
rec.query();

if (!rec.next()) {
// If not found, check Name + Company
var alt = new GlideRecord('your_target_table');
alt.addQuery('name', source.u_name);
alt.addQuery('company', source.u_company);
alt.query();

if (alt.next())
target.sys_id = alt.sys_id; // Update existing record
}
})();

 This lets the import first match on Site ID + Company, and if not found, it falls back to Name + Company before inserting a new record.