Tag: Data Table Widget

Collaboration Store, Part LXXXII

Posted on | by snhackery

“It’s really complex to make something simple.”
— Jack Dorsey

Last time, we wrapped up an initial version of the storefront and released a new Update Set in the hopes that a few folks out there would pull it down and take it for a spin. While we wait patiently to hear back from anyone who might have been willing to download the new version and run it through its paces, let’s see if we can’t add a little more functionality to our shopping experience. The page that we laid out had a place for two widgets, but we only completed the primary display widget so far. The other, smaller portion of the screen was reserved for some kind of search/filter widget to help narrow down the results for installations where a large number of applications have been shared with the community. Adding that second widget to the page would give us something like this:

Storefront with new search/filter widget added

Rather than have the two widgets speak directly to one another, my thought was that we could use the same technique that was used with the Configurable Data Table Widget Content Selector bundled with the SNH Data Table Widgets. Instead of having one widget broadcast messages and the other widget listen for those messages, the Content Selector communicates with the Data Table widget via the URL. Whenever a selection is made, a URL is constructed from the selections, and then the Content Selector branches to that URL where both the Content Selector and the Data Table widget pull their control information from the shared URL query parameters. We can do exactly the same thing here for the same reasons.

For this initial attempt, I laid out a generic search box and a number of checkboxes for various states of applications on the local instance. To support that, we can use the following URL parameters: search, local, current, upgrade, and notins. In the widget, we can also use the same names for the variables used to store the information, and we can populate the variables from the URL. In fact, that turns out to be the entirety of the server-side code.

(function() {
	data.search = $sp.getParameter('search') ? decodeURIComponent($sp.getParameter('search')) : '';
	data.local = $sp.getParameter('local') == 'true';
	data.current = $sp.getParameter('current') == 'true';
	data.upgrade = $sp.getParameter('upgrade') == 'true';
	data.notins = $sp.getParameter('notins') == 'true';
})();

And here is the HTML that I put together to display the information.

<div class="panel">
  <div class="row">
    <div class="col">
      <p>&nbsp;</p>
    </div>
  </div>
  <div class="row">
    <div class="col">
      <input ng-model="c.data.search" name="search" id="search" type="text" class="form-control" placeholder="&#x1F50D;"/>
    </div>
  </div>
  <div class="row">
    <div class="col">
      <input ng-model="c.data.local" name="local" id="local" type="checkbox"/>
      <label for="local">${Local}</label>
    </div>
  </div>
  <div class="row">
    <div class="col">
      <input ng-model="c.data.current" name="current" id="current" type="checkbox"/>
      <label for="current">${Current}</label>
    </div>
  </div>
  <div class="row">
    <div class="col">
      <input ng-model="c.data.upgrade" name="upgrade" id="upgrade" type="checkbox"/>
      <label for="upgrade">${Upgrade Available}</label>
    </div>
  </div>
  <div class="row">
    <div class="col">
      <input ng-model="c.data.notins" name="new" id="new" type="checkbox"/>
      <label for="notins">${Not Installed}</label>
    </div>
  </div>
  <div class="row">
    <div class="col text-center">
      <button ng-click="search()" class="btn btn-primary" title="${Click to search the Collaboration Store}">${Search}</button>
    </div>
  </div>
</div>

To format things a little nicer, and to kick that little magnifying glass search icon over to the right, we also need to throw in some CSS.

.col {
  padding: .5vw;
}

label {
  margin-left: .5vw;
}

::placeholder {
  text-align: right;
}

There is one client-side function referenced in the HTML for the button click, and that’s pretty much all there is to the widget’s Client script.

api.controller = function($scope, $location) {
	var c = this;

	$scope.search = function() {
		var search = '?id=' + $location.search()['id'];
		if (c.data.search) {
			search += '&search=' + encodeURIComponent(c.data.search);
		}
		if (c.data.local) {
			search += '&local=true';
		}
		if (c.data.current) {
			search += '&current=true';
		}
		if (c.data.upgrade) {
			search += '&upgrade=true';
		}
		if (c.data.notins) {
			search += '&notins=true';
		}
		window.location.search = search;
	};
};

The search() function builds up a URL query string based on the operator’s input and then updates the current location with the new query string, essentially branching to the new location. When the new page loads, both the search widget and the storefront widget can then pull their information from the current URL. We can test all of this out now by saving the new widget, pulling up our collaboration_store page in the Service Portal Designer, and then dragging our new widget onto the page in the space already reserved for that purpose.

Dragging the new widget onto the existing page

With that completed, we can now try out the page and see that entering some data and clicking on the Search button actually does reload the page with a new URL. However, at this point the content of the primary page never changes because we have yet to add code to the main widget to pull in the URL parameters and then use that data to adjust the database query. That sounds like a good subject for our next installment.

Scripted Value Columns, Enhanced, Part III

Posted on | by snhackery

“It’s not about ideas. It’s about making ideas happen.”
— Scott Belsky

Last time, we played around with the Customer column in our new example data table, and today we will jump back over to the Labor Hours column and see if we can create that mouse-over breakdown of who put in the hours. The first thing that we will need to do is to gather up the data, so will need to add a groupBy to our GlideAggregate so that we can obtain the hours per technician.

var timeGA = new GlideAggregate('time_card');
timeGA.addQuery('task', item.sys_id);
timeGA.addAggregate('SUM', 'total');
timeGA.groupBy('user');
timeGA.orderBy('user');
timeGA.query();

Once we have the data, we will need to format it for display. There are a number of different ways that we can approach this, including straight CSS and many creative variations, but the easiest thing to do is to just use the title attribute of a span, which seems to accomplish the same thing.

Mouseover breakdown of labor hours

To create the text, we can establish some variables before we go into the while loop, and then inside the loop we can build up the text, one person’s hours at a time.

var hours = 0;
var tooltip = '';
var separator = '';
var timeGA = new GlideAggregate('time_card');
timeGA.addQuery('task', item.sys_id);
timeGA.addAggregate('SUM', 'total');
timeGA.groupBy('user');
timeGA.orderBy('user');
timeGA.query();
while (timeGA.next()) {
	var total = timeGA.getAggregate('SUM', 'total') * 1;
	hours += total;
	tooltip += separator + timeGA.getDisplayValue('user') + ' - ' + total.toFixed(2);
	separator = '\n';
}

Once we accumulate the total hours and build up the tooltip text, we can then format the HTML we want, but only when there are hours charged to the task.

if (hours > 0) {
	response = '<span style="text-align: right; width: 100%;" title="' + tooltip + '">' + hours.toFixed(2) + '</span>';
}

Putting it all together, our new function to provide right-justified total hours with a mouseover breakdown looks like this:

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

	var hours = 0;
	var tooltip = '';
	var separator = '';
	var timeGA = new GlideAggregate('time_card');
	timeGA.addQuery('task', item.sys_id);
	timeGA.addAggregate('SUM', 'total');
	timeGA.groupBy('user');
	timeGA.orderBy('user');
	timeGA.query();
	while (timeGA.next()) {
		var total = timeGA.getAggregate('SUM', 'total') * 1;
		hours += total;
		tooltip += separator + timeGA.getDisplayValue('user') + ' - ' + total.toFixed(2);
		separator = '\n';
	}
	if (hours > 0) {
		response = '<span style="text-align: right; width: 100%;" title="' + tooltip + '">' + hours.toFixed(2) + '</span>';
	}

	return response;
}

I think that is enough examples for folks to get the basic idea. Adding the ability to include HTML with a scripted value opens up a number of possibilities. We have just explored a couple of them here, but I am sure that specific requirements will drive many other variations from those willing to give it a try and see what they can do with it.

Here is an Update Set with the modifications, including this new example page. Feedback can be left here in the comments, or in the discussion area where it has been posted out on Share. If you have been able to utilize this feature for anything interesting, a screenshot would definitely be something in which folks would have an interest, so please let us all in on what you were able to accomplish.

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, Enhanced

Posted on | by snhackery

“Never let an inventor run a company. You can never get him to stop tinkering and bring something to market.”
— Ernst Friedrich Schumacher

Since I posted a new version of the SNH Data Table Widgets out on Share with the new Scripted Value Columns feature, I have been playing around with it for various purposes, and have decided to make a little tweak to the core Data Table widget to accommodate some additional capabilities. My interest was in having the value provider script have the ability to return HTML as part of the value, which I was able to do, but when I did that, the table would simply display the HTML as text rather than process it to format the value. So I dug into the HTML in the core SNH Data Table widget and changed this:

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

… to this:

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

That sort of worked, but it still stripped out some portions of the HTML for safety reasons. To get around that problem, I had to change things to this:

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

… and then add this new function to the Client script of the widget:

$scope.trustAsHtml = function(string) {
	return $sce.trustAsHtml(string);
};

That was better, but still not exactly what I wanted, because when I tried to right justify some numeric values, they still came out on the left. The data was on the right side of the span, but the span was only as wide as the data, so that really did not do what I wanted. I wanted the data to be on the right side of the table cell, not the span inside of the table cell. However, I was able to solve that problem with a little extra style magic, so now I had this.

<td ng-repeat="obj in item.svcValue" role="cell" class="sp-list-cell" ng-class="{selected: item.selected}" tabindex="0">
  <span ng-bind-html="trustAsHtml(obj.value)" style="display: flex;"></span>
</td>

Even better, but still not exactly what I wanted. With the display set to flex, the carriage returns above and below the span end up rendered in the cell along with the (finally!) right-justified value. To solve that problem, I ended up putting the td, the span, and the closing td tags all on one line with no white space of any kind in between. Now I finally had what I was after. I just needed to do a little trial and error to see what I could do with it.

I was working on a list of assigned Tasks for any given Assignment Group, and one of the columns that I wanted to include on the list was the number of hours spent on the tasks so far. To get the value, I used a GlideAggregate on the time_card table.

var hours = 0;
var timeGA = new GlideAggregate('time_card');
timeGA.addQuery('task', item.sys_id);
timeGA.addAggregate('SUM', 'total');
timeGA.query();
if (timeGA.next()) {
	hours = timeGA.getAggregate('SUM', 'total');
}

That gave me the number of hours charged to the task from anyone from any group, whether or not the Time Card had been approved. I could have run a different query for a different number, but this was the data that I wanted to display on the table. Returning the hours value alone aligned the column on the left, though, and I wanted the values to be right justified. With the modifications made to core data table widget above, I was able to wrap the value with a right justified span and obtain the result that I wanted to see.

return '<span style="text-align: right; width: 100%;">' + (hours * 1).toFixed(2) + '</span>';

Things just look better with numbers lined up on the right.

Hours column lined up on the right

I like it, but there are still some things that we could do to make it better. It would be nice to know who put in these hours. Maybe a tooltip with a breakdown or a modal pop-up with the details would be nice. Also, it might be nice to have nothing at all in the column if the value is zero. That would make the rows with values stand out a little more. So many possibilities …

Oh, and we haven’t even begun to take a look at that Customer column just yet. Let’s play around with that and more next time out.

Scripted Value Columns, Part VIII

Posted on | by snhackery

“Too many men work on parts of things. Doing a job to completion satisfies me.”
— Richard Proenneke

Last time, we wanted to wrap up the modifications on the last two wrapper widgets and put out a new Update Set, but we discovered that we missed an important element in our list of things that would need to be modified, the Configurable Data Table Widget Content Selector widget. We need to take a look at that guy and see what needs to be done to accommodate scripted value columns, and then retest the third wrapper widget, which shares the page with this component.

A quick scan of the Server script for aggarray comes up empty, but in the Client script, we come across this:

s.aggregates = '';
if (tableInfo[state].aggarray && Array.isArray(tableInfo[state].aggarray) && tableInfo[state].aggarray.length > 0) {
	s.aggregates = JSON.stringify(tableInfo[state].aggarray);
}

Making a copy of that and doing a little string replacement here and there gives us an equivalent block of code for the new scripted value column configurations.

s.scripteds = '';
if (tableInfo[state].svcarray && Array.isArray(tableInfo[state].svcarray) && tableInfo[state].svcarray.length > 0) {
	s.scripteds = JSON.stringify(tableInfo[state].svcarray);
}

And that seems to be all there is to that. Now we can go back to our last test and run it again to see if that fixed our problem.

Successful test of the third wrapper widget

That’s better! Now we have a column for the Last Comment, and we even have a row with some data in it. Good deal. And just to check on the content selector widget, we can look at the URL that it built to see how the configuration options for the scripted value columns appeared in the URL.

/sp?id=my_things&table=incident&filter=caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^active%3Dtrue&fields=number,opened_by,opened_at,short_description&scripteds=[{"heading":"Last Comment","name":"last_comment","label":"Last Comment","script":"global.ScriptedJournalValueProvider"}]&aggregates=&buttons=&refpage={"sys_user":"user_profile"}&px=requester&sx=open&spa=1&p=1&o=opened_at&d=asc

Well, that’s the whole thing, but we can zoom in on the part in which we have an interest.

scripteds=[{"heading":"Last Comment","name":"last_comment","label":"Last Comment","script":"global.ScriptedJournalValueProvider"}]

So that is the last of the wrapper widgets, and unless we have left something else out, that’s the last of the work to be done to implement this new feature. Now all that is left is to bundle the whole thing up into a new Update Set and post it out on Share as a new version.

Here is the new Update Set, and here is where you can find it on Share. If you happen to use it, find it to be of value, or run into any issues, please let us all know in the comments below.

Scripted Value Columns, Part VII

Posted on | by snhackery

“Unplanned occurrences are reminders to check your tendency to think that you’re the one in control.”
— James Martin

Last time, we created another example of how one might utilize the new scripted value column feature, this time with catalog item variables instead of Incident journal entries. There are a number of other things that we could try, but two examples should be enough to get the point across, and I’ll leave it to others to come up with additional examples of their own.

We still have two more wrapper widgets to update, though, and we still have that annoying misalignment between the original columns and the new. Here is the way things come out right now:

Misalignment of original columns and new columns

… and here is the way that it should look:

Correct alignment of original columns and new columns

I was able to capture that second image because I found and fixed the problem. I had to replace this HTML:

<sn-avatar ng-if="item[field].value && item[field].type == 'reference' && item[field].table == 'sys_user'" primary="item[field].value" class="avatar-small" show-presence="true" enable-context-menu="false"></sn-avatar>
<a ng-if="$first" href="javascript:void(0)" ng-click="go(item.targetTable, item)" aria-label="${Open record}: {{::item[field].display_value}}">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</a>
<a ng-if="!$first && item[field].type == 'reference' && item[field].value" href="javascript:void(0)" ng-click="referenceClick(field, item)" aria-label="${Click for more on }{{::item[field].display_value}}">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</a>
<span ng-if="!$first && item[field].type != 'reference'">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</span>

… with this:

<span style="display: inline-flex;">
  <span style="display: inline-flex;" ng-if="item[field].value && item[field].type == 'reference' && item[field].table == 'sys_user'">
    <sn-avatar primary="item[field].value" class="avatar-small" show-presence="true" enable-context-menu="false"></sn-avatar>
    &nbsp;
  </span>
  <a ng-if="$first" href="javascript:void(0)" ng-click="go(item.targetTable, item)" aria-label="${Open record}: {{::item[field].display_value}}">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</a>
  <a ng-if="!$first && item[field].type == 'reference' && item[field].value" href="javascript:void(0)" ng-click="referenceClick(field, item)" aria-label="${Click for more on }{{::item[field].display_value}}">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</a>
  <span ng-if="!$first && item[field].type != 'reference'">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</span>
</span>

Now, without getting into too much detail that no one really cares about, the source of the problem was the sn-avatar tag, which I added a while back so that user columns would have the avatar in front of the name. For some reason, the tag renders out a carriage return and a handful of spaces just before the avatar image. With the ng-if attribute set to false, this collection of white space is still rendered on the page, even when the avatar itself is not. I solved that problem by wrapping the avatar tag with a span and putting the ng-if attribute on the outer span rather than on the sn-avatar tag. That took care of things for columns where there was no avatar, but the user columns, which show the avatar, were still out of alignment with the rest of the columns. Adding style=”display: inline-flex;“ took care of that problem with the avatar, but then the user name ended up underneath the avatar instead of next to it. To solve that problem, I wrapped the whole thing in another span with the same style attribute. Now everything lines up the way that it should.

Now that that is out of the way, we still have two more wrapper widgets to update. Let’s jump into the SNH Data Table from Instance Definition and do the same kind of searching we did before, looking for some code that might need to be copied and modified. On this particular widget, such a search turns up nothing at all in either the Server script or the Client script, so the only thing that we really need to do is to add another entry to the Option schema for our new scripted value column specification.

{"hint":"A JSON object containing the specifications for scripted value columns",
"name":"scripteds",
"default_value":"",
"section":"Behavior",
"label":"Scripted Value Column Specifications (JSON)",
"type":"String"}

To test this, we can modify our scripted_value_test_2 page to use this widget instead of the SNH Data Table from JSON Configuration widget, and then transfer our configuration options from the Script Include to the widget options.

Widget configuration options for the modified wrapper widget

Now all we need to do is to save it and then run out to the Service Portal and take a quick peek.

Testing the modified SNH Data Table from JSON Configuration widget

So that all looks good. And much, much better now that the column data all lines up as it should! It’s nice to finally have that fixed. That takes care of wrapper widget #2. Now let’s take a look at that last one, the SNH Data Table from URL Definition widget. The only line that appears to require modification is this one:

copyParameters(data, ['aggregates', 'buttons', 'refpage', 'bulkactions']);

… which we can convert to this to pick up our new configuration option:

copyParameters(data, ['scripteds', 'aggregates', 'buttons', 'refpage', 'bulkactions']);

Now we need to test it, so we will need to find or create a page that use this widget. Let’s take a look at the ones that are already out there by checking out the Related List down at the bottom of the form.

Pages that use the SNH Data Table from URL Definition widget

The page my_things looks like a good candidate, so we can take a look at the configuration script that it uses and then edit it to add one or more scripted value columns. One of the tables utilized on that page is the Incident table, so let’s go ahead and use our existing value provider script to add a journal entry column to one of those.

name: 'incident',
displayName: 'Incident',
open: {
	filter: 'caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^active=true',
	fields: 'number,opened_by,opened_at,short_description',
	svcarray: [{
		name: 'last_comment',
		label: 'Last Comment',
		heading: 'Last Comment',
		script: 'global.ScriptedJournalValueProvider'
	}],
	aggarray: [],
	btnarray: [],
	refmap: {
		sys_user: 'user_profile'
	},
	actarray: []
}

Now let’s take a look.

First test of the modified SNH Data Table from URL Definition widget

Well, that didn’t work! It’s always something. Even if there were no comments on any of these Incidents, we should still have a column heading for our new scripted value column. I don’t think this problem is in the widget that we just modified, however. This widget shares the page with the Configurable Data Table Widget Content Selector widget, and that is a widget that we have not even touched. That is going to have to be modified to accommodate our new feature as well, as it builds the URL that the SNH Data Table from URL Definition widget turns to for its configuration information. This was not on our list of things to do for this feature, but it definitely needs to be done.

I was hoping to wrap things up with this installment, but now we have a new widget to modify and more testing to do, so I think we will just save all of that, plus the Update Set creation, for our next time out.

Scripted Value Columns, Part VI

Posted on | by snhackery

“Do not be too timid and squeamish about your actions. All life is an experiment. The more experiments you make the better.”
— Ralph Waldo Emerson

Last time, we used the new scripted value columns feature to create columns from the comments and work notes of an Incident. Today we are going to do something similar, but instead of working with journal entries, we will try something with the optional variables that can be associated with Service Catalog items. For our demonstration, we will use the Executive Desktop catalog item, which has a number of defined ordering options.

Executive Desktop catalog item

Let’s see if we can’t create a list of all orders for this item, and include some of the catalog item variables on the list. To begin, let’s make another copy of our example scripted value provider and call it ScriptedCatalogValueProvider.

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

	getScriptedValue: function(item, config) {
		return Math.floor(Math.random() * 100) + '';
	},

	type: 'ScriptedCatalogValueProvider'
};

Let’s also make a copy of our last configuration script and change things over from the Incident table to the Requested Item table, and define some scripted value columns for some of the catalog item variables associated with this item.

var ScriptedValueConfig2 = Class.create();
ScriptedValueConfig2.prototype = Object.extendsObject(ContentSelectorConfig, {
	initialize: function() {
	},

	perspective: [{
		name: 'all',
		label: 'all',
		roles: ''
	}],

	state: [{
		name: 'all',
		label: 'All'
	}],

	table: {
		all: [{
			name: 'sc_req_item',
			displayName: 'Requested Item',
			all: {
				filter: 'cat_item=e46305bdc0a8010a00645e608031eb0f',
				fields: 'number,request,requested_for',
				svcarray: [{
					name: 'cpu',
					label: 'CPU Speed',
					heading: 'CPU Speed',
					script: 'global.ScriptedCatalogValueProvider'
				},{
					name: 'memory',
					label: 'Memory',
					heading: 'Memory',
					script: 'global.ScriptedCatalogValueProvider'
				},{
					name: 'drive',
					label: 'Hard Drive',
					heading: 'Hard Drive',
					script: 'global.ScriptedCatalogValueProvider'
				},{
					name: 'os',
					label: 'Operating System',
					heading: 'Operating System',
					script: 'global.ScriptedCatalogValueProvider'
				}],
				aggarray: [],
				btnarray: [],
				refmap: {},
				actarray: []
			}
		}]
	},

	type: 'ScriptedValueConfig2'
});

Finally, let’s make a copy of our last test page and call it scripted_value_test_2, and then edit the widget options to use our new configuration file. Now let’s jump out to the Service Portal and pull up the page and see what we have so far.

First test of our new portal page

So far, so good. Everything seems to work, but of course all we have in our new columns is the random numbers that we threw in there for demonstration purposes earlier. But that just means that all we have left to do now is to figure out what kind of script we need to pull out the actual variable values that are associated with each catalog item order. To begin, let’s map our variable names, which are also our column names, to their catalog item variable (question) sys_ids.

questionMap: {
	cpu: 'e46305fbc0a8010a01f7d51642fd6737',
	memory: 'e463064ac0a8010a01f7d516207cd5ab',
	drive: 'e4630669c0a8010a01f7d51690673603',
	os: 'e4630688c0a8010a01f7d516f68c1504'
}

This will allow us to use some common code for all of the columns by passing in the correct sys_id for the catalog item variable associated with that column.

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

	var column = config.name;
	if (this.questionMap[column]) {
		response = this.getVariableValue(this.questionMap[column], item.sys_id);
	}

	return response;
}

Now we need to build the getVariableValue function, which takes the sys_id of the question and the sys_id of the requested item as arguments. To locate the value selected for this question for this order, we use the sc_item_option_mtom table, which maps requested items to individual question responses.

getVariableValue: function(questionId, itemId) {
	var response = '';

	var mtomGR = new GlideRecord('sc_item_option_mtom');
	mtomGR.addQuery('request_item', itemId);
	mtomGR.addQuery('sc_item_option.item_option_new', questionId);
	mtomGR.query();
	if (mtomGR.next()) {
		response = mtomGR.getDisplayValue('sc_item_option.value');
	}

	return response;
}

Now let’s save that and give the page another look.

Second test of our new portal page

Well, that’s better, but it is still not exactly what we want. The values that appear in the columns are the raw values for the variables, but what we would really like to see is the display value. To get that, we have to go all the way back to the original question choices and find the choice record that matches both the question and the answer. For that, you need the sys_id of the question and the value of the answer.

getDisplayValue: function(questionId, value) {
	var response = '';

	var choiceGR = new GlideRecord('question_choice');
	choiceGR.addQuery('question', questionId);
	choiceGR.addQuery('value', value);
	choiceGR.query();
	if (choiceGR.next()) {
		response = choiceGR.getDisplayValue('text');
	}

	return response;
}

To utilize this new function, we have to tweak our getVariableValue function just a little bit to make the call.

getVariableValue: function(questionId, itemId) {
	var response = '';

	var mtomGR = new GlideRecord('sc_item_option_mtom');
	mtomGR.addQuery('request_item', itemId);
	mtomGR.addQuery('sc_item_option.item_option_new', questionId);
	mtomGR.query();
	if (mtomGR.next()) {
		var value = mtomGR.getDisplayValue('sc_item_option.value');
		if (value) {
			response = this.getDisplayValue(questionId, value);
		}
	}

	return response;
}

Now let’s take one more look at that page now that we have made these modifications.

Third and final test of our new portal page

That’s better! Now we have the same text in our columns that the user saw when placing the order, and that’s what we really want to see here. Or at least, that’s what I wanted to see. You may be interested in something completely different, but that’s the whole point of this approach. You can basically script whatever you want to put whatever you want to put in whatever column or columns you would like to define. These are just a couple of examples, but there really is no limit to what you might be able to do with a little imagination and some Javascript.

So here is the final version of our second close-to-real-world example value provider script:

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

	questionMap: {
		cpu: 'e46305fbc0a8010a01f7d51642fd6737',
		memory: 'e463064ac0a8010a01f7d516207cd5ab',
		drive: 'e4630669c0a8010a01f7d51690673603',
		os: 'e4630688c0a8010a01f7d516f68c1504'
	},

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

		var column = config.name;
		if (this.questionMap[column]) {
			response = this.getVariableValue(this.questionMap[column], item.sys_id);
		}

		return response;
	},

	getVariableValue: function(questionId, itemId) {
		var response = '';

		var mtomGR = new GlideRecord('sc_item_option_mtom');
		mtomGR.addQuery('request_item', itemId);
		mtomGR.addQuery('sc_item_option.item_option_new', questionId);
		mtomGR.query();
		if (mtomGR.next()) {
			var value = mtomGR.getDisplayValue('sc_item_option.value');
			if (value) {
				response = this.getDisplayValue(questionId, value);
			}
		}

		return response;
	},

	getDisplayValue: function(questionId, value) {
		var response = '';

		var choiceGR = new GlideRecord('question_choice');
		choiceGR.addQuery('question', questionId);
		choiceGR.addQuery('value', value);
		choiceGR.query();
		if (choiceGR.next()) {
			response = choiceGR.getDisplayValue('text');
		}

		return response;
	},

	type: 'ScriptedCatalogValueProvider'
};

The added columns still do not align with the original columns, which I would like to fix before I build a new Update Set, and we still have a couple more wrapper widgets to address, but I think we are getting close. Maybe we can wrap this whole thing up in our next installment.

Scripted Value Columns, Part V

Posted on | by snhackery

“Make incremental progress; change comes not by the yard, but by the inch.”
— Rick Pitino

Last time, we had enough parts cobbled together to demonstrate that the concept actually works. Of course, all we had to show for it was some random numbers, but that told us that the specified script was being called for each row, which is what we were after. Now that we know that the basic structure is performing as desired, we can revisit the configurable Script Include component and see if we can come up with some actual use cases that might be of value to someone.

One of the questions that triggered this idea was related to comments and work notes on Incidents. Assuming that the main record in the table is an Incident, we can clone our example Script Include to create one dedicated to pulling data out of the latest comment or work note on an Incident. We can call this new Script Include ScriptedJournalValueProvider.

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

	getScriptedValue: function(item, config) {
		return Math.floor(Math.random() * 100) + '';
	},

	type: 'ScriptedJournalValueProvider'
};

We will want to delete the example code in the getScriptedValue function and come up with our own, but other than that, the basic structure remains the same. Assuming that we want our script to be able to handle a number of attributes of an Incident Journal entry, we can use the name of the column to determine which function will fetch us our value.

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

	var column = config.name;
	if (column == 'last_comment') {
		response = this.getLastComment(item, config);
	} else if (column == 'last_comment_by') {
		response = this.getLastCommentBy(item, config);
	} else if (column == 'last_comment_on') {
		response = this.getLastCommentOn(item, config);
	} else if (column == 'last_comment_type') {
		response = this.getLastCommentType(item, config);
	}

	return response;
}

This way, we can point to this same script in multiple columns and the name of the column will determine which value from the last comment or work note gets returned.

Since all of the functions will need the data for the last entry, we should create a shared function that they all can leverage to obtain the record. As with many things on the ServiceNow platform, there are a number of ways to go about this, but for our demonstration purposes, we will read the sys_journal_field table looking for the last entry for the Incident in the current row.

getLastJournalEntry: function(sys_id) {
	var journalGR = new GlideRecord('sys_journal_field');
	journalGR.orderByDesc('sys_created_on');
	journalGR.addQuery('name', 'incident');
	journalGR.addQuery('element_id', sys_id);
	journalGR.setLimit(1);
	journalGR.query();
	journalGR.next();
	return journalGR;
}

Now that we have a common way to obtain the GlideRecord for the latest entry, we can start building our functions that extract the requested data value. Here is the one for the comment text.

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

	var journalGR = this.getLastJournalEntry(item.sys_id);
	if (journalGR.isValidRecord()) {
		response = journalGR.getDisplayValue('value');
	}

	return response;
}

The others will basically be copies of the above, modified to return different values based on their purpose. The whole thing, all put together, now looks like this.

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

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

		var column = config.name;
		if (column == 'last_comment') {
			response = this.getLastComment(item, config);
		} else if (column == 'last_comment_by') {
			response = this.getLastCommentBy(item, config);
		} else if (column == 'last_comment_on') {
			response = this.getLastCommentOn(item, config);
		} else if (column == 'last_comment_type') {
			response = this.getLastCommentType(item, config);
		}

		return response;
	},

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

		var journalGR = this.getLastJournalEntry(item.sys_id);
		if (journalGR.isValidRecord()) {
			response = journalGR.getDisplayValue('value');
		}

		return response;
	},

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

		var journalGR = this.getLastJournalEntry(item.sys_id);
		if (journalGR.isValidRecord()) {
			response = journalGR.getDisplayValue('sys_created_by');
		}

		return response;
	},

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

		var journalGR = this.getLastJournalEntry(item.sys_id);
		if (journalGR.isValidRecord()) {
			response = journalGR.getDisplayValue('sys_created_on');
		}

		return response;
	},

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

		var journalGR = this.getLastJournalEntry(item.sys_id);
		if (journalGR.isValidRecord()) {
			response = journalGR.getDisplayValue('element');
		}

		return response;
	},

	getLastJournalEntry: function(sys_id) {
		var journalGR = new GlideRecord('sys_journal_field');
		journalGR.orderByDesc('sys_created_on');
		journalGR.addQuery('name', 'incident');
		journalGR.addQuery('element_id', sys_id);
		journalGR.setLimit(1);
		journalGR.query();
		journalGR.next();
		return journalGR;
	},

	type: 'ScriptedJournalValueProvider'
};

Now that we have a Script Include to utilize, we need to put together a new page so that we can configure it to make use of it so that we can test it out. Let’s make a quick copy of the page that we were using for testing last time and call it scripted_value_test. Also, let’s make a quick copy of the test configuration script that we were using earlier and call it ScriptedValueConfig.

var ScriptedValueConfig = Class.create();
ScriptedValueConfig.prototype = Object.extendsObject(ContentSelectorConfig, {
	initialize: function() {
	},

	perspective: [{
		name: 'all',
		label: 'all',
		roles: ''
	}],

	state: [{
		name: 'all',
		label: 'All'
	}],

	table: {
		all: [{
			name: 'incident',
			displayName: 'Incident',
			all: {
				filter: 'caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^active=true',
				fields: 'number,opened_by,opened_at,short_description',
				svcarray: [{
					name: 'last_comment_on',
					label: 'Last Comment',
					heading: 'Last Comment',
					script: 'global.ScriptedJournalValueProvider'
				},{
					name: 'last_comment_by',
					label: 'Last Comment By',
					heading: 'Last Comment By',
					script: 'global.ScriptedJournalValueProvider'
				}],
				aggarray: [],
				btnarray: [],
				refmap: {
					sys_user: 'user_profile'
				},
				actarray: []
			}
		}]
	},

	type: 'ScriptedValueConfig'
});

Now let’s pull up our new page in the Service Portal Designer and point the table widget to our new configuration script.

Configuring the new test page to use the new test configuration script

Once we save that, we can pop over to the Service Portal and pull up our new page to try it out.

First test of our first real world utilization of this feature

Beautiful! Our new scripted value provider Script Include was called by the core SNH Data Table widget and it returned the requested values, which were then displayed on the list with all of the other standard table columns. That wasn’t so hard, now was it?

Of course, we still have a couple more wrapper widgets to modify (and test!), and I would like to produce another example, maybe something to do with catalog item variables, but I think we are close. One thing I see that I never noticed before, though, is that the added columns don’t quite line up with the original columns. Maybe it is a CSS thing, or maybe it is something a little more diabolical, but I want to take a look at that and see what is going on there. All of the data in the columns should be displayed consistently; I don’t like it when things don’t all line up correctly. I need to figure out what is going on there and see what I can do about it.

Anyway, we still have a little more work to do before we can wrap this all up into a new Update Set and post a new version out on Share, but we will keep plugging along in our next installment.

Scripted Value Columns, Part IV

Posted on | by snhackery

“Where you’re headed is more important than how fast you’re going.”
— Stephen Covey

Last time, we modified the core SNH Data Table widget to process the new configuration properties for scripted value columns. Before we can try it out, though, we need to update one or more of the three wrapper widgets, since no one uses the core widget directly. Probably the simplest to take on would be the SNH Data Table from JSON Configuration widget, the one that was added to process a configuration script directly. As we did with the editor and the core data table widget, we can scan the code for the aggarray and then copy any code needed as a starting point for our modifications. The only reference that appears in this widget is in the Server script, and only on a single line:

data.aggarray = data.tableData.aggarray;

We can replicate that line, and then modify the copy for our new array of scripted value columns.

data.svcarray = data.tableData.svcarray;
data.aggarray = data.tableData.aggarray;

And that’s the only thing that needs to be done to update this widget to support the new feature. Now we can build a page to try things out and see if it all works. Or better yet, maybe there is already a page out there that we can use for this quick test. Down at the bottom of the widget form there is a list of pages that already use this widget. Maybe we can tinker with one of those, just to give this a quick look.

Related list of portal pages that include the SNH Data Table from JSON Configuration widget

The table_from_json page looks like a prime candidate. All we need to do is to pull it up in the Portal Page Designer, point the configuration script option to the script that we modified earlier, and then give the page a try.

First test of the new scripted value column using random numbers as values

So, there is the “test” column that we added, filled with random numbers from our new ScriptedValueExample Script Include. This test demonstrates that our modified wrapper widget successfully passed the data from our recently edited configuration script to the core data table widget, and the core data table widget successfully handled the new configuration option and obtained the value for the new column from the specified Script Include. Sweet! Now we need to come up with some real world examples of how this feature might be employed for specific purposes, and also update the remaining wrapper widgets to accommodate this new feature. That all sounds like a good topic for our next installment.

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.