Tag: Javascript

Periodic Review, Part X

Posted on | by snhackery

“A person with a clear purpose will make progress on even the roughest road. A person with no purpose will make no progress on even the smoothest road.”
— Thomas Carlyle

Last time, we brought our process far enough along to send out some empty notices, and now we need to create the content for those notices that will inform the recipient of the actions required of them. Before we jump into that, though, we still need to add a little bit more code to our Script Include to wrap the process and update the execution record with the results. At the end of our sendNotices function, let’s add the following code:

// finalize the execution
var itemLabel = 'item';
if (executionGR.total_items > 1) {
	itemLabel = 'items';
}
var noticeLabel = 'notice was';
if (noticeCt > 1) {
	noticeLabel = 'notices were';
}
executionGR.total_notices = noticeCt;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Complete';
executionGR.completion_code = 0;
executionGR.description = noticeCt + ' ' + noticeLabel + ' generated and sent out covering a total of ' + executionGR.total_items + ' ' + itemLabel + '.';
executionGR.update();

This will update the state of the execution and provide some statistics and a description of the execution. We also need to do one more thing to link the notice records to their corresponding email records, but before we can do that, we have to give the event time to fire and the process time to react to the event firing. We can use a gs.sleep command to do that, but since this is a scoped application, we will have to use a little workaround to get things to work.

// update the references to the sent email
var sleeper = new global.Sleeper();
sleeper.sleep(10000);

Once we know that the notice has been sent out, we can use the sys_watermark table to locate the information that we need to link the notice record to the associated email record.

// update the references to the sent email
var sleeper = new global.Sleeper();
sleeper.sleep(10000);
noticeGR.initialize();
noticeGR.addQuery('review_execution', executionGR.getUniqueValue());
noticeGR.query();
while (noticeGR.next()) {
	var watermark = noticeGR.getValue('email_watermark');
	if (watermark) {
		var watermarkGR = new GlideRecord('sys_watermark');
		if (watermarkGR.get('number', watermark.substring(4))) {
			noticeGR.setValue('email', watermarkGR.getValue('email'));
			noticeGR.update();
		}
	}
}

That should wrap up the process for sending out the requests to review the artifacts. At this point our entire Script Include now looks like this:

var PeriodicReviewUtils = Class.create();
PeriodicReviewUtils.prototype = {
    initialize: function() {
    },

	dailyProcess: function() {
		var toRun = [];
		var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
		var today = new GlideDate();
		configurationGR.addQuery('next_scheduled_date', today);
		configurationGR.orderBy('number');
		configurationGR.query();
		while (configurationGR.next()) {
			var execution = {};
			execution.configuration = configurationGR.getUniqueValue();
			execution.table = configurationGR.getDisplayValue('table');
			execution.filter = configurationGR.getDisplayValue('filter');
			toRun.push(execution);
		}
		if (toRun.length > 0) {
			gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
			for (var i in toRun) {
				var thisRun = toRun[i];
				this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
			}
			gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
		} else {
			gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
		}
	},

	processExecution: function(configuration, table, filter) {
		gs.info('PeriodicReviewUtils.processExecution: ' + configuration + '; ' + table + '; ' + filter);

		// fetch system properties
		var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
		var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');

		// create execution record
		var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
		executionGR.configuration = configuration;
		executionGR.run_date = new GlideDate();
		executionGR.run_start = new GlideDateTime();
		executionGR.state = 'Running';
		executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
		executionGR.total_items = 0;
		executionGR.total_notices = 0;
		executionGR.completion_code = 0;
		executionGR.insert();
		var configurationGR = executionGR.configuration.getRefRecord();
		var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');

		// create temporary notice record
		var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
		noticeGR.review_execution = executionGR.getUniqueValue();
		noticeGR.recipient = configurationGR.fallback_recipient;
		noticeGR.short_description = 'Temporary notice record';
		noticeGR.insert();
		var tempNotice = noticeGR.getUniqueValue();

		// create notice item records from source table data
		var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
		var itemCt = 0;
		var sourceGR = new GlideRecord(table);
		if (sourceGR.isValid()) {
			if (filter) {
				sourceGR.addEncodedQuery(filter);
			}
			sourceGR.orderBy(configurationGR.short_description_column);
			sourceGR.query();
			while (sourceGR.next()) {
				noticeItemGR.initialize();
				noticeItemGR.review_notice = tempNotice;
				noticeItemGR.id = sourceGR.getUniqueValue();
				noticeItemGR.short_description =  sourceGR.getDisplayValue(configurationGR.short_description_column);
				noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
				noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
				noticeItemGR.insert();
				itemCt++;
				var recipientGR = new GlideRecord('sys_user');
				recipientGR.get(noticeItemGR.getValue('recipient'));
				var notes = '';
				if (recipientGR.isValid()) {
					if (recipientGR.getValue('active')) {
						var email = recipientGR.getDisplayValue('email');
						if (email) {
							if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
								notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
							}
						} else {
							notes = 'Specified recipient has no email address; reverting to fallback recipient';
						}
					} else {
						notes = 'Specified recipient is not active; reverting to fallback recipient';
					}
				} else {
					notes = 'Recipient column empty on source record; reverting to fallback recipient';
				}
				if (notes) {
					noticeItemGR.notes = notes;
					noticeItemGR.recipient = fallbackRecipient;
					noticeItemGR.update();
				}
			}
			if (itemCt > 0) {
				executionGR.total_items = itemCt;
				executionGR.update();
				this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
			} else {

				// finalize the execution
				executionGR.total_items = 0;
				executionGR.total_notices = 0;
				executionGR.run_end = new GlideDateTime();
				executionGR.state = 'Complete';
				executionGR.completion_code = 0;
				executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
				executionGR.update();
			}

			// delete the temporary notice
			noticeGR.get(tempNotice);
			noticeGR.deleteRecord();
		} else {

			// finalize the execution
			executionGR.total_items = 0;
			executionGR.total_notices = 0;
			executionGR.run_end = new GlideDateTime();
			executionGR.state = 'Failed';
			executionGR.completion_code = 1;
			executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
			executionGR.update();
		}

		// set the next run date
		configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
		configurationGR.update();
	},

	sendNotices: function(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice) {
		gs.info('PeriodicReviewUtils.sendNotices: ' + configurationGR.getUniqueValue() + '; ' + executionGR.getUniqueValue() + '; ' + noticeGR.getUniqueValue() + '; ' + noticeItemGR.getUniqueValue() + '; ' + tempNotice);
		var noticeCt = 0;

		var noticeItemGA = new GlideAggregate('x_11556_periodic_r_review_notice_item');
		noticeItemGA.addQuery('review_notice', tempNotice);
		noticeItemGA.addAggregate('COUNT');
		noticeItemGA.groupBy('recipient');
		noticeItemGA.orderBy('recipient');
		noticeItemGA.query();
		while (noticeItemGA.next()) {
			var recipient = noticeItemGA.getValue('recipient');
			noticeGR.initialize();
			noticeGR.review_execution = executionGR.getUniqueValue();
			noticeGR.recipient = recipient;
			noticeGR.short_description = executionGR.getDisplayValue('run_date') + ' review notice for ' + configurationGR.getDisplayValue('short_description');
			noticeGR.insert();
			noticeItemGR.initialize();
			noticeItemGR.addQuery('recipient', recipient);
			noticeItemGR.addQuery('review_notice', tempNotice);
			noticeItemGR.query();
			while (noticeItemGR.next()) {
				noticeItemGR.review_notice = noticeGR.getUniqueValue();
				noticeItemGR.update();
			}
			// now you need to send out the notice, passing in the notice record for variables
			gs.eventQueue('x_11556_periodic_r.ReviewNotice', noticeGR, noticeGR.recipient, noticeGR.getUniqueValue());
			noticeCt++;
		}

		// finalize the execution
		var itemLabel = 'item';
		if (executionGR.total_items > 1) {
			itemLabel = 'items';
		}
		var noticeLabel = 'notice was';
		if (noticeCt > 1) {
			noticeLabel = 'notices were';
		}
		executionGR.total_notices = noticeCt;
		executionGR.run_end = new GlideDateTime();
		executionGR.state = 'Complete';
		executionGR.completion_code = 0;
		executionGR.description = noticeCt + ' ' + noticeLabel + ' generated and sent out covering a total of ' + executionGR.total_items + ' ' + itemLabel + '.';
		executionGR.update();

		// update the references to the sent email
		var sleeper = new global.Sleeper();
		sleeper.sleep(10000);
		noticeGR.initialize();
		noticeGR.addQuery('review_execution', executionGR.getUniqueValue());
		noticeGR.query();
		while (noticeGR.next()) {
			var watermark = noticeGR.getValue('email_watermark');
			if (watermark) {
				var watermarkGR = new GlideRecord('sys_watermark');
				if (watermarkGR.get('number', watermark.substring(4))) {
					noticeGR.setValue('email', watermarkGR.getValue('email'));
					noticeGR.update();
				}
			}
		}
	},

	calculateNextRunDate: function(configurationGR) {
        var runDate = new Date();
        var frequency = configurationGR.getValue('frequency');
        var days = 0;
        var months = 0;
        if (frequency == 'daily') {
            days = 1;
        } else if (frequency == 'weekly') {
            days = 7;
        } else if (frequency == 'biweekly') {
            days = 14;
        } else if (frequency == 'monthly') {
            months = 1;
        } else if (frequency == 'bimonthly') {
            months = 2;
        } else if (frequency == 'quarterly') {
            months = 3;
        } else if (frequency == 'semiannually') {
            months = 6;
        } else if (frequency == 'annually') {
            months = 12;
        } else if (frequency == 'biannually') {
            months = 24;
        }
        if (days > 0) {
            runDate.setDate(runDate.getDate() + days);
        } else {
            runDate.setMonth(runDate.getMonth() + months);
        }
        return JSON.stringify(runDate).substring(1, 11);
    },

    type: 'PeriodicReviewUtils'
};

We still have to build the content for the notices, and a way for the notice recipients to communicate their responses back to the system so that the appropriate action can be taken, so let’s jump into all of that next time out.

Periodic Review, Part VIII

Posted on | by snhackery

“Code is like humor. When you have to explain it, it’s bad.”
— Cory House

Last time, we built out the processExecution function in our utility Script Include, and now we need to continue on by building out the sendNotices function. Before we build it out completely, though, let’s just create a quick stub so that we can test out the work so far. This ought to do the trick.

sendNotices: function(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice) {
	gs.info('PeriodicReviewUtils.sendNotices: ' + configurationGR.getUniqueValue() + '; ' + executionGR.getUniqueValue() + '; ' + noticeGR.getUniqueValue() + '; ' + noticeItemGR.getUniqueValue() + '; ' + tempNotice);
}

Now we just need to make sure that we have the next run date set correctly on our lone example configuration and then we can navigate over to Scripts – Background and type in our earlier testing code.

var pru = new PeriodicReviewUtils();
pru.dailyProcess();
gs.info('Done!');

And once again we can hit that Run script button and see what happens.

Initial test results

Well, that’s not good! It seems that there is some kind of problem with our calculateNextRunDate function. It’s always something! But then, that’s why I like to test things out as I go rather than waiting for the entire thing to be built to try things out. The good news is that it seems to have run all of the way through and made it all the way to the end, where it updates the run date, before it crashed and burned. Let me dig into that function and see what might be wrong.

(insert brief debugging pause)

OK, I found the issue. Apparently, it doesn’t like this line:

var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));

The next scheduled date on the configuration record is a GlideDate, and the display value is year, month, and then day. The Javascript Date constructor doesn’t like that, although I know that I have done that in the past without issue. Still, it doesn’t really matter because the value should be today’s date; otherwise, we wouldn’t be running right now. So we can simple change that to this:

var runDate = new Date();

That should still get us the date that we are looking for, so let’s hop back over to Scripts – Background and try this again.

Successful test results

That’s better. So far, so good. Now we need to actually build out that sendNotices function to group all of the notice item records by recipient, create a notice record for each recipient, and then send out the notices. We can develop an unduplicated list of recipients by using a GlideAggregate.

var noticeItemGA = new GlideAggregate('x_11556_periodic_r_review_notice_item');
noticeItemGA.addQuery('review_notice', tempNotice);
noticeItemGA.addAggregate('COUNT');
noticeItemGA.groupBy('recipient');
noticeItemGA.orderBy('recipient');
noticeItemGA.query();
while (noticeItemGA.next()) {
	var recipient = noticeItemGA.getValue('recipient');
	...
}

Once we have the recipient, we can create the notice record for that recipient.

noticeGR.initialize();
noticeGR.review_execution = executionGR.getUniqueValue();
noticeGR.recipient = recipient;
noticeGR.short_description = executionGR.getDisplayValue('run_date') + ' review notice for ' + configurationGR.getDisplayValue('short_description');
noticeGR.insert();

Now that a notice record for this recipient has been created, we need to go back and find all of the notice item records that are associated with this recipient and update the reference to the notice record from the temporary notice to the newly created notice for this recipient.

noticeItemGR.initialize();
noticeItemGR.addQuery('recipient', recipient);
noticeItemGR.addQuery('review_notice', tempNotice);
noticeItemGR.query();
while (noticeItemGR.next()) {
	noticeItemGR.review_notice = noticeGR.getUniqueValue();
	noticeItemGR.update();
}

At this point, we should be able to send out the notice, but as that is a rather complex process in and of itself, let’s just stub things out for now so that we can test out the process so far.

gs.info('This is where we would send a notice to ' + noticeGR.getDisplayValue('recipient'));

All together, our new sendNotices function now looks like this.

sendNotices: function(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice) {
	gs.info('PeriodicReviewUtils.sendNotices: ' + configurationGR.getUniqueValue() + '; ' + executionGR.getUniqueValue() + '; ' + noticeGR.getUniqueValue() + '; ' + noticeItemGR.getUniqueValue() + '; ' + tempNotice);
var noticeCt = 0;

	var noticeItemGA = new GlideAggregate('x_11556_periodic_r_review_notice_item');
	noticeItemGA.addQuery('review_notice', tempNotice);
	noticeItemGA.addAggregate('COUNT');
	noticeItemGA.groupBy('recipient');
	noticeItemGA.orderBy('recipient');
	noticeItemGA.query();
	while (noticeItemGA.next()) {
		var recipient = noticeItemGA.getValue('recipient');
		noticeGR.initialize();
		noticeGR.review_execution = executionGR.getUniqueValue();
		noticeGR.recipient = recipient;
		noticeGR.short_description = executionGR.getDisplayValue('run_date') + ' review notice for ' + configurationGR.getDisplayValue('short_description');
		noticeGR.insert();
		noticeItemGR.initialize();
		noticeItemGR.addQuery('recipient', recipient);
		noticeItemGR.addQuery('review_notice', tempNotice);
		noticeItemGR.query();
		while (noticeItemGR.next()) {
			noticeItemGR.review_notice = noticeGR.getUniqueValue();
			noticeItemGR.update();
		}
		gs.info('This is where we would send a notice to ' + noticeGR.getDisplayValue('recipient'));
	}
}

That should be enough to give things the old college try. Let’s pop back over to Scripts – Background and paste in our testing code and see what we get.

Testing results

Nice! Obviously, a lot more testing is in order before we can say for sure that everything works for every scenario, but things are looking pretty good. Next time, let’s see if we can actually send out a notice.

Periodic Review, Part VII

Posted on | by snhackery

“Whether you want to uncover the secrets of the universe, or you just want to pursue a career in the 21st century, basic computer programming is an essential skill to learn.”
— Stephen Hawking

Last time, we got started on our script that will run the review process by building and testing the primary function that finds all of the configurations that will need to run. Today we need to continue on with the script, beginning with the process for a single configuration’s execution. Before we get started, though, we need to talk a little bit about making sure that the notice will go to somebody.

Both the configuration record and the system as a whole have a default recipient/email address. The system default email address is contained in a System Property and the configuration default recipient is on the configuration record. There is a field on the source record for the recipient, and if that recipient record is valid and active and contains an email address, then that will be the address that we will want to use. If not, then we will want to use the configuration fallback recipient, and if there is some issue in the notice process with that recipient’s email address, then we will end up using the system fallback address. This is to ensure that the notice will actually get to a real person who can take action on the review request.

There is also another optional System Property that can be used to limit notifications to a specific email domain. If that value is present, and the recipient email is not an email address of that domain, then the fallback recipient will be used as well. All of this should be documented in the notice item record. So the first thing that we will want to do will be to pull those System Properties.

// fetch system properties
var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');

The next thing that we will want to do will be to create a record in the Review Execution table to memorialize this execution.

// create execution record
var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
executionGR.configuration = configuration;
executionGR.run_date = new GlideDate();
executionGR.run_start = new GlideDateTime();
executionGR.state = 'Running';
executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.completion_code = 0;
executionGR.insert();

This is standard GlideRecord stuff, so we don’t really need to go into a lot of detail here other than to say that we link it back to the configuration that is being executed and initialize all of the other values. Once we have our execution record created, we can pull the configuration record from the reference, and once we have it, we can set up the fallback recipient for this configuration.

var configurationGR = executionGR.configuration.getRefRecord();
var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');

Now we need to go fetch all of the items that need to be reviewed this cycle, but because we want to consolidate all of the items for any given recipient into a single notice, we will first need to fetch them all, and then once we have the full list, group them by recipient. To facilitate that, we can create a temporary notice record and assign all of the items to that notice, and once we run through all of the query results, we can then fetch them back by recipient. So let’s create that temporary notice record now.

// create temporary notice record
var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
noticeGR.review_execution = executionGR.getUniqueValue();
noticeGR.recipient = configurationGR.fallback_recipient;
noticeGR.short_description = 'Temporary notice record';
noticeGR.insert();
var tempNotice = noticeGR.getUniqueValue();

Once we have our notice record, we can use the table and filter from the configuration record to find all of the items to be reviewed.

// create notice item records from source table data
var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
var itemCt = 0;
var sourceGR = new GlideRecord(table);
if (sourceGR.isValid()) {
	if (filter) {
		sourceGR.addEncodedQuery(filter);
	}
	sourceGR.orderBy(configurationGR.short_description_column);
	sourceGR.query();
	while (sourceGR.next()) {
		...
	}
	...
} else {
	...
}

Then, inside the query loop, we can build a record in the Review Notice Item table for each item.

noticeItemGR.initialize();
noticeItemGR.review_notice = tempNotice;
noticeItemGR.id = sourceGR.getUniqueValue();
noticeItemGR.short_description =  sourceGR.getDisplayValue(configurationGR.short_description_column);
noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
noticeItemGR.insert();
itemCt++;

Once the notice item record has been created, we can inspect the recipient and come up with a valid recipient to which we will send the notice.

var recipientGR = new GlideRecord('sys_user');
recipientGR.get(noticeItemGR.getValue('recipient'));
var notes = '';
if (recipientGR.isValid()) {
	if (recipientGR.getValue('active')) {
		var email = recipientGR.getDisplayValue('email');
		if (email) {
			if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
				notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
			}
		} else {
			notes = 'Specified recipient has no email address; reverting to fallback recipient';
		}
	} else {
		notes = 'Specified recipient is not active; reverting to fallback recipient';
	}
} else {
	notes = 'Recipient column empty on source record; reverting to fallback recipient';
}
if (notes) {
	noticeItemGR.notes = notes;
	noticeItemGR.recipient = fallbackRecipient;
	noticeItemGR.update();
}

Once we complete the loop, we will want to see if the query returned any items, and either process the items or close out this execution.

if (itemCt > 0) {
	executionGR.total_items = itemCt;
	executionGR.update();
	this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
} else {

	// finalize the execution
	executionGR.total_items = 0;
	executionGR.total_notices = 0;
	executionGR.run_end = new GlideDateTime();
	executionGR.state = 'Complete';
	executionGR.completion_code = 0;
	executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
	executionGR.update();
}

And once that is done, whether we had any items returned or not, we will want to delete that temporary notice record.

// delete the temporary notice
noticeGR.get(tempNotice);
noticeGR.deleteRecord();

One last thing: we need to do something in the event that we find ourselves in the else branch of the if (sourceGR.isValid()) { conditional. This basically means that the source table specified on the configuration record is not valid and so we cannot look for any items there. This is basically a failure of the execution, so we need to update the execution record to reflect that.

// finalize the execution
executionGR.total_items = 0;
executionGR.total_notices = 0;
executionGR.run_end = new GlideDateTime();
executionGR.state = 'Failed';
executionGR.completion_code = 1;
executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
executionGR.update();

Failure is not an option that we originally set up for that field, so we will have to go back into the dictionary at some point and fix that to make that code work. Finally, regardless of how the execution turned out, we need to update the configuration record with the next run date, which is calculated in the function that we added for that purpose earlier.

// set the next run date
configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
configurationGR.update();

With the addition of this completed function, our utility Script Include now looks like this.

var PeriodicReviewUtils = Class.create();
PeriodicReviewUtils.prototype = {
    initialize: function() {
    },

	dailyProcess: function() {
		var toRun = [];
		var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
		var today = new GlideDate();
		configurationGR.addQuery('next_scheduled_date', today);
		configurationGR.orderBy('number');
		configurationGR.query();
		while (configurationGR.next()) {
			var execution = {};
			execution.configuration = configurationGR.getUniqueValue();
			execution.table = configurationGR.getDisplayValue('table');
			execution.filter = configurationGR.getDisplayValue('filter');
			toRun.push(execution);
		}
		if (toRun.length > 0) {
			gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
			for (var i in toRun) {
				var thisRun = toRun[i];
				this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
			}
			gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
		} else {
			gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
		}
	},

	processExecution: function(configuration, table, filter) {
		gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);

		// fetch system properties
		var systemFallbackAddress = gs.getProperty('x_11556_periodic_r.fallback_email_address');
		var systemEmailDomain = gs.getProperty('x_11556_periodic_r.email_domain');

		// create execution record
		var executionGR = new GlideRecord('x_11556_periodic_r_review_execution');
		executionGR.configuration = configuration;
		executionGR.run_date = new GlideDate();
		executionGR.run_start = new GlideDateTime();
		executionGR.state = 'Running';
		executionGR.short_description = executionGR.getDisplayValue('run_date') + ' review notices';
		executionGR.total_items = 0;
		executionGR.total_notices = 0;
		executionGR.completion_code = 0;
		executionGR.insert();
		var configurationGR = executionGR.configuration.getRefRecord();
		var fallbackRecipient = configurationGR.getDisplayValue('fallback_recipient');

		// create temporary notice record
		var noticeGR = new GlideRecord('x_11556_periodic_r_review_notice');
		noticeGR.review_execution = executionGR.getUniqueValue();
		noticeGR.recipient = configurationGR.fallback_recipient;
		noticeGR.short_description = 'Temporary notice record';
		noticeGR.insert();
		var tempNotice = noticeGR.getUniqueValue();

		// create notice item records from source table data
		var noticeItemGR = new GlideRecord('x_11556_periodic_r_review_notice_item');
		var itemCt = 0;
		var sourceGR = new GlideRecord(table);
		if (sourceGR.isValid()) {
			if (filter) {
				sourceGR.addEncodedQuery(filter);
			}
			sourceGR.orderBy(configurationGR.short_description_column);
			sourceGR.query();
			while (sourceGR.next()) {
				noticeItemGR.initialize();
				noticeItemGR.review_notice = tempNotice;
				noticeItemGR.id = sourceGR.getUniqueValue();
				noticeItemGR.short_description =  sourceGR.getDisplayValue(configurationGR.short_description_column);
				noticeItemGR.description = sourceGR.getDisplayValue(configurationGR.description_column);
				noticeItemGR.recipient = sourceGR.getValue(configurationGR.recipient_column);
				noticeItemGR.insert();
				itemCt++;
				var recipientGR = new GlideRecord('sys_user');
				recipientGR.get(noticeItemGR.getValue('recipient'));
				var notes = '';
				if (recipientGR.isValid()) {
					if (recipientGR.getValue('active')) {
						var email = recipientGR.getDisplayValue('email');
						if (email) {
							if (systemEmailDomain && !email.endsWith(systemEmailDomain)) {
								notes = 'Recipient email address is not an authoized email address; reverting to fallback recipient';
							}
						} else {
							notes = 'Specified recipient has no email address; reverting to fallback recipient';
						}
					} else {
						notes = 'Specified recipient is not active; reverting to fallback recipient';
					}
				} else {
					notes = 'Recipient column empty on source record; reverting to fallback recipient';
				}
				if (notes) {
					noticeItemGR.notes = notes;
					noticeItemGR.recipient = fallbackRecipient;
					noticeItemGR.update();
				}
			}
			if (itemCt > 0) {
				executionGR.total_items = itemCt;
				executionGR.update();
				this.sendNotices(configurationGR, executionGR, noticeGR, noticeItemGR, tempNotice);
			} else {

				// finalize the execution
				executionGR.total_items = 0;
				executionGR.total_notices = 0;
				executionGR.run_end = new GlideDateTime();
				executionGR.state = 'Complete';
				executionGR.completion_code = 0;
				executionGR.description = 'No items matched the filter criteria during this run, so no notices were sent out.';
				executionGR.update();
			}

			// delete the temporary notice
			noticeGR.get(tempNotice);
			noticeGR.deleteRecord();
		} else {
			// finalize the execution
			executionGR.total_items = 0;
			executionGR.total_notices = 0;
			executionGR.run_end = new GlideDateTime();
			executionGR.state = 'Failed';
			executionGR.completion_code = 1;
			executionGR.description = 'The specified source table in the configuration record is not valid; execution cannot proceed; aborting execution.';
			executionGR.update();
		}

		// set the next run date
		configurationGR.setValue('next_scheduled_date', this.calculateNextRunDate(configurationGR));
		configurationGR.update();
	},

	calculateNextRunDate: function(configurationGR) {
		var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
		var frequency = configurationGR.getValue('frequency');
		var days = 0;
		var months = 0;
		if (frequency == 'daily') {
			days = 1;
		} else if (frequency == 'weekly') {
			days = 7;
		} else if (frequency == 'biweekly') {
			days = 14;
		} else if (frequency == 'monthly') {
			months = 1;
		} else if (frequency == 'bimonthly') {
			months = 2;
		} else if (frequency == 'quarterly') {
			months = 3;
		} else if (frequency == 'semiannually') {
			months = 6;
		} else if (frequency == 'annually') {
			months = 12;
		} else if (frequency == 'biannually') {
			months = 24;
		}
		if (days > 0) {
			runDate.setDate(runDate.getDate() + days);
		} else {
			runDate.setMonth(runDate.getMonth() + months);
		}
		return JSON.stringify(runDate).substring(1, 11);
	},

    type: 'PeriodicReviewUtils'
};

Next time, we will stub out that sendNotices function and give things another test or two, and then actually build out that function to group the items by recipient and send out the notices.

Periodic Review, Part VI

Posted on | by snhackery

“One man’s crappy software is another man’s full time job.”
— Jessica Gaston

Last time, we wrapped up the work on creating all of the tables that we will need to support the review process. Now it is time to create that process using those tables. Before we jump into that, though, we should talk a little bit about how the process should work.

There are a couple of different ways to go about things that occur on a periodic basis. One way is to handle things the way most US states handle the renewals for driver’s licenses: each driver has their own renewal date based on something like their birthday or the day on which they originally got their license. Another way would be to handle things like the IRS does for federal income taxes: every person has the same date each year, sometime in the middle of April. One way spreads out the work across the entire year and the other results in all of the work coming at once at a particular time. Our process should be able to handle both approaches, and between the frequency value and the filter value in the configuration record, there should be a way to set things out to work out for either one.

Speaking of the frequency field, we should probably set up a list of specific choices for that guy so that our code knows what to expect. We can do that in the Choices tab of the Related Lists on the Dictionary Entry form of the frequency field.

Choice list for configuration frequency field

We may come up with other options in the future, but this list will do for now. The main purpose of this field is so that the process can establish the date of the next run for this particular configuration. Now that we know the values, we can create a simple function to set the next run date based on the current run date and the specified frequency.

calculateNextRunDate: function(configurationGR) {
	var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
	var frequency = configurationGR.getValue('frequency');
	var days = 0;
	var months = 0;
	if (frequency == 'daily') {
		days = 1;
	} else if (frequency == 'weekly') {
		days = 7;
	} else if (frequency == 'biweekly') {
		days = 14;
	} else if (frequency == 'monthly') {
		months = 1;
	} else if (frequency == 'bimonthly') {
		months = 2;
	} else if (frequency == 'quarterly') {
		months = 3;
	} else if (frequency == 'semiannually') {
		months = 6;
	} else if (frequency == 'annually') {
		months = 12;
	} else if (frequency == 'biannually') {
		months = 24;
	}
	if (days > 0) {
		runDate.setDate(runDate.getDate() + days);
	} else {
		runDate.setMonth(runDate.getMonth() + months);
	}
	return JSON.stringify(runDate).substring(1, 11);
}

Of course, we won’t need that until the end of the run, as that will be one of the last things that we will do before we move on to the next configuration that we will be running today. So let’s back things up a bit and look at things from a little higher perspective.

To run the review process, we will set up a Scheduled Job that will run once a day, presumably at some time where the demand on the system is lower, and that job will call a Script Include function that will do all of the heavy lifting. That function will then query the database for any configuration records where the next run date is the current date. For each returned record, the process will use the filter field on the configuration record to query the table specified on the configuration record to obtain all of the items up for review. Next we will want to organize all of the items by recipient and send each recipient a notice that contains all of the items up for review for which that recipient is responsible. To house all of the functions required to perform all of thee tasks, we can create a single utility Script Include and call it PeriodicReviewUtils.

New utility Script Include

We’ll call our first function dailyProcess set up an object array called toRun and populate it with data from the returned configuration records. Then we will spin through the array and pass the values from that object to another function called processExecution that will handle the process for a single configuration.

dailyProcess: function() {
	var toRun = [];
	var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
	var today = new GlideDate();
	configurationGR.addQuery('next_scheduled_date', today);
	configurationGR.orderBy('number');
	configurationGR.query();
	while (configurationGR.next()) {
		var execution = {};
		execution.configuration = configurationGR.getUniqueValue();
		execution.table = configurationGR.getDisplayValue('table');
		execution.filter = configurationGR.getDisplayValue('filter');
		toRun.push(execution);
	}
	if (toRun.length > 0) {
		gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
		for (var i in toRun) {
			var thisRun = toRun[i];
			this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
		}
		gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
	} else {
		gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
	}
}

I like to test things as I go along, just to make sure that I didn’t fat finger something along the way, and we can stop and do that here if we temporarily stub out the processExecution function to just spit out the values that were passed to it. That would make it look something like this:

processExecution: function(configuration, table, filter) {
	gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);
}

We only have the one configuration record that we set up for Service Accounts right now, but that should be enough to validate the code that we have so far, which currently looks like this.

var PeriodicReviewUtils = Class.create();
PeriodicReviewUtils.prototype = {
    initialize: function() {
    },

	dailyProcess: function() {
		var toRun = [];
		var configurationGR = new GlideRecord('x_11556_periodic_r_review_configuration');
		var today = new GlideDate();
		configurationGR.addQuery('next_scheduled_date', today);
		configurationGR.orderBy('number');
		configurationGR.query();
		while (configurationGR.next()) {
			var execution = {};
			execution.configuration = configurationGR.getUniqueValue();
			execution.table = configurationGR.getDisplayValue('table');
			execution.filter = configurationGR.getDisplayValue('filter');
			toRun.push(execution);
		}
		if (toRun.length > 0) {
			gs.info('PeriodicReviewUtils.dailyProcess: Running ' + toRun.length + ' execution(s) today.');
			for (var i in toRun) {
				var thisRun = toRun[i];
				this.processExecution(thisRun.configuration, thisRun.table, thisRun.filter);
			}
			gs.info('PeriodicReviewUtils.dailyProcess: ' + toRun.length + ' execution(s) completed.');
		} else {
			gs.info('PeriodicReviewUtils.dailyProcess: Nothing scheduled to run today.');
		}
	},

	processExecution: function(configuration, table, filter) {
		gs.info('PeriodicReviewUtils.dailyProcess: ' + configuration + '; ' + table + '; ' + filter);
	},

	calculateNextRunDate: function(configurationGR) {
		var runDate = new Date(configurationGR.getDisplayValue('next_scheduled_date'));
		var frequency = configurationGR.getValue('frequency');
		var days = 0;
		var months = 0;
		if (frequency == 'daily') {
			days = 1;
		} else if (frequency == 'weekly') {
			days = 7;
		} else if (frequency == 'biweekly') {
			days = 14;
		} else if (frequency == 'monthly') {
			months = 1;
		} else if (frequency == 'bimonthly') {
			months = 2;
		} else if (frequency == 'quarterly') {
			months = 3;
		} else if (frequency == 'semiannually') {
			months = 6;
		} else if (frequency == 'annually') {
			months = 12;
		} else if (frequency == 'biannually') {
			months = 24;
		}
		if (days > 0) {
			runDate.setDate(runDate.getDate() + days);
		} else {
			runDate.setMonth(runDate.getMonth() + months);
		}
		return JSON.stringify(runDate).substring(1, 11);
	},

    type: 'PeriodicReviewUtils'
};

To run a quick test, we can navigate over to Scripts – Background and use this little test script.

var pru = new PeriodicReviewUtils();
pru.dailyProcess();
gs.info('Done!');

Then all we need to do is to click on the Run script button and see what happens.

First test run results

Well, as usual, there is good news and bad news here. The good news is that it didn’t crash and it made all the way through without an error. The bad news is that I forgot to set the next run date field on the lone configuration record, so the process didn’t get very far. On the bright side, though, we did successfully test the scenario where there is nothing to run, so we can check that off of the list. Now let’s update the configuration and try this again.

Second test run results

There, that’s better! And once again, we ran through without any errors, so I think we are off to a good start. Next time, we will build out the code for that processExecution function and give that guy a little test.

Periodic Review, Part III

Posted on | by snhackery

“We conquer by continuing.”
— George Matheson

Last time, we created our app, added a few properties, and built our first table. Today we are going to work with that table to configure the layout of both the list and the form, and then maybe do a few other things before we move on to the rest of the tables. Let’s start with the list.

To edit the fields that will show up on the list view, we can bring up the list view and then select Configure -> List Layout from the context menu.

Configuring the list layout

Using the slush bucket, we can select Number, Short description, Item label and Description from the available fields on the table.

Selecting the fields to appear on the list view

That will give us a list view that looks like this.

Newly configured list view

Using basically the same method, we can arrange the fields on the form view.

Configuring the form layout

The form is a bit more complicated than the list, so to help organize things, we can divide the form into sections. After we lay out the main section of the form, we can scroll down to the Section list and click on the New… option, which brings up a small dialog box where we can give our new section a name.

Creating a new section on the form

Once we have created the new section, we can drag in and arrange all of the fields that we would like to see in that section of the form.

Populating the fields in our new section

Once that has been completed, we can take a look at our new form.

The new form layout

We are still not quite done with the form just yet, though. All of the columns that reference fields on the selected table should have a selection list that is limited to just the fields on that table. To accommodate that, we need to pull up the dictionary record for each of those fields and set up a dependency. To do that, right click on the field label and select Configure Dictionary from the resulting context menu.

Editing the dictionary record from the form

Using the Advanced view, go into the Dependent Field tab and check the Use dependent field checkbox and select Table from the list of fields.

Setting up the dependent field

This process will need to be repeated for all of the columns that represents fields on the configured table.

The last thing that we need to add to this form, at least for now, is the ability to test the Filter against the specified table. It would probably be more user-friendly if our Filter field was some kind of query builder, but since it is just a simple String field, the least we can do is to provide some mechanism to test out the query string once it has been entered. The easiest way to do that would be to create a UI Action called Test Filter that used the Table and the Filter fields to branch to the List view of that table. Building a link to the List view in script would look something like this:

current.table + '_list.do?sysparm_query=' + encodeURIComponent(current.filter)

Branching to that page in a UI Action script would then just be this:

action.setRedirectURL(current.table + '_list.do?sysparm_query=' + encodeURIComponent(current.filter));

Clicking on the button would then take you to the list where you could see what records would be selected using that filter. To create the UI Action, we can use the context menu on the form and select Configure -> UI Actions and then click on the New button to create a new UI Action for that form.

Creating a new UI Action to test out the entered query filter

Once our action has been configured and saved, the button should appear at the top of the form.

New form button from new UI Action configuration

That should just be about it for our first table and all of the associated fields, forms, and views. Next time, we can use our Service Account Management app as a potential first user of this app and see if we can set up the configuration for that app before we move on to creating other tables.

Collaboration Store, Part LXXVII

Posted on | by snhackery

“Set your goals high, and don’t stop till you get there.”
— Bo Jackson

Last time, we started hacking up a copy of the Service Portal page sc_category, beginning with the HTML. Now we need to update the Server script to pull in our data so that we can bring up the widget and see how it looks. For the header portion, we need to pull in data about the store, which we can get from the instance record for the Host instance.

function fetchStoreDetails() {
	var instanceGR = new GlideRecord('x_11556_col_store_member_organization');
	if (instanceGR.get('instance', gs.getProperty('x_11556_col_store.host_instance'))) {
		data.store = {};
		data.store.name = instanceGR.getDisplayValue('name');
		data.store.description = instanceGR.getDisplayValue('description');
		data.store.logo = instanceGR.getValue('logo');
		data.store.sys_id = instanceGR.getUniqueValue();
	}
}

For the apps, we look to the application table. At this point, we just want to pull in all of the data, so we will save any filtering for later in the development process. Right now, we just want to see how our presentation is coming out.

function fetchItemDetails(items) {
	var appGR = new GlideRecord('x_11556_col_store_member_application');
	appGR.query();
	while (appGR.next()) {
		var item = {};
		item.name = appGR.getDisplayValue('name');
		item.short_description = appGR.getDisplayValue('description');
		item.picture = appGR.getValue('logo');
		item.version = appGR.getDisplayValue('current_version');
		item.provider = appGR.getDisplayValue('provider.name');
		item.providerLogo = appGR.provider.getRefRecord().getValue('logo');
		item.sys_id = appGR.getUniqueValue();
		item.hasPrice = false;
		item.page = 'sc_cat_item';
		item.type = appGR.getValue('sys_class_name');
		item.order = 0;
		item.sys_class_name = appGR.getValue('sys_class_name');
		items.push(item);
	}
}

Some of that is still left over from the original widget, but we’ll clean that up later. For now, we just want to get to the point where we can throw this widget on a page and bring it up and see what we have. A lot of the other code from the original can be tossed, but we will need to retain a few things related to the paging, which leaves us with this.

(function() {
	data.items = [];
	data.show_more = false;
	fetchStoreDetails();
	var itemsInPage = options.limit_item || 9;

	data.limit = itemsInPage;
	if (input && input.new_limit) {
		data.limit = input.new_limit;
	}
	if (input && input.items) {
		data.items = input.items.slice();//Copy the input array
	}

	if (input && input.startWindow) {
		data.endWindow = input.endWindow;
	} else {
		data.startWindow = 0;
		data.endWindow = 0;
	}

	fetchItemDetails(data.items);

	if (data.items.length > data.limit) {
		data.show_more = true;
	}

	data.more_msg = gs.getMessage(" Showing {0} items", data.limit);

	function fetchStoreDetails() {
		var instanceGR = new GlideRecord('x_11556_col_store_member_organization');
		if (instanceGR.get('instance', gs.getProperty('x_11556_col_store.host_instance'))) {
			data.store = {};
			data.store.name = instanceGR.getDisplayValue('name');
			data.store.description = instanceGR.getDisplayValue('description');
			data.store.logo = instanceGR.getValue('logo');
			data.store.sys_id = instanceGR.getUniqueValue();
		}
	}

	function fetchItemDetails(items) {
		var appGR = new GlideRecord('x_11556_col_store_member_application');
		appGR.query();
		while (appGR.next()) {
			var item = {};
			item.name = appGR.getDisplayValue('name');
			item.short_description = appGR.getDisplayValue('description');
			item.picture = appGR.getValue('logo');
			item.version = appGR.getDisplayValue('current_version');
			item.provider = appGR.getDisplayValue('provider.name');
			item.providerLogo = appGR.provider.getRefRecord().getValue('logo');
			item.sys_id = appGR.getUniqueValue();
			item.hasPrice = false;
			item.page = 'sc_cat_item';
			item.type = appGR.getValue('sys_class_name');
			item.order = 0;
			item.sys_class_name = appGR.getValue('sys_class_name');
			items.push(item);
		}
	}

})();

That should be enough to make it work. Now let’s create a page for our widget, call it collaboration_store, and bring it up in the Service Portal Designer. To begin, let’s drag a 3/9 container onto the page.

Dragging a 3/9 container onto the new portal page

Once we have our container in place, let’s find our new widget and drag it into the 9 portion of the 3/9 container.

Dragging our new widget onto the page

Eventually, we will want to come up some kind of search/filter widget for the 3 section of the 3/9 container, but for now we will just leave that empty, save what we have, and pull up the page in the Service Portal to see how things are looking.

First look at our new store layout

Not bad! Of course, we still have a lot of work to do on the Client script to handle things like switching to the alternate view, clicking on a tile, or paging through lengthy results, but the look and feel seems to work so far. I think it might be good to put the logo of the store in the header, but other than that, I do not see any significant changes that I would like to make at this point. I think it actually looks pretty good.

We’ll need to start working on the Client script now, once again stripping out those things related to catalogs and categories, and making sure that the toggle functions still work for switching back and forth between the tiles layout and the table layout. Also, we will need to figure out what we want to do when the operator clicks on an app. We could bring up the details of the application in a modal pop-up, navigate to a detail page, or even go straight into launching an install. That will all end up in the Client script as well.

One thing that we will need to add to the view is the state of the app on the instance, whether it is up to date, needs to be upgraded to the latest version, or has never been installed. The state of the app may determine what happens when you click on the app, so we will need to figure all that out as well. So much to figure out, but still it is a pretty good start. Next time, we will just keep forging ahead unless we have some test results to address.

Scripted Value Columns, Enhanced, Part II

Posted on | by snhackery

“Alone we can do so little; together we can do so much.”
— Helen Keller

Last time, we tinkered with the Scripted Value Columns section of the SNH Data Table Widgets, and created an example Task list using an aggregation of labor hours as a demonstration of how you can include HTML with your returned value. Today we will have a look at another scripted column in that example, the Customer column.

There are quite a number of extensions to the core Task table in ServiceNow, all of which share the same core list of fields. One of those fields is called opened_by, and identifies the person who was signed on to the system when the task was initially opened. That person is not necessarily the customer, though. In the case of an Incident, for example, that is more likely to be a call center agent who took the call, not the person who is reporting the issue. There is no core table field to represent the customer. Each extension of the Task table has its own way of identifying the person awaiting the completion of the task. Using the scripted value column feature, though, you can create a “customer” column by looking at the type of task, and then grabbing the value of the field that is appropriate for that particular type.

getScriptedValue: function(item, config) {
	var response = '';

	var table = item.sys_class_name.value;
	var field = 'opened_by';
	if (table == 'incident') {
		field = 'caller_id';
	} else if (table == 'sc_request') {
		field = 'requested_for';
	} else if (table == 'sc_req_item') {
		field = 'request.requested_for';
	} else if (table == 'sc_task') {
		field = 'request_item.request.requested_for';
	} else if (table == 'change_request') {
		field = 'requested_by';
	}
	var taskGR = new GlideRecord(table);
	if (taskGR.get(item.sys_id)) {
		if (taskGR.getValue(field) > '') {
			response = taskGR.getDisplayValue(field);
		}
	}

	return response;
}

This particular example handles incidents, catalog activity, and change requests, but could easily be modified to handle any number of other types of extensions to the core task table. This just returns the value, though. If you wanted to make the field a link to the user profile page, for example, you could do something like this.

getScriptedValue: function(item, config) {
	var response = '';

	var table = item.sys_class_name.value;
	var field = 'opened_by';
	if (table == 'incident') {
		field = 'caller_id';
	} else if (table == 'sc_request') {
		field = 'requested_for';
	} else if (table == 'sc_req_item') {
		field = 'request.requested_for';
	} else if (table == 'sc_task') {
		field = 'request_item.request.requested_for';
	} else if (table == 'change_request') {
		field = 'requested_by';
	}
	var taskGR = new GlideRecord(table);
	if (taskGR.get(item.sys_id)) {
		if (taskGR.getValue(field) > '') {
			var sysId = taskGR.getValue(field);
			var name = taskGR.getDisplayValue(field);
			response += '<a href="?id=user_profile&table=sys_user&sys_id=';
			response += sysId;
			response += '" aria-label="${Click for more on }';
			response += name;
			response += '">';
			response += name;
			response += '</a>\n';
		}
	}

	return response;
}

That would provide an output such as this.

Customer column as link to user profile page

The one thing that the Customer column does not have that the Assigned to column, which is a standard SNH Data Table user reference, does have is the user avatar image. That’s easily done with the sn-avatar tag, but that tag is not simple HTML, and not processed using the ng-bind-html attribute. Theoretically, it can be processed using the sc-bind-html-compile attribute, but I have never had any luck in utilizing this component. I tried it, and couldn’t get it to work, so I gave up and went back to using the ng-bind-html attribute. So, there is no user avatar in this version. Maybe one day I will figure that out, but that day is not today.

Speaking of figuring things out one day, I still have not been able to get rid of that extra line that appears when there is an image available for the avatar. If you look at the example above, the lines with an avatar image are wider than the lines where that field is blank, or the initials are used in place of an image. This because there is an extra carriage return in there that expands the height of the line. Only when there is an actual image does this occur, but it shouldn’t occur at all. If there is some grand master CSS wizard out there who understands why this is happening and can explain it to me, or better yet, tell me what I can do to prevent it, I would be forever in your debt. I have not been able to figure that one out.

One other thing that you may have noticed in that image is that I got rid of the 0.00 value for tasks that had no hours charged to them. I like that better, and that was a pretty simple thing to do. I still want to have some kind of mouse-over display of who logged the hours, so maybe we will take a look at that next time out.

Scripted Value Columns, Part III

Posted on | by snhackery

“Every day you may make progress. Every step may be fruitful. Yet there will stretch out before you an ever-lengthening, ever-ascending, ever-improving path.”
— Winston Churchill

Last time, we took care of the configuration script editor and now we need to turn our attention to the main SNH Data Table Widgets starting with the core widget, SNH Data Table. As we did with the editor, we can search the various sections of the widget for aggarray, copy the relevant code, and modify it to handle the new svcarrary. As usual, we can start with the HTML, where we find a couple of sections, one for the headings:

<th ng-repeat="scripted in data.svcarray" class="text-nowrap center" tabindex="0">
  {{scripted.heading || scripted.label}}
</th>

… and one for the data columns:

<td ng-repeat="obj in item.svcValue" role="cell" class="sp-list-cell" ng-class="{selected: item.selected}" tabindex="0">
  {{obj.value}}
</td>

That takes care of the HTML. Now we need to take a look at the Server script. The first thing that we come across is this added block of comments:

// Start: SNH Data Table enhancements
	 * data.bulkactions = the JSON string containing the bulk action specifications
	 * data.refpage = the JSON string containing the reference link specifications
	 * data.aggregates = the JSON string containing the aggregate column specifications
	 * data.buttons = the JSON string containing the button specifications
	 * data.actarray = the bulk actions specifications object
	 * data.refmap = the reference link specifications object
	 * data.aggarray = the array of aggregate column specifications
	 * data.btnarray = the array of button specifications
// End: SNH Data Table enhancements

So we will modify that to include two new properties for our new feature.

// Start: SNH Data Table enhancements
	 * data.bulkactions = the JSON string containing the bulk action specifications
	 * data.refpage = the JSON string containing the reference link specifications
	 * data.scripteds = the JSON string containing the scripted value column specifications
	 * data.aggregates = the JSON string containing the aggregate column specifications
	 * data.buttons = the JSON string containing the button specifications
	 * data.actarray = the bulk actions specifications object
	 * data.refmap = the reference link specifications object
	 * data.svcarray = the array of scripted value column specifications
	 * data.aggarray = the array of aggregate column specifications
	 * data.btnarray = the array of button specifications
// End: SNH Data Table enhancements

The next reference to aggarray is this added variable copy statement:

// Start: SNH Data Table enhancements
	optCopy(['table_name', 'aggregates', 'buttons', 'btns', 'refpage', 'bulkactions', 'aggarray', 'btnarray', 'refmap', 'actarray', 'field_list']);

	...
// End: SNH Data Table enhancements

So we will add our new variables to this list.

// Start: SNH Data Table enhancements
	optCopy(['table_name', 'scripteds', 'aggregates', 'buttons', 'btns', 'refpage', 'bulkactions', 'svcarray', 'aggarray', 'btnarray', 'refmap', 'actarray', 'field_list']);

	...
// End: SNH Data Table enhancements

Shortly after that, we come to this code that validates and initializes the aggarray value.

if (data.aggregates) {
	try {
		var aggregateinfo = JSON.parse(data.aggregates);
		if (Array.isArray(aggregateinfo)) {
			data.aggarray = aggregateinfo;
		} else if (typeof aggregateinfo == 'object') {
			data.aggarray = [];
			data.aggarray[0] = aggregateinfo;
		} else {
			gs.error('Invalid aggregates option in SNH Data Table widget: ' + data.aggregates);
			data.aggarray = [];
		}
	} catch (e) {
		gs.error('Unparsable aggregates option in SNH Data Table widget: ' + data.aggregates);
		data.aggarray = [];
	}
} else {
	if (!data.aggarray) {
		data.aggarray = [];
	}
}

So we can copy that, and add a section just like it for the new svcarray.

if (data.scripteds) {
	try {
		var scriptedinfo = JSON.parse(data.scripteds);
		if (Array.isArray(scriptedinfo)) {
			data.svcarray = scriptedinfo;
		} else if (typeof scriptedinfo == 'object') {
			data.svcarray = [];
			data.svcarray[0] = scriptedinfo;
		} else {
			gs.error('Invalid scripteds option in SNH Data Table widget: ' + data.scripteds);
			data.svcarray = [];
		}
	} catch (e) {
		gs.error('Unparsable scripteds option in SNH Data Table widget: ' + data.scripteds);
		data.svcarray = [];
	}
} else {
	if (!data.svcarray) {
		data.svcarray = [];
	}
}

The next reference that we find is the code that actually adds the values to the records. For the aggregate columns, that code looks like this:

record.aggValue = [];
if (data.aggarray.length > 0) {
	for (var j=0; j<data.aggarray.length; j++) {
		var config = data.aggarray[j];
		var sysId = record.sys_id;
		if (config.source) {
			sysId = gr.getValue(config.source);
		}
		record.aggValue.push(getAggregateValue(sysId, config));
	}
}

We can make a copy of this section as well, but since the scripted values do not require a source property, our new section will be even simpler.

record.svcValue = [];
if (data.svcarray.length > 0) {
	for (var j=0; j<data.svcarray.length; j++) {
		record.svcValue.push(getScriptedValue(record, data.svcarray[j]));
	}
}

Of course, now we have referenced a function that doesn’t yet exist, but that is in fact the next and last reference that we come across. The function for the aggregates looks like this:

// Start: SNH Data Table enhancements
	function getAggregateValue(sys_id, config) {
		var value = 0;
		var ga = new GlideAggregate(config.table);
		ga.addAggregate('COUNT');
		var query = config.field + '=' + sys_id;
		if (config.filter) {
			query += '^' + config.filter;
		}
		ga.addEncodedQuery(query);
		ga.query();
		if (ga.next()) {
			value = parseInt(ga.getAggregate('COUNT'));
		}
		var response = {value: value};
		if (config.hint || config.page_id) {
			response.name = config.name;
		}
		return response;
	}
// End: SNH Data Table enhancements

Here is where we have to do something completely different from the original that we are copying. For the aggregate columns, we are actually doing the query to count the related records. For our new purpose, we are just going to grab an instance of the specified script and call the function on the script to get the value. Since we will be calling the same script for every row, it would be better to fetch the instance of the script once and hang on to it so that the same instance could be used again and again. To support that, we can establish a map of instances and an instance of the Instantiator up near the top.

var instantiator = new Instantiator(this);
var scriptMap = {};

With that in place, we can add the following new function to support the new scripted value columns.

function getScriptedValue(record, config) {
	var response = {value: ''};
	var scriptName = config.script;
	if (scriptName) {
		if (scriptName.startsWith('global.')) {
			scriptName = scriptName.split('.')[1];
		}
		if (!scriptMap[scriptName]) {
			scriptMap[scriptName] = instantiator.getInstance(scriptName);
		}
		if (scriptMap[scriptName]) {
			response.value = scriptMap[scriptName].getScriptedValue(record, config);
		}
	}
	return response;
}

That’s it for the Server script. The whole thing now looks like this:

(function() {
	if (!input) // asynch load list
		return;

	data.msg = {};
	data.msg.sortingByAsc = gs.getMessage("Sorting by ascending");
	data.msg.sortingByDesc = gs.getMessage("Sorting by descending");

	/*
	 * data.table = the table
	 * data.p = the current page starting at 1
	 * data.o = the order by column
	 * data.d = the order by direction
	 * data.keywords = the keyword search term
	 * data.list = the table data as an array
	 * data.invalid_table = true if table is invalid or if data was not succesfully fetched
	 * data.table_label = the table's display name. e.g. Incident
	 * data.table_plural = the table's plural display name. e.g. Incidents
	 * data.fields = a comma delimited list of field names to show in the data table
	 * data.column_labels = a map of field name -> display name
	 * data.window_size = the number of rows to show
	 * data.filter = the encoded query
// Start: SNH Data Table enhancements
	 * data.bulkactions = the JSON string containing the bulk action specifications
	 * data.refpage = the JSON string containing the reference link specifications
	 * data.scripteds = the JSON string containing the scripted value column specifications
	 * data.aggregates = the JSON string containing the aggregate column specifications
	 * data.buttons = the JSON string containing the button specifications
	 * data.actarray = the bulk actions specifications object
	 * data.refmap = the reference link specifications object
	 * data.svcarray = the array of scripted value column specifications
	 * data.aggarray = the array of aggregate column specifications
	 * data.btnarray = the array of button specifications
// End: SNH Data Table enhancements
	 */
	// copy to data[name] from input[name] || option[name]
	optCopy(['table', 'p', 'o', 'd', 'filter', 'filterACLs', 'fields', 'keywords', 'view']);
	optCopy(['relationship_id', 'apply_to', 'apply_to_sys_id', 'window_size']);

// Start: SNH Data Table enhancements
	optCopy(['table_name', 'scripteds', 'aggregates', 'buttons', 'btns', 'refpage', 'bulkactions', 'svcarray', 'aggarray', 'btnarray', 'refmap', 'actarray', 'field_list']);

	// for some reason, 'buttons' and 'table' sometimes get lost in translation ...
	if (data.btns) {
		data.buttons = data.btns;
	}
	if (data.table_name) {
		data.table = data.table_name;
	}
// End: SNH Data Table enhancements

	if (!data.table) {
		data.invalid_table = true;
		data.table_label = "";
		return;
	}

// Start: SNH Data Table enhancements
	var instantiator = new Instantiator(this);
	var scriptMap = {};
	if (data.scripteds) {
		try {
			var scriptedinfo = JSON.parse(data.scripteds);
			if (Array.isArray(scriptedinfo)) {
				data.svcarray = scriptedinfo;
			} else if (typeof scriptedinfo == 'object') {
				data.svcarray = [];
				data.svcarray[0] = scriptedinfo;
			} else {
				gs.error('Invalid scripteds option in SNH Data Table widget: ' + data.scripteds);
				data.svcarray = [];
			}
		} catch (e) {
			gs.error('Unparsable scripteds option in SNH Data Table widget: ' + data.scripteds);
			data.svcarray = [];
		}
	} else {
		if (!data.svcarray) {
			data.svcarray = [];
		}
	}

	if (data.aggregates) {
		try {
			var aggregateinfo = JSON.parse(data.aggregates);
			if (Array.isArray(aggregateinfo)) {
				data.aggarray = aggregateinfo;
			} else if (typeof aggregateinfo == 'object') {
				data.aggarray = [];
				data.aggarray[0] = aggregateinfo;
			} else {
				gs.error('Invalid aggregates option in SNH Data Table widget: ' + data.aggregates);
				data.aggarray = [];
			}
		} catch (e) {
			gs.error('Unparsable aggregates option in SNH Data Table widget: ' + data.aggregates);
			data.aggarray = [];
		}
	} else {
		if (!data.aggarray) {
			data.aggarray = [];
		}
	}

	if (data.buttons) {
		try {
			var buttoninfo = JSON.parse(data.buttons);
			if (Array.isArray(buttoninfo)) {
				data.btnarray = buttoninfo;
			} else if (typeof buttoninfo == 'object') {
				data.btnarray = [];
				data.btnarray[0] = buttoninfo;
			} else {
				gs.error('Invalid buttons option in SNH Data Table widget: ' + data.buttons);
				data.btnarray = [];
			}
		} catch (e) {
			gs.error('Unparsable buttons option in SNH Data Table widget: ' + data.buttons);
			data.btnarray = [];
		}
	} else {
		if (!data.btnarray) {
			data.btnarray = [];
		}
	}

	if (data.refpage) {
		try {
			var refinfo = JSON.parse(data.refpage);
			if (typeof refinfo == 'object') {
				data.refmap = refinfo;
			} else {
				gs.error('Invalid reference page option in SNH Data Table widget: ' + data.refpage);
				data.refmap = {};
			}
		} catch (e) {
			gs.error('Unparsable reference page option in SNH Data Table widget: ' + data.refpage);
			data.refmap = {};
		}
	} else {
		if (!data.refmap) {
			data.refmap = {};
		}
	}

	if (data.bulkactions) {
		try {
			var actioninfo = JSON.parse(data.bulkactions);
			if (Array.isArray(actioninfo)) {
				data.actarray = actioninfo;
			} else if (typeof actioninfo == 'object') {
				data.actarray = [];
				data.actarray[0] = actioninfo;
			} else {
				gs.error('Invalid bulk actions in SNH Data Table widget: ' + data.bulkactions);
				data.actarray = [];
			}
		} catch (e) {
			gs.error('Unparsable bulk actions in SNH Data Table widget: ' + data.bulkactions);
			data.actarray = [];
		}
	} else {
		if (!data.actarray) {
			data.actarray = [];
		}
	}

	if (!data.fields) {
		if (data.field_list) {
			data.fields = data.field_list;
		} else if (data.view) {
			data.fields = $sp.getListColumns(data.table, data.view);
		} else {
			data.fields = $sp.getListColumns(data.table);
		}
	}
// End: SNH Data Table enhancements

	data.view = data.view || 'mobile';
	data.table = data.table || $sp.getValue('table');
	data.filter = data.filter || $sp.getValue('filter');
	data.keywords = data.keywords || $sp.getValue('keywords');
	data.p = data.p || $sp.getValue('p') || 1;
	data.p = parseInt(data.p);
	data.o = data.o || $sp.getValue('o') || $sp.getValue('order_by');
	data.d = data.d || $sp.getValue('d') || $sp.getValue('order_direction') || 'asc';
	data.useTinyUrl = gs.getProperty('glide.use_tiny_urls') === 'true';
	data.tinyUrlMinLength = gs.getProperty('glide.tiny_url_min_length');

// Start: SNH Data Table enhancements
	if (data.filter && data.filter.indexOf('{{sys_id}}')) {
		data.filter = data.filter.replace('{{sys_id}}', $sp.getParameter('sys_id'));
	}
// End: SNH Data Table enhancements


	var grForMetaData = new GlideRecord(data.table);

	if (input.setOrderUserPreferences) {
		// update User Preferences on a manual sort for UI consistency
		gs.getUser().savePreference(data.table + ".db.order", data.o);
		gs.getUser().savePreference(data.table + ".db.order.direction", data.d == "asc" ? "" : "DESC");
		data.setOrderUserPreferences = false;
	}
	// if no sort specified, find a default column for UI consistency
	if (!data.o)
		getOrderColumn();

	data.page_index = data.p - 1;
	data.show_new = data.show_new || options.show_new;
	var windowSize = data.window_size || $sp.getValue('maximum_entries') || 20;
	windowSize = parseInt(windowSize);
	if (isNaN(windowSize) || windowSize < 1)
		windowSize = 20;
	data.window_size = windowSize;

	var gr;
	// FilteredGlideRecord is not supported in scoped apps, so GlideRecordSecure will always be used in an application scope
	if (typeof FilteredGlideRecord != "undefined" && (gs.getProperty("glide.security.ui.filter") == "true" || grForMetaData.getAttribute("glide.security.ui.filter") != null)) {
		gr = new FilteredGlideRecord(data.table);
		gr.applyRowSecurity();
	} else
		gr = new GlideRecordSecure(data.table);
	if (!gr.isValid()) {
		data.invalid_table = true;
		data.table_label = data.table;
		return;
	}

	data.canCreate = gr.canCreate();
	data.newButtonUnsupported = data.table == "sys_attachment";
	data.table_label = gr.getLabel();
	data.table_plural = gr.getPlural();
	data.title = input.useInstanceTitle && input.headerTitle ? gs.getMessage(input.headerTitle) : data.table_plural;
	data.hasTextIndex = $sp.hasTextIndex(data.table);
	if (data.filter) {
		if (data.filterACLs)
			gr = $sp.addQueryString(gr, data.filter);
		else
			gr.addEncodedQuery(data.filter);
	}
	if (data.keywords) {
		gr.addQuery('123TEXTQUERY321', data.keywords);
		data.keywords = null;
	}

	data.filter = gr.getEncodedQuery();

	if (data.relationship_id) {
		var rel = GlideRelationship.get(data.relationship_id);
		var target = new GlideRecord(data.table);
		var applyTo = new GlideRecord(data.apply_to);
		applyTo.get("sys_id", data.apply_to_sys_id);
		rel.queryWith(applyTo, target); // put the relationship query into target
		data.exportQuery = target.getEncodedQuery();
		gr.addEncodedQuery(data.exportQuery); // get the query the relationship made for us
	}
	if (data.exportQuery)
		data.exportQuery += '^' + data.filter;
	else
		data.exportQuery = data.filter;
	data.exportQueryEncoded = encodeURIComponent(data.exportQuery);
	if (data.o){
		if (data.d == "asc")
			gr.orderBy(data.o);
		else
			gr.orderByDesc(data.o);
		if (gs.getProperty("glide.secondary.query.sysid") == "true")
			gr.orderBy("sys_id");
	}

	data.window_start = data.page_index * data.window_size;
	data.window_end = (data.page_index + 1) * data.window_size;
	gr.chooseWindow(data.window_start, data.window_end);
	gr.setCategory("service_portal_list");
	gr._query();

	data.row_count = gr.getRowCount();
	data.num_pages = Math.ceil(data.row_count / data.window_size);
	data.column_labels = {};
	data.column_types = {};
	data.fields_array = data.fields.split(',');

	// use GlideRecord to get field labels vs. GlideRecordSecure
	for (var i in data.fields_array) {
		var field = data.fields_array[i];
		var ge = grForMetaData.getElement(field);
		if (ge == null)
			continue;

		data.column_labels[field] = ge.getLabel();
		data.column_types[field] = ge.getED().getInternalType();
	}

	data.list = [];
	while (gr._next()) {
		var record = {};
		$sp.getRecordElements(record, gr, data.fields);
		if (typeof FilteredGlideRecord != "undefined" && gr instanceof FilteredGlideRecord) {
			// FilteredGlideRecord doesn't do field-level
			// security, so take care of that here
			for (var f in data.fields_array) {
				var fld = data.fields_array[f];
				if (!gr.isValidField(fld))
					continue;

				if (!gr[fld].canRead()) {
					record[fld].value = null;
					record[fld].display_value = null;
				}
			}
		}
		record.sys_id = gr.getValue('sys_id');

// Start: SNH Data Table enhancements
		for (var f in data.fields_array) {
			var fld = data.fields_array[f];
			if (record[fld].type == 'reference') {
				var refGr = gr;
				var refFld = fld;
				if (fld.indexOf('.') != -1) {
					var parts = fld.split('.');
					for (var x=0;x<parts.length-1;x++) {
						refGr = refGr[parts[x]].getRefRecord();
					}
					refFld = parts[parts.length-1];
				}
				if (refGr.isValidField(refFld)) {
					record[fld].table = refGr.getElement(refFld).getED().getReference();
					record[fld].record = {type: 'reference', sys_id: {value: record[fld].value, display_value: record[fld].value}, name: {value: record[fld].display_value, display_value: record[fld].display_value}};
				}
			}
		}
		record.svcValue = [];
		if (data.svcarray.length > 0) {
			for (var j=0; j<data.svcarray.length; j++) {
				record.svcValue.push(getScriptedValue(record, data.svcarray[j]));
			}
		}
		record.aggValue = [];
		if (data.aggarray.length > 0) {
			for (var k=0; k<data.aggarray.length; k++) {
				var config = data.aggarray[k];
				var sysId = record.sys_id;
				if (config.source) {
					sysId = gr.getValue(config.source);
				}
				record.aggValue.push(getAggregateValue(sysId, config));
			}
		}
// End: SNH Data Table enhancements

		record.targetTable = gr.getRecordClassName();
		data.list.push(record);
	}

	data.enable_filter = (input.enable_filter == true || input.enable_filter == "true" ||
		options.enable_filter == true || options.enable_filter == "true");
	var breadcrumbWidgetParams = {
		table: data.table,
		query: data.filter,
		enable_filter: data.enable_filter
	};
	data.filterBreadcrumbs = $sp.getWidget('widget-filter-breadcrumbs', breadcrumbWidgetParams);

	// copy to data from input or options
	function optCopy(names) {
		names.forEach(function(name) {
			data[name] = input[name] || options[name];
		})
	}

	// getOrderColumn logic mirrors that of Desktop UI when no sort column is specified
	function getOrderColumn() {
		// First check for user preference
		var pref = gs.getUser().getPreference(data.table + ".db.order");
		if (!GlideStringUtil.nil(pref)) {
			data.o = pref;
			if (gs.getUser().getPreference(data.table + ".db.order.direction") == "DESC")
				data.d = 'desc';
			return;
		}

		// If no user pref, check for table default using same logic as Desktop UI:
		// 1) if task, use number
		// 2) if any field has isOrder attribute, use that
		// 3) use order, number, name column if exists (in that priority)
		if (grForMetaData.isValidField("sys_id") && grForMetaData.getElement("sys_id").getED().getFirstTableName() == "task") {
			data.o = "number";
			return;
		}

		// Next check for isOrder attribute on any column
		var elements = grForMetaData.getElements();
		// Global and scoped GlideRecord.getElements return two different things,
		// so convert to Array if needed before looping through
		if (typeof elements.size != "undefined") {
			var elementArr = [];
			for (var i = 0; i < elements.size(); i++)
				elementArr.push(elements.get(i));
			elements = elementArr;
		}
		// Now we can loop through
		for (var j = 0; elements.length > j; j++) {
			var element = elements[j];
			if (element.getAttribute("isOrder") == "true") {
				data.o = element.getName();
				return;
			}
		}
		// As last resort, sort on Order, Number, or Name column
		if (grForMetaData.isValidField("order"))
			data.o = "order";
		else if (grForMetaData.isValidField("number"))
			data.o = "number";
		else if (grForMetaData.isValidField("name"))
			data.o = "name";
	}

// Start: SNH Data Table enhancements
	function getScriptedValue(record, config) {
		var response = {value: ''};
		var scriptName = config.script;
		if (scriptName) {
			if (scriptName.startsWith('global.')) {
				scriptName = scriptName.split('.')[1];
			}
			if (!scriptMap[scriptName]) {
				scriptMap[scriptName] = instantiator.getInstance(scriptName);
			}
			if (scriptMap[scriptName]) {
				response.value = scriptMap[scriptName].getScriptedValue(record, config);
			}
		}
		return response;
	}

	function getAggregateValue(sys_id, config) {
		var value = 0;
		var ga = new GlideAggregate(config.table);
		ga.addAggregate('COUNT');
		var query = config.field + '=' + sys_id;
		if (config.filter) {
			query += '^' + config.filter;
		}
		ga.addEncodedQuery(query);
		ga.query();
		if (ga.next()) {
			value = parseInt(ga.getAggregate('COUNT'));
		}
		var response = {value: value};
		if (config.hint || config.page_id) {
			response.name = config.name;
		}
		return response;
	}
// End: SNH Data Table enhancements

})();

There are no changes needed to the Client script, or any other area, so we are done with the modifications to this widget. Now would be a good time to try it out, but we will need at least one of the three wrapper widgets to be updated before we can give things a try. That sounds like a good project for our next installment.

sn-record-picker Helper, Corrected (again!)

Posted on | by snhackery

“The biggest room in the world is the room for improvement.”
— Helmut Schmidt

The other day I was working on adding Aggregate List Columns to my SNH Data Table collection and I lifted some code from the sn-record-picker Helper that looked like it might be fairly close to what I needed at the time. Unfortunately, when I tested it all out, I came to realize that the code that I lifted contained an error, and a pretty serious one at that. The problem is with the filter used on all of the table field drop-downs, which is supposed to include all of fields on the selected table. As it turns out, it includes all of the fields on the primary table, but does not include all of the other fields from any other table in the table hierarchy. That is definitely not good!

Tables can be extended from other tables, and in cases such as the CMDB, there can be quite a number of tables in the extension hierarchy. For example, the cmdb_ci_win_server table extends the cmdb_ci_server table, which extends the cmdb_ci_computer table, which extends the cmdb_ci_hardware table, which extends the cmdb_ci table, which extends the cmdb table. That’s a lot of missing fields if you only include the fields from the primary table. Clearly, this needs to be addressed, and should have been addressed long ago.

In digging through the code, the problem appears to be in this function that creates the filter for all of the field pickers:

$scope.buildFieldFilter = function() {
	c.data.ready = false;
	c.data.fieldFilter = 'elementISNOTEMPTY^name=' + c.data.table.value;
};

The current filter that is built turns out to be:

name=(name of selected table)

… when what it really needs to be is:

nameIN(list of all tables in the heirarchy)

That is simple enough to do, but then, the question becomes, where do we get a list of all of the tables in the hierarchy? As usual, the Now Platform has anticipated that someone might need that, and includes a way to get that very information. The solution to our query is the TableUtils object. That object includes a method called getTables() that will return just such a list. Unfortunately for us, this object is only available on the server side, and up until now, our widget did not need to have any server-side code. Well, things change.

Here is how we can use this object to get a Javascript Array of table names:

var tu = new TableUtils(input.table.value);
var tableList = tu.getTables();
data.tableList = j2js(tableList);

The object returned by the getTables() method is actually a Java object, so we have to use the j2js (Java to Javascript) function to turn it into an Array that we can use. Here is the entire new widget Server script with this functionality included:

(function() {
	if (input && input.table.value) {
		var tu = new TableUtils(input.table.value);
		var tableList = tu.getTables();
		data.tableList = j2js(tableList);
	}
})();

With that now available on the server side, we need to go into our client-side function and have it call the server to get the list, and then use that list to rebuild the filter in a way that will include all of the fields on all of the tables involved. Here is the new buildFieldFilter function:

$scope.buildFieldFilter = function() {
	c.data.ready = false;
	c.data.fieldFilter = 'name=' + c.data.table.value;
	c.server.update().then(function(response) {
		if (response.tableList && Array.isArray(response.tableList) && response.tableList.length > 1) {
			c.data.fieldFilter = 'nameIN' + response.tableList.join(',');
		}
		c.data.fieldFilter += '^elementISNOTEMPTY';
	});
};

If there is not more than one table involved, then we can leave the filter with the straight equals condition, but if there is more than one table in the returned list, then we overlay that with the IN condition instead. Either way, we tack on the rest of the original filter, and we should be good to go.

Well, that’s all there is to that. Now I just need to pull all of the parts together for a new Update Set, and we should be good until we come across the next previously undetected problem!

Collaboration Store, Part XLV

Posted on | by snhackery

“Progress isn’t made by early risers. It’s made by lazy men trying to find easier ways to do something.”
— Robert Heinlein

Last time, we completed the code to sync up the version records, so now all we have left to do is to do basically the same thing for the Update Set XML files attached to the version records. The attachments are a slightly different animal, though, so we can’t just clone what we have done before as we were able to do when using the application record process as a basis for creating the version record process. For one thing, the relationship between instances and their applications is one-to-many, just as the relationship between applications and their various versions is one-to-many. Theoretically, there should only be one attached Update Set XML file for any given version. Technically, there is nothing stopping someone from attaching additional files to a version record, and in the future we may, in fact, want to attach things to a version record like release notes or user guides, but there should only be one Update Set for each version.

One thing that I ended up doing was to go back into the syncVersions function and add in the capability to send the sys_id of the version record to the syncAttachments function. Because the sys_attachments table uses both a table_name and a table_sys_id property to link to the attachment to its record, I couldn’t really do any dot-walking to come up with a query to find the right attachment record. So I added a versionIdList in addition to the versionList to capture the sys_id of the version record along with the version number. The updated version of the syncVersions function now looks like this:

syncVersions: function(targetGR, thisApplication, thisInstance, remoteAppId) {
	var versionList = [];
	var versionIdList = [];
	var versionGR = new GlideRecord('x_11556_col_store_member_application_version');
	versionGR.addQuery('member_application.name', thisApplication);
	versionGR.addQuery('member_application.provider.instance', thisInstance);
	versionGR.query();
	while (versionGR.next()) {
		versionList.push(versionGR.getDisplayValue('version'));
		versionIdList.push(versionGR.getUniqueValue());
	}
	if (versionList.length > 0) {
		var request  = new sn_ws.RESTMessageV2();
		request.setHttpMethod('get');
		request.setBasicAuth(this.CSU.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
		request.setRequestHeader("Accept", "application/json");
		request.setEndpoint('https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/x_11556_col_store_member_application_version?sysparm_fields=version%2Csys_id&sysparm_query=member_application%3D' + remoteAppId);
		var response = request.execute();
		if (response.haveError()) {
			gs.error('InstanceSyncUtils.syncVersions - Error returned from attempt to fetch version list: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		} else if (response.getStatusCode() == '200') {
			var jsonString = response.getBody();
			var jsonObject = {};
			try {
				jsonObject = JSON.parse(jsonString);
			} catch (e) {
				gs.error('InstanceSyncUtils.syncVersions - Unparsable JSON string returned from attempt to fetch version list: ' + jsonString);
			}
			if (Array.isArray(jsonObject.result)) {
				for (var i=0; i<versionList.length; i++) {
					var thisVersion = versionList[i];
					var thisVersionId = versionIdList[i];
					var remoteVerId = '';
					for (var j=0; j<jsonObject.result.length && remoteVerId == ''; j++) {
						if (jsonObject.result[j].version == thisVersion) {
							remoteVerId = jsonObject.result[j].sys_id;
						}
					}
					if (remoteVerId == '') {
						remoteVerId = this.sendVersion(targetGR, thisVersion, thisApplication, thisInstance, remoteAppId);
					}
					this.syncAttachments(targetGR, thisVersionId, thisVersion, thisApplication, thisInstance, remoteVerId);
				}
			} else {
				gs.error('InstanceSyncUtils.syncVersions - Invalid response body returned from attempt to fetch version list: ' + response.getBody());
			}
		} else {
			gs.error('InstanceSyncUtils.syncVersions - Invalid HTTP response code returned from attempt to fetch version list: ' + response.getStatusCode());
		}
	} else {
		gs.info('InstanceSyncUtils.syncVersions - No versions to sync for application ' + thisApplication + ' on instance ' + thisInstance);
	}
}

With the sys_id in hand, we could now build a simple query to fetch the attachment record.

var attachmentList = [];
var attachmentGR = new GlideRecord('sys_attachment');
attachmentGR.addQuery('table_name', 'x_11556_col_store_member_application_version');
attachmentGR.addQuery('table_sys_id', thisVersionId);
attachmentGR.addQuery('content_type', 'CONTAINS', 'xml');
attachmentGR.query();
while (attachmentGR.next()) {
	attachmentList.push(attachmentGR.getUniqueValue());
}

I’m still building up a list here, even though there should only be one XML attachment for each version record, but since I copied most of the code from the version function, I just left that in place. In practice, this should always be a list of one item. Assuming we have an attachment (which we always should!), we need to check the target instance to see if they already have it as well. For that, of course, we resort to making another REST API call.

var request  = new sn_ws.RESTMessageV2();
request.setHttpMethod('get');
request.setBasicAuth(this.CSU.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
request.setRequestHeader("Accept", "application/json");
request.setEndpoint('https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/sys_attachment?sysparm_fields=sys_id&sysparm_query=table_name%3Dx_11556_col_store_member_application_version%5Etable_sys_id%3D' + remoteVerId + '%5Econtent_typeCONTAINSxml');

Once we build the request, we execute it and check the results.

var response = request.execute();
if (response.haveError()) {
	gs.error('InstanceSyncUtils.syncAttachments - Error returned from attempt to fetch attachment list: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
} else if (response.getStatusCode() == '200') {
	var jsonString = response.getBody();
	var jsonObject = {};
	try {
		jsonObject = JSON.parse(jsonString);
	} catch (e) {
		gs.error('InstanceSyncUtils.syncAttachments - Unparsable JSON string returned from attempt to fetch attachment list: ' + jsonString);
	}
	if (Array.isArray(jsonObject.result)) {
		if (jsonObject.result.length == 0) {
			this.sendAttachment(targetGR, attachmentList[0], remoteVerId, thisVersion, thisApplication);
		}
	} else {
		gs.error('InstanceSyncUtils.syncAttachments - Invalid response body returned from attempt to fetch attachment list: ' + response.getBody());
	}
} else {
	gs.error('InstanceSyncUtils.syncAttachments - Invalid HTTP response code returned from attempt to fetch attachment list: ' + response.getStatusCode());
}

All together, the new syncAttachments function looks like this:

syncAttachments: function(targetGR, thisVersionId, thisVersion, thisApplication, thisInstance, remoteVerId) {
	var attachmentList = [];
	var attachmentGR = new GlideRecord('sys_attachment');
	attachmentGR.addQuery('table_name', 'x_11556_col_store_member_application_version');
	attachmentGR.addQuery('table_sys_id', thisVersionId);
	attachmentGR.addQuery('content_type', 'CONTAINS', 'xml');
	attachmentGR.query();
	while (attachmentGR.next()) {
		attachmentList.push(attachmentGR.getUniqueValue());
	}
	if (attachmentList.length > 0) {
		var request  = new sn_ws.RESTMessageV2();
		request.setHttpMethod('get');
		request.setBasicAuth(this.CSU.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
		request.setRequestHeader("Accept", "application/json");
		request.setEndpoint('https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/sys_attachment?sysparm_fields=sys_id&sysparm_query=table_name%3Dx_11556_col_store_member_application_version%5Etable_sys_id%3D' + remoteVerId + '%5Econtent_typeCONTAINSxml');
		var response = request.execute();
		if (response.haveError()) {
			gs.error('InstanceSyncUtils.syncAttachments - Error returned from attempt to fetch attachment list: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		} else if (response.getStatusCode() == '200') {
			var jsonString = response.getBody();
			var jsonObject = {};
			try {
				jsonObject = JSON.parse(jsonString);
			} catch (e) {
				gs.error('InstanceSyncUtils.syncAttachments - Unparsable JSON string returned from attempt to fetch attachment list: ' + jsonString);
			}
			if (Array.isArray(jsonObject.result)) {
				if (jsonObject.result.length == 0) {
					this.sendAttachment(targetGR, attachmentList[0], remoteVerId, thisVersion, thisApplication);
				}
			} else {
				gs.error('InstanceSyncUtils.syncAttachments - Invalid response body returned from attempt to fetch attachment list: ' + response.getBody());
			}
		} else {
			gs.error('InstanceSyncUtils.syncAttachments - Invalid HTTP response code returned from attempt to fetch attachment list: ' + response.getStatusCode());
		}
	} else {
		gs.info('InstanceSyncUtils.syncAttachments - No attachments to sync for version ' + thisVersionId + ' of application ' + thisApplication + ' on instance ' + thisInstance);
	}
}

If there was no matching attachment on the target instance, we need to send it over, which we accomplish with a new sendAttachment function modeled after the earlier sendVersion function.

sendAttachment: function(targetGR, attachmentId, remoteVerId, thisVersion, thisApplication) {
	var attachmentGR = new GlideRecord('sys_attachment');
	if (attachmentGR.get(attachmentId)) {
		gs.info('InstanceSyncUtils.sendAttachment - Sending attachment ' + attachmentGR.getDisplayValue('file_name') + ' from version ' + thisVersion + ' of application ' + thisApplication + ' to target instance ' + targetGR.getDisplayValue('instance'));
		var result = this.CSU.pushAttachment(attachmentGR, targetGR, remoteVerId);
		if (result.error) {
			gs.error('InstanceSyncUtils.sendAttachment - Error occurred attempting to push attachment ' + attachmentGR.getDisplayValue('file_name') + ' from version ' + thisVersion + ' of application ' + thisApplication + ' to target instance ' + targetGR.getDisplayValue('instance') + ': ' + result.errorCode + '; ' + result.errorMessage);
		} else if (result.status != 200 && result.status != 201) {
			gs.error('InstanceSyncUtils.sendAttachment - Invalid HTTP response code returned from attempt to push attachment ' + attachmentGR.getDisplayValue('file_name') + ' from version ' + thisVersion + ' of application ' + thisApplication + ' to target instance ' + targetGR.getDisplayValue('instance') + ': ' + result.status);
		} else if (!result.obj) {
			gs.error('InstanceSyncUtils.sendAttachment - Invalid response body returned from attempt to push attachment ' + attachmentGR.getDisplayValue('file_name') + ' from version ' + thisVersion + ' of application ' + thisApplication + ' to target instance ' + targetGR.getDisplayValue('instance') + ': ' + result.body);
		}
	}
}

That completes all of the coding for the process of syncing all of the Client instances with the Host instance. If all is working correctly, it should ensure that all member instances have the same list of instances, the same list of applications from each contributing instance, the same list of versions for each application, and the associated Update Set XML file attachment for each version. Of course, there is still quite a bit of testing to do to make sure that all of this works as intended, so I will put together yet another Update Set and talk about how folks can help test all of this out in our next installment.