If you are planning to import thousands of records into your instance and you have a complex coalesce key to update data, this post is for you. Easy import, data load, and import sets are wonderfully designed to import data into your instance.
ServiceNow uses two steps to import data:
- Loading
- Transforming
Data import is crafted to a very high specification, where the loading happens on the Data Sources while the transforming happens on the Transformation maps. Each execution is controlled by an Import set that displays the history of the data imported. Transformations maps can have coalesce field (keys to avoid duplicates) to allow them to update records.
I will focus on showing an example of a transformation map with a "complex key" (more than one field as coalesce) to update the target records which also avoid duplicates from reference fields (see below) and make one just one query (instead of multiple internal queries if selecting multiple coalesce fields).
On a transformation map, one, or several, 'coalesce' fields define when a record is updated. Whilst the transformation maps are flexible and configurable, when using complex "keys" some transformations are better with a "field map script" as coalesce (aka "conditional coalesce").
A few notes on coalesce fields:
- Coalesce field searches benefit from indexes on the target data field they are mapped to.
- sys_id is indexed on all tables, making searches faster if they are used for mappings.
- Using a reference field as coalesce can cause duplicates if the referenced data has duplicates (see below).
- Setting multiple fields as coalesce, they could cause multiple queries for each of the coalesce fields on the target data, increasing import times.
On my example, I will use the alm_stock_rule table. To the untrained eye, you would think it contains only strings, and integers.
A closer look at the alm_stock_rule table show the fields Model, Parent stockroom, and Stockroom are references to another table data (Reference fields). Reference fields store a sys_id for each referenced record in the database, but the sys_id is not shown. The reference field shows the display value.
(empty) or blank does not means the reference field is empty. It could be that the reference field display value is (empty) or blank. Always validate this by reviewing if it contains a sys_id value or not on the record itself e.g. Review the XML data of the record.
Coalesce using one-to-one field mapping on the transformation map
On reference fields, you can import data using the sys_id of the target 'referenced' data. However, most times, you would like to import data into "alm_stock_rule", using the display value instead to match the existing records.
For this example, we would use "Stockroom", "Model", "Parent stockroom", "restocking option" as key for updates.
On this transformation map, we would define the "Stockroom", "Model", "Parent stockroom", "restocking option" fields with coalesce "true"
PROS | CONS | |
It is very configurable per field | You have no control on the final searches performed to match the coalesce fields values to the target data. This means that more than one search could be triggered. Worst case scenario is that more than one per each coalesce fields may be triggered. | |
It is easy to understand | If some of the coalesce fields source data is empty, it can trigger a query for (field=NULL) and the remaining coalesce fields which is unlikely to follow the indexes | |
No scripting is required | It depends on the field mapping options available | |
You can map more than the display value of the reference field by using "referenced value field name" | If some of the coalesce fields data holds very limited values (e.g. choice field) and the target table is very large, the query could be slow. e.g. you add impact as part of you coalesce fields, and your target table is incident. There is a case where query could be "select ... from incident where impact = 1" which could be a large query if you have a large incident table. | |
It is easier see which fields on the target table requires indexes (if the data is unique enough) | It could cause duplicates if reference fields are used as coalesce (see below) |
Duplicate records could appear if reference fields are used as coalesce.
Notes on coalesce on reference fields
In this example, the model we are importing is "APC 42U 3100 SP2 NetShelter." I have created two records on the model referenced table (it is not the target table itself but the 'Product Model' table which is referenced by 'model'). As this happens, the coalesce fields will match two, then the import will create a new unwanted record instead of updating it. This is a common problem as not all tables holds unique values.
On the import set, those records will show as State = Inserted when it should show ignored or updated
Using a reference field as coalesce can cause duplicates if the referenced data has duplicates
Coalesce on field map scripts
An alternative coalesce would be a "Script" mapping to the target "sys_id".
For this example, I will explain a technique of creating a simple coalesce field by field map script to the sys_id of the target. As sys_id have an index already, so the last search with the script result as coalesce is minimal. You would like to do this to have more flexibility on the final search generated to update your data.
When using a field map script, the previous example transformation map would look as follow:
Then set the field map script to match the sys_id on the target and make it the ONLY with coalesce = true.
On the field map script, add the script to find the correct target record:
Here is the script I used to find the target record:
answer = function(a) {
var list_to_compare=[["u_stockroom","stockroom.display_name"],
["u_parent_stockroom","parent_stockroom.display_name"],
["u_restocking_option","restocking_option"],
["u_model","model.display_name"]];
return findmatch(list_to_compare, source, map.target_table,false,true);
}(source);/* Function findmatch is use on transformation maps to find a match with multiple coalesce fields
vlist: list of fields to compare, Array = [[ "source_field","target_field"],...] Target field allows dot walk.
vsource: source record,
vtarget: target record,
nomatchcreate: true will create record if there is no match)
debugon: true will log the information about the matching results
Returns sys_id of the target record, or null if error or if nomatchcreate = false and no match is found.
Coalesce empty fields need to be OFF, so null answer (e.g on error), insert is cancelled
*/
function findmatch(vlist, vsource, vtarget, nomatchcreate, debugon) {
try {
vtarget = new GlideRecord(vtarget + "");
// Check the source fields coalesce has a value to add to the query
for (var h = vlist.length, c = 0; c < h; c++)
vsource[vlist[c][0]].hasValue() &&
vsource.isValidField(vlist[c][0]) &&
vtarget.addQuery(vlist[c][1], "=", vsource[vlist[c][0]].getDisplayValue());vtarget.setLimit(1);
vtarget.query();
var d;
vtarget.next() ?
// if we find a match, we return the sys_id, otherwise, if nomatchcreate = false returns null
(d = vtarget.sys_id, debugon && (log.info("source: " + vsource.sys_id + " - record match: " + d), vsource.sys_import_state_comment = "record match: " + d)) :
// If no match is found it validates whether a new sys_id is required
nomatchcreate ?
d = gs.generateGUID() :
(d = null, debugon && (log.info("source: " + vsource.sys_id + " - record match: None"), vsource.sys_import_state_comment = "record match: None"));
return d
} catch (f) {
return log.error("script error: " + f), vsource.sys_import_state_comment = "ERROR: " + f, null
}};
The script gives you flexibility to set the search that better meet your business requirements.
Ensure you set "coalesce empty field" unchecked (OFF), because if an error happens on the query or field script, it will return null, then it will ignore the record coalesce field is matching null
You can see this example is center the updates on only one query that depends on the values available.
After opening the data source and clicking on "Load All Records", then transforming them, the import set data will show as follow:
On the import set, the import set rows tab will show the records would match the correct value this time.
The imported data will insert the new record, and update the existing one, even when the referenced model has duplicated data, the field map script will match the right record.
Using the field map script, we know it will only execute ONE search on the target form, and allow you to define any query that identify uniquely your target record, giving you flexibility and increasing performance on updates.
I've tested using Helsinki, using Google chrome as the browser.
For more information on transforming your data see:
Video demos:
- Troubleshooting Truncated Data
- Troubleshooting Missing Data in Date Fields
- Troubleshooting ServiceNow Export Limits
Importing and Exporting data:
- Transforming your data: "Load All Records" vs. "Test Load 20 Records"
- Exporting Large Data Sets?
- Increasing the Export Limit
- Exporting large data sets into Excel
- How to make sure your import/export ship has sailed
- Troubleshooting an import that fails
- Troubleshooting an import that is taking a long time to complete
- Troubleshooting an import that does not complete or is missing data
- Troubleshooting export issues
- Troubleshooting issues with transform maps
- Import Sets
- Importing Data Using Import Sets
- Scheduling Data Imports
- Troubleshooting Import Set Performance
- Import & Export Resources
- Tips to improve the run time of imports
Transforming your data:
1 Comment
