Coalesce on 3 different field in Transform maps

[Suvetha S]

Hi all,

We are importing data into cmdb hardware table through transform maps. We want the coalesce field to be set on 3 different fields. Serial Number, Name & IP address. If serial number is  empty, it should look for Name, if Name is empty it should look for IP Address. Please help with the script. 

[Weird]

Create a transform "Field Map" with "Use source script" set to true and "Target field" to sys_id.
Then you can just create a regular GlideRecord script looking for your record.
For example:

var cmdb = new GlideRecord('cmdb_ci');
if(source.serial_number != ''){
cmdb.addQuery('serial_number', source.serial_number);
}else if(source.name != ''){
cmdb.addQuery('name', source.name);
}else if{
cmdb.addQuery('ip_address', source.ip_address);
}else{
cmdb.addQuery("sys_id", "somethingsomethign");
}
cmdb.query();
if(cmdb.isValidRecord()){
return cmdb.sys_id.toString();
}else{
return -1;
}

Basically this simply queries the cmdb table for serial number if it's set in the source. If not, it'll try name and if that's missing as well then it'll try the ip address.
If none are found, we instead just set the query to look for something that doesn't exist. That way after query we can just check if a record is found and return that sys_id as coalesce. If none are found then we return -1 which should be considered new.

Note that I just wrote this from the top of my head, so I didn't test it and you'll have to give it a try first with some test data.

[Upsilon]

Hi, 

You have to use the sys_id for the coalesce. Try something like this example

answer = (function transformEntry(source) {

    // Add your code here
    var gr = new GlideRecord('cmdb_Table_name');
    if (gr.get("serial_number",source.serial_number) && (source.serial_number!=""))
        return gr.sys_id.toString(); // return the value to be put into the target field
    else

    if (gr.get("name", source.name) && (source.name!=""))
        return gr.sys_id.toString();
    else
    if (gr.get("ip_address", source.ip) && (source.ip!=""))
        return gr.sys_id.toString();

})(source);

I hope it help

Regards

[Suvetha S]

Hi @Upsilon The above script is not working.. its inserting the record

[Upsilon]

Hello, 

If you want to restrict your transform map to upadates, add an onBefore script where you ignore inserting record something like this: 

 if(action == "insert") { ignore = true;

log.warn("Your error message")}

Regards