Month: January 2019

“I shall either find a way or make one.”
— Hannibal Barca

Last time out, we modified the sys_properties table and associated form so that we could create System Properties with a field type of Reference. This was only half the battle, however, and the easy half at that. The more difficult task will be to figure out how to get the page used to set the value of System Properties to properly render the property for user input.

Even though there is a Value field on the default System Properties form, system property values are generally set using the system_properties_ui.do page, which takes a title and one or more categories as URL parameters. You can see several examples of this in the out-of-the-box left-hand navigation menu, such as the one pictured below from the Live Feed section:

You can see from the rendered form that properties of different Types have different input mechanisms. There is a checkbox for the boolean properties and text input for the string and number properties. All we need to do is figure out how to convince it to render the Reference type properties with a selection list from the specified table. How hard can that be?

Up to this point, everything that we have done has been fairly vanilla ServiceNow stuff. Modifying Choice Lists, adding columns to Tables, manipulating Form Layouts, and creating UI Policies are all bread and butter ServiceNow sysadmin activities. There has been no need to resort to any kind of creative hackery to accomplish what we wanted do, which is always a good thing. Unfortunately, that’s all about to change.

As far as I can tell, the system_properties_ui.do page is yet another one of those items that is an integral part of the product and is not defined in the system database in a way that you can alter it in any way. As with the Email Client, we will have to rely on a script to modify the page after it has been delivered to the browser. To get an idea of how we want to do that, let’s define a property of type reference and see what the out-of-the-box functionality does with it when it renders the page. Entering sys_properties.list in the left-hand navigation search box will get us to the full list of System Properties where we can click on the New button to create our new property. At this point, I am just trying to create something that I can use to see how the input screen turns out, so I just enter Test as the Name, Test as the Description, select reference as the Type, and then select the sys_user table as the Table. Once the property has been defined, I can go back in and assign it to the Category Test by scrolling down to the bottom of the form and clicking on the New button in the Category list.

Once assigned to a Category, I can bring up the standard property value maintenance page with the URL https://<instance>.service-now.com/nav_to.do?uri=%2Fsystem_properties_ui.do%3Fsysparm_title%3DTest%2520Properties%26sysparm_category%3DTest. The good news is that the page didn’t choke on the new, unknown property type, and simply rendered the property as if the type were string:

More important than how it looks, though, is what’s under the hood … let’s inspect that HTML:

<tr>
 <td class="tdwrap label_left" oncontextmenu="return showPropertyContext(event, 'Test');">
  <span>
   <label class="label-inline" for="98c30fe6db362300f9699006db961935">Test</label>
   <button type="button" data-toggle="tooltip" aria-label="Property name: Test" title="" class="btn btn-icon-small btn-icon-white icon-help sn-tooltip-basic" data-original-title="Property name: Test"></button>
  </span>
 </td>
</tr>
<tr>
 <td>
  <input name="98c30fe6db362300f9699006db961935" id="98c30fe6db362300f9699006db961935" value="" aria-label="" style="width: 700px" autocomplete="off">
  <br>
  <br>
 </td>
</tr>

Pretty straightforward stuff, just a couple of single cell table rows, with the label in one row and the input box in the other. It looks like both the name and id of the input element are the sys_id of the property, so it would appear that we have everything that we need to have our way with this code, once it has been delivered to the browser.

So, here’s the plan: given the property categories available from the URL of the page, we should be able to determine all of the properties in the specified category or categories where Type=reference. Looping through that list of properties, we can find the existing input field based on the property’s sys_id, and then replace it with a more appropriate input mechanism to support the selection of records from the specified table. The only question, really, is with what will we replace the input element? If this were a Service Portal widget, we could leverage the snRecordPicker tag, but tags are useless once the page has been delivered to the browser. We could emulate everything that it does, and generate all of the HTML, CSS, and Javascript on our own, but that seems like considerably more work than I care to contemplate right now. We’ll have to give this one a little thought.

In the meantime, let’s jump on that UI Script that pulls the categories off of the URL and makes the Ajax call to the guy that will find all of the properties where Type=reference. That one shouldn’t be much work at all …

if (window.location.pathname == '/system_properties_ui.do') {
	if (window.location.href.indexOf('sysparm_category=') != -1) {
		fetchReferenceProperties();
	}
}

function fetchReferenceProperties() {
	var thisUrl = window.location.href;
	var category = thisUrl.substring(thisUrl.indexOf('sysparm_category=') + 17);
	if (category.indexOf('&') != -1) {
		category = category.substring(0, category.indexOf('&'));
	}
	if (category > '') {
		var ga = new GlideAjax('Reference_Properties');
		ga.addParam('sysparm_name', 'fetchReferenceProperties');
		ga.addParam('sysparm_category', category);
		ga.getXML(processProperties);
	}
}

function processProperties(response) {
	var answer = response.responseXML.documentElement.getAttribute("answer");
	if (answer > '') {
		var property = JSON.parse(answer);
		for (var i=0; i<property.length; i++) {
			var prop = property[i];
			var elem = gel(prop.property);
			elem.parentNode.innerHTML = buildHTML(prop);
		}
	}
}

function buildHTML(prop) {
	// we'll need to figure this out one day ...
}

Well, that wasn’t so bad. While we are thinking about the HTML that we will need to build to replace the original input tag, let’s go ahead and create that Script Include that will gather up all of the properties where Type=reference.

var Reference_Properties = Class.create();
Reference_Properties.prototype = Object.extendsObject(AbstractAjaxProcessor, {

	fetchReferenceProperties: function() {
		var property = [];
		var catList = this.getParameter('sysparm_category');
		if (catList > '') {
			catList = decodeURIComponent(catList);
			var category = catList.split(',');
			for (var i=0; i<category.length; i++) {
				var gr = new GlideRecord('sys_properties_category_m2m');
				gr.addQuery('category.name', category[i]);
				gr.addQuery('category.sys_scope', gs.getCurrentApplicationId());
				gr.addQuery('property.type', 'reference');
				gr.query();
				while (gr.next()) {
					property.push({property: gr.getValue('property')});
				}
			}
		}
		for (var i=0; i<property.length; i++) {
			var sys_id = property[i].property;
			var gr = new GlideRecord('sys_properties');
			gr.get(sys_id);
			property[i].table = gr.getValue('u_table');
			property[i].tableName = gr.getDisplayValue('u_table.name');
			property[i].value = gr.getValue('value');
			property[i].column = this.getDisplayColumn(property[i].tableName);
			property[i].displayValue = this.getDisplayValue(property[i].tableName, property[i].value, property[i].displayColumn);
		}
		return JSON.stringify(property);
	},

	getDisplayColumn: function(table) {
		if (!this.displayColumn[table]) {
			this.displayColumn[table] = this.fetchDisplayColumn(table);
		}
		return this.displayColumn[table];
	},

	fetchDisplayColumn: function(table) {
		var displayColumn = 'sys_id';
		var possibleColumn = ['name','short_description','title','description'];
		for (var i=0; i<possibleColumn.length && displayColumn == 'sys_id'; i++) {
			if (this.columnPresent(table, possibleColumn[i])) {
				displayColumn = possibleColumn[i];
			}
		}
		return displayColumn;
	},

	columnPresent: function(table, column) {
		columnPresent = false;
		var gr = new GlideRecord('sys_dictionary');
		gr.addQuery('name',  table);
		gr.addQuery('element', column);
		gr.query();
		if (gr.next()) {
			columnPresent = true;
		}
		return columnPresent;
	},

	getDisplayValue: function(table, value, column) {
		var displayValue = '';
		if (value > '') {
			var gr = new GlideRecord(table);
			gr.get(value);
			displayValue = gr.getDisplayValue(column);
		}
		return displayValue;
	},

	displayColumn: {},

	type: 'Reference_Properties'
});

This one is pretty self-explanatory, but here is the basic premise for the code: for every category in the list, we search for properties where Type=reference, and then push each one into a single pile of property objects that contain the sys_id of the property record. When we are through creating the pile, we then loop through the pile and fetch the property records using the stored sys_id so that we can add additional data to the objects, including the reference table and the current value. One of the data points that we will undoubtedly need will be the name of the field on the table that contains the “display” value. Although we could have added yet another field to the sys_properties table and had the user provide that information, for now I just hunt for it using a few potential candidates, and then fall back to sys_id if nothing else is available.

At this point, we can actually try out what we have so far, even though we still haven’t figured out what we are going to use to replace the original input element. For now, we can just dump the values onto the screen and make sure that all of the parts and pieces that we have build so far are doing what we would expect them to do. We can do that by adding a little code to the buildHTML function of our UI Script:

function buildHTML(prop) {
	return JSON.stringify(prop);
}

Now we can another little test using our previously defined test property by pulling up the same page that we did at the start.

What that proves is that we can grab the categories from the URL, use them to find all of the reference properties in those categories, use the list of reference properties to find their corresponding input elements on the page, and then replace those input elements with something else. Now all that we have to figure out is what we really want to use to replace those input elements so that we have a pick list from the records in the specified table.

Right about now, that sounds like an interesting exercise for next time out …