“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.
