Tag: TableUtils

Aggregate List Columns, Part IX

Posted on | by snhackery

“You have to finish things — that’s what you learn from, you learn by finishing things.”
— Neil Gaiman

Last time, we attempted to wrap this whole thing up with the remaining modifications to the Content Selector Configuration Editor, but we ran into a problem with the code that we borrowed from the sn-record-picker Helper. Now that we have taken a quick detour to resolve that issue, we need to get back to our new pop-up aggregate column editor and apply the same fix to our pilfered code. As with the sn-record-picker Helper, we need to add some code to the server side to leverage the TableUtils object to get our list of tables in the extension hierarchy. Here is the new Server script, with basically the same function as the one that we added to the sn-record-picker Helper, with a few minor modifications due to the difference in our variable names.

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

With that now in place, we can update the client-side buildFieldFilter function to call the server side to get the list, and then use that list to build the new filter.

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

Now we just need to pop up the editor again and see if that actually fixes the problem that we were having earlier.

Pop-up aggregate column spec editor with field pick list corrected

That’s better! Now when I search for a field on the Incident table, I get to select from all of the fields on the table, not just the ones attached to the primary table. That’s what we wanted to see.

That takes care of the pop-up aggregate column specification editor that we cloned from the existing button/icon specification editor, so now all that is left for us to do is to tweak the code that actually saves the changes once all of the editing has been completed. The Save process actually rebuilds the entire script stored in the Script Include record, so we just need to add some code to create the aggregate column section for each table definition. Once again, we can leverage the existing buttons and icons code as a starting point, and then make the necessary changes to adapt it to use for aggregate columns. Here are the relevant lines from the Save() function in the widget’s Server script:

script += "',\n				btnarray: [";
var lastSeparator = '';
for (var b=0; b<tableTable[tableState.name].btnarray.length; b++) {
	var thisButton = tableTable[tableState.name].btnarray[b];
	script += lastSeparator;
	script += "{\n					name: '";
	script += thisButton.name;
	script += "',\n					label: '";
	script += thisButton.label;
	script += "',\n					heading: '";
	script += thisButton.heading;
	script += "',\n					icon: '";
	script += thisButton.icon;
	script += "',\n					color: '";
	script += thisButton.color;
	script += "',\n					hint: '";
	script += thisButton.hint;
	script += "',\n					page_id: '";
	script += thisButton.page_id;
	script += "'\n				}";
	lastSeparator = ",";
}
script += "]";

As we have done a number of times now, we can make a few global text replacements and alter a few variable names to align with our needs and come up with something that will work for aggregate columns specifications in very much the same way that it is currently working for buttons and icons.

script += "',\n				aggarray: [";
var lastSeparator = '';
for (var g=0; g<tableTable[tableState.name].aggarray.length; g++) {
	var thisAggregate = tableTable[tableState.name].aggarray[g];
	script += lastSeparator;
	script += "{\n					name: '";
	script += thisAggregate.name;
	script += "',\n					label: '";
	script += thisAggregate.label;
	script += "',\n					heading: '";
	script += thisAggregate.heading;
	script += "',\n					table: '";
	script += thisAggregate.table;
	script += "',\n					field: '";
	script += thisAggregate.field;
	script += "',\n					filter: '";
	script += thisAggregate.filter;
	script += "',\n					source: '";
	script += thisAggregate.source;
	script += "'\n				}";
	lastSeparator = ",";
}
script += "]";

And that’s all there is to that. That should be everything now, so all that is left to do is to bundle all of this into an Update Set so that folks can play along at home. This effort has been primarily focused on the components of the Customizing the Data Table Widget project, but it has also involved elements of the Configurable Data Table Widget Content Selector series as well as the Content Selector Configuration Editor. Additionally, many of the Service Portal pages that use these components, such as the Service Portal User Directory, also include the Dynamic Service Portal Breadcrumbs widget. Rather than create a new version for each and every one of these interrelated project, I think I will just lump everything together into a single Update Set and call it version 2.0 of the Customizing the Data Table Widget project. Since many of the widgets involved also utilize the Service Portal Form Fields, that will get pulled into the Update Set as well, and just for good measure, I think I will toss in the sn-record-picker Helper, too. That one is not actually directly related to all of the others, but we did steal some code from there, so there might be a few folks who may want to take a look at that one. You can download the whole lot from here. As always, if you have any comments, questions, concerns, or issues, please leave a comment below. All feedback is always welcome. And if you have made it this far, thanks for following along all the way to the end!

But wait … there’s more!

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

Posted on | by snhackery

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

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

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

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

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

The current filter that is built turns out to be:

name=(name of selected table)

… when what it really needs to be is:

nameIN(list of all tables in the heirarchy)

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

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

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

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

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

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

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

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

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