Example conditional coalesce scripts

Review examples of conditional coalesce scripts.

Updates only

To only update records where a match is found, and skip records where a match is not found, specify a coalesce field and add the following script as an onBefore script to the transform map.

if (action == 'insert') 
  ignore = true;

Conditional coalesce using dot-walking

You can use dot-walked fields in a conditional coalesce script, such as to match the email address of a user when importing incident data.

In this example, this script is defined in the Source script of a field map for the Incident target table sys_id field.

var gr = new GlideRecord('incident');
gr.addQuery('caller_id.email', source.u_email); //check if the incident caller's email matches the import row email value
gr.query();

if(gr.next())
{
    answer = gr.sys_id; //if a match exists, return the sys_id of the matching Incident record
}
else
{
    answer= -1;
}

Conditional coalesce with an OR condition

You can use a conditional coalesce script to match source and target records based on multiple field values. Unlike multiple-field coalesce where all coalesce fields must match, you can specify a script to only require one of the fields to match.

In this example, this script is defined in the Source script of a field map for the User target table sys_id field.

var gr = new GlideRecord('sys_user');
var qc = gr.addQuery('email', source.u_email); //first check if the user's email matches
qc.addOrCondition('user_name', source.u_name); //alternatively, check if the username matches
gr.query();

if(gr.next()) 
{
    answer = grUser.sys_id; //if a match exists, return the sys_id of the matching User record
}
else 
{
    answer = -1;
}