“I want to try it to see what it’s like and see what my stuff looks like when I take it from inception to completion.”
— Charlie Kaufman
Last time, we started the work of modifying the core widget of the data table widget collection. We took care of all of the code that imports the configuration data from the various wrapper widgets in the collection, and now we need to get down to the business of actually putting real data in the new aggregate list columns. To begin, we need to take a look at the code that pulls in the regular data for each row in the list.
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;
}
}
}
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.sys_id = gr.getValue('sys_id');
record.targetTable = gr.getRecordClassName();
data.list.push(record);
}
Basically, this code creates an array called data.list and then for each row creates an object called record, populates the record object from the GlideRecord, and then pushes the record object into the list. What we will want to do is add data to the record object before it gets pushed onto the list. The sys_id of the record will actually be useful to us, so it seems as if the best place to insert our code would be after the sys_id is established, but before the data.list.push(record) occurs. Here is what I came up with:
record.aggValue = [];
if (data.aggarray.length > 0) {
for (var j=0; j<data.aggarray.length; j++) {
record.aggValue.push(getAggregateValue(record.sys_id, data.aggarray[j]));
}
}
To store the values, I added a value list to the record object. This is done whether or not any aggregate columns have been defined, just so there is something there regardless. Then we check to see if there actually are any aggregate columns defined, and if so, we loop through the definitions and then push a value onto the array for each definition. The value itself will be determined by a new function called getAggregateValue that takes the sys_id of the row and the aggregate column definition as arguments. We will need to build out that function, but for now, we can just return a hard-coded value, just to make sure that all is working before we dive into that.
function getAggregateValue(sys_id, config) {
return 10;
}
Now that we have an array of values, we will need to go back into the HTML and modify the aggregate column section to pull data from this array. That section of the HTML now looks like this:
<td ng-repeat="aggValue in item.aggValue" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
{{aggValue}}
</td>
That should be enough to take it out for a spin and see if we broke anything. Let’s have a look.
Cool! So far, so good. Now we just need to go back into that getAggregateValue function definition and replace the hard-code value of 10 with some actual logic to pull the real data out of the database. For that, we will use a GlideAggregate on the configured table using the configured field and the sys_id of the current row. And if there is an optional filter present, we simply concatenate that to the end of the primary query.
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'));
}
return value;
}
Now we can take another quick look and see if that finally gets us what we have been after all along.
Voila! There it is — a column containing a count of selected related records. Now that wasn’t so hard, was it? Of course, we are not done quite yet. The SNH Data Table from JSON Configuration widget is not the only wrapper widget in the collection. We will need to add support for aggregate columns to the SNH Data Table from Instance Definition widget, as well as the SNH Data Table from URL Definition widget, the widget designed to work with the Configurable Data Table Widget Content Selector. Also, we have the Content Selector Configuration Editor, which allows you to create the JSON configuration files for both the Configurable Data Table Widget Content Selector and the SNH Data Table from JSON Configuration widget. That will have to be modified to support aggregate column specifications as well. None of that is super challenging now that we have things working, but it all needs to be done, so we will jump right into that next time out.
