Use Case:
The coalesce function establishes a unique identifier within the field mapping and aids in determining whether to create or update a record. Multiple coalesce functions can be utilized to support an 'AND' condition, facilitating validation. Guidance is needed on how to manage fields under an 'OR' condition to configure a coalesce for a single field.
Solution:
The mapping of the 'sys_id' field is essential for addressing this use case. It is necessary to choose the 'sys_id' field from the target table, while the 'Use Source Script' field facilitates the management of processes through the utilization of a script. The diagram below illustrates the handling of the import set table and outlines the configurations that need to be taken into account.
//First check with user id
var userID = new GlideRecord('sys_user'); // Target table: sys_user
userID.addQuery('active', true); // Optional: Only consider active users
userID.addQuery('user_name', source.u_user_name)
userID.query();
if (userID.next()) {
return userID.sys_id; // Return the matching record
}
//Second check with email id
var userEMAIL = new GlideRecord('sys_user'); // Target table: sys_user
userEMAIL.addQuery('active', true); // Optional: Only consider active users
userEMAIL.addQuery('user_name', source.u_email)
userEMAIL.query();
if (userEMAIL.next()) {
return userEMAIL.sys_id; // Return the matching record
}
return ' '; // No match found & creates a new record.
The diagram below illustrates three action items while omitting the more complex script. The straightforward configuration facilitates the on-boarding of major items into the import set table.
i. Coalesce - This feature allows for the definition of a unique identifier to create and update records.
ii. Choice Creation - This function enables the creation, updating, or ignoring of new or existing value validations. It is typically applicable to drop down field choices, such as the Language field from the sys_user table.
iii. Referenced Field Value Name - This feature pertains to reference fields and determines which field should be mapped, identifying whether the value is existing or new. If the value is new, it permits the creation of that entry.
ServiceNow enables the creation and configuration of multiple web service transform maps. It accommodates a single source table that can manage multiple target tables, adhering to specified sequences.
This procedure is relevant for the import of Configuration Management Database (CMDB) Configuration Items (CIs) and integration areas. The initial verification should be conducted using the Serial Number, followed by the Asset Tag, and finally the CI Names. Among these three criteria, only the one that matches will be managed in the 'Use Source Script' section.
#servicenow #procodeSolution #importsetTable #Coalesce #ORCoalesce
Hope you like it. If any questions or remarks, let me know!
If this helped you in any way, I would appreciate it if you hit bookmark or mark it as helpful.
Regards,
Suresh.
