NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
Within ServiceNow, there are many tables that contain "condition" or "template value" columns. In doing data migration, data cleanup, or even to force population of certain attributes, you might find that you need to populate or update these fields through script. To get an idea of how to start this process, let's look at an out-of-the-box business rule:
In this case, the condition is that the state changes to Closed of Canceled. If you open the XML of this record, you'll notice this is captured for the filter_condition field:
A couple things to note: the main value that we see there is the same that we would use in a GlideRecord or GlideAggregate when doing addEncodedQuery. To verify that we don't have to code out the additional entries that are an XML of the encoded query, I ran a simple script to insert a new business rule with this same encoded query.
var busRule = new GlideRecord('sys_script');
busRule.initialize();
busRule.setValue('collection','incident');
busRule.setValue('action_insert',true);
busRule.setValue('action_update',true);
busRule.setValue('filter_condition','stateCHANGESTO7^ORstateCHANGESTO8^EQ');
busRule.setValue('name','Testing Script Creation');
busRule.setValue('when','after');
busRule.insert();
You'll notice that I only passed to filter_condition the encoded query itself, not the additional XML details. When I checked the created record's XML, I found that the XML breakdown of the encoded query is automatically created:
This makes the code we need to write to automatically populate these types of fields much simpler. When you're dealing with the Template field on sys_template, it becomes even more straightforward because you only have AND operators. You'll also notice that this one does not contain the XML version of the encoded query.
Now that we understand how the data is stored in these types of fields, let's talk about how to take an import set and populate them via scripting. I'm going to use the example of having a set of task data from a legacy system and wanting to use that to create templates in ServiceNow. Before we go to far, let's contextualize the solution. Normally, end users are going to interact with templates via the templates toolbar on different records in ServiceNow:
To see the templates toolbar, click on the ellipsis on your record and choose Toggle Template Bar:
You should then set at the bottom of the window the toolbar with links to existing templates and the ability to create a template from the record you are currently on:
If you select a template, it will take the value from the "template" field on the sys_template record and update your current record to have the same values. For example, if we were to select the "Major Incident" template, then it would populate Short description, Caller, Impact, and Urgency with the values defined in the image below (which is the sys_template record):
Now that we understand a little bit more about templates and how they are used, let's look at using a spreadsheet to create templates.
I created my example spreadsheet by exporting a few columns from my PDI's incident table and then updating some of the column names to make this a little less one-to-one with mapping. I then attached this file to a new Data source:
Once I have test loaded my data, I now have an import table with the information to transform:
Back on our data source, we'll use the Transform related list to start a new Transform map:
You will always want to populate the corresponding "table" column on a record that has a condition or template value field. In my transform I also populated the name and short description of the sys_template record.
Now let's do the fun bit, creating the script to take our columns that don't 100% match our target model and create the encoded query that goes into the template field:
answer = (function transformEntry(source) {
//What source columns are we going to use to populate the "template" field on the target sys_template
var columnsForTemplate = [
'u_title',
'u_details',
'u_category',
'u_priority',
'u_state',
'u_assignment_group',
'u_configuration_item'
];
//Initialize our targetValue that we will return at the end of this script
var targetValue = '';
//We will loop through the source columns to add them to our targetValue encoded query
for (var item in columnsForTemplate) {
var currentSourceField = columnsForTemplate[item];
var currentSourceValue = source[columnsForTemplate[item]];
//make sure that the source column has a value before adding it to our encoded query
if (!gs.nil(currentSourceValue)) {
//Determine if we need to convert the source value into the right backend value and set the columnValue
var targetColumnValue = '';
switch (currentSourceField) {
case 'u_state':
targetColumnValue = convertState(currentSourceValue);
break;
case 'u_priority':
targetColumnValue = convertPriority(currentSourceValue);
break;
case 'u_assignment_group':
targetColumnValue = findRecord('sys_user_group', 'name', currentSourceValue);
break;
case 'u_configuration_item':
targetColumnValue = findRecord('cmdb_ci', 'name', currentSourceValue);
break;
case 'u_category':
targetColumnValue = convertCategory(currentSourceValue);
break;
default:
targetColumnValue = currentSourceValue;
}
//Convert the source column into the target field for the columnValue
var targetField = convertField(currentSourceField);
//Build (onto) our targetValue encoded query with the new field and value
if (targetValue == '') {
targetValue = targetField + '=' + targetColumnValue;
} else {
targetValue += '^' + targetField + '=' + targetColumnValue;
}
}
}
//Add the final piece of the encoded query (always the same)
if (targetValue != '') {
targetValue += '^EQ';
}
return targetValue;
function convertState(originalState) {
var newState = 0;
var trimmedState = originalState.trim();
var lowerState = trimmedState.toLowerCase();
switch (lowerState) {
case 'open':
newState = 1;
break;
case 'in progress':
newState = 2;
break;
case 'pending':
newState = 3;
break;
case 'resolved':
newState = 6;
}
return newState;
}
function convertPriority(originalPriority) {
var newPriority = parseInt(originalPriority.slice(0, 1));
return newPriority;
}
function findRecord(tbl, field, value) {
var recordId = '';
var getRecord = new GlideRecord(tbl);
if (getRecord.get(field, value)) {
recordId = getRecord.sys_id;
}
return recordId;
}
function convertCategory(originalCategory) {
var newCategory = originalCategory;
var pos = originalCategory.indexOf('/');
if (pos > -1) {
newCategory = originalCategory.slice(0, pos);
}
var lowerCaseCat = newCategory.toLowerCase();
return lowerCaseCat;
}
function convertField(sourceField) {
var targetField = '';
switch (sourceField) {
case 'u_title':
targetField = 'short_description';
break;
case 'u_details':
targetField = 'description';
break;
case 'u_configuration_item':
targetField = 'cmdb_ci';
break;
default:
targetField = sourceField.slice(2, sourceField.length);
}
return targetField;
}
})(source);
Once we run our transform, we now have our template records created and the template field populated correctly:
If you find this article helps you, don't forget to log in, bookmark it and mark the article as "Helpful"!
Don't Forget the ServiceNow User Groups! Sign up and attend one near you!
