“Get a good idea and stay with it. Do it, and work at it until it’s done right.”
— Walt Disney
We have had a lot of fun with the Service Portal Data Table Widget on this site. So much so, in fact, that we had to make our own copy to avoid extensive modifications to a core component of the Service Portal. So far, we have created a Configurable Data Table Widget Content Selector, allowed individual columns to be links to referenced records, added buttons and icons, added User Avatars to user columns, set up the Configurable Data Table Widget Content Selector to use a JSON object to configure the widget, created an Editor for the JSON configuration object, added check boxes to the rows, added an additional extension of the base Data Table Widget to use the JSON configuration object directly, and built a User Directory using all of these custom components. That’s quite a bit of customization, but there is at least one more thing that we could do to make this all even better.
The feature that I have in mind is to have one or more columns that would include counts of related records. For example, on a list of Assignment Groups, you might want to include columns for how many people are in the group or how many open Incidents are assigned to the group. These are not columns on the Group table; these are counts of related records. It seems as if we could borrow some of the concepts from our Buttons and Icons strategy to come up with a similar approach to configuring Aggregate List Columns that could be defined when setting up the configuration for the table. You know what I always like to ask myself: How hard could it be?
Let’s take a quick look at what we need to configure a button or icon using the current version of the tools. Then we can compare that to what we would need to configure an Aggregate List Column using a similar approach. Here is the data that we collect when defining a button/icon:
- Label
- Name
- Heading
- Icon
- Color
- Hint
- Page
The first three would appear to apply to our new requirement as well, but the rest are specific to the button/icon configuration. Still, we could snag those first three, and copy all of the code that deals with those first three, and then ditch the rest and replace them with data points that will be useful to our new purpose. Our list, then, would end up looking something like this:
- Label
- Name
- Heading
- Table
- Field
- Filter
The first three would be treated exactly the same way that their counterparts are treated in the button/icon code. The rest would be unique to our purpose. Table would be the name of the table that contains the related records to be counted. Field would be the name of the reference field on that table that would contain the sys_id of the current row. Filter would be an additional query filter that would be used to further limit the records to be counted. The purpose for the Filter would be to provide for the ability to count only those records desired. For example, a Table of Incident and a Field of assigned_to would count all of the Incidents ever assigned to that person, which is of questionable value. With the ability to add a Filter of active=true, that would limit the count to just those Incidents that were currently open. That is actually a useful statistic to add to a list.
One other thing that would be useful would be the addition of a link URL that would allow you to pull up a list of the records represented in the count. Although I definitely see the value in this additional functionality, anyone who has followed this web site for any length of time knows that I don’t really like to get too wild and crazy right out of the gate. My intent is to just see if I can get the count to appear on the list before I worry too much about other features that would be nice to have, regardless of their obvious value.
So, it seems as if the place to start here would be to pull up a JSON configuration object and see if we can add a configuration for an Aggregate List Column. When we built the User Directory, we added a simple configuration file to support both the Location Roster and the Department Roster. This looks like a good candidate to use as a starting point to see if we can set up a test case. Here is the original configuration file used in the User Directory project:
var RosterConfig = Class.create();
RosterConfig.prototype = Object.extendsObject(ContentSelectorConfig, {
initialize: function() {
},
perspective: [{
name: 'all',
label: 'All',
roles: ''
}],
state: [{
name: 'department',
label: 'Department'
},{
name: 'location',
label: 'Location'
}],
table: {
all: [{
name: 'sys_user',
displayName: 'User',
department: {
filter: 'active=true^department={{sys_id}}',
fields: 'name,title,email,location',
btnarray: [],
refmap: {
cmn_location: 'location_roster'
},
actarray: []
},
location: {
filter: 'active=true^location={{sys_id}}',
fields: 'name,title,department,email',
btnarray: [],
refmap: {
cmn_department: 'department_roster'
},
actarray: []
}
}]
},
type: 'RosterConfig'
});
For our purpose, we don’t really need two state options, so we can simplify this even further by reducing this down to just one that we can call all. Then we can add our example aggregate configuration just above the button configuration. Also, since this is just a test, we will want to limit our list of people to just members of a single Assignment Group, so we can update the filter accordingly to limit the number of rows. Here is the configuration that I came up with for an initial test.
var AggregateTestConfig = Class.create();
AggregateTestConfig.prototype = Object.extendsObject(ContentSelectorConfig, {
initialize: function() {
},
perspective: [{
name: 'all',
label: 'All',
roles: ''
}],
state: [{
name: 'all',
label: 'All',
}],
table: {
all: [{
name: 'sys_user',
displayName: 'User',
all: {
filter: 'active=true^sys_idIN46c4aeb7a9fe1981002bbd372644a37b,46d44a23a9fe19810012d100cca80666,5137153cc611227c000bbd1bd8cd2005,5137153cc611227c000bbd1bd8cd2007,9ee1b13dc6112271007f9d0efdb69cd0,f298d2d2c611227b0106c6be7f154bc8',
fields: 'name,title,email,department,location',
aggarray: [{
label: 'Incidents',
name: 'incidents',
heading: 'Incidents',
table: 'incident',
field: 'assigned_to',
filter: 'active=true'
}],
btnarray: [],
refmap: {},
actarray: []
}
}]
},
type: 'AggregateTestConfig'
});
For now, I just created a simple filter using all of the sys_ids of all of the members of the Hardware group rather than bringing in the group member table and filtering on the group itself. This is not optimum, but this is just a test, and it will avoid other issues that we can discuss at a later time. The main focus at this point, though is the new aggarray property, which includes all of the configuration information that we listed earlier.
aggarray: [{
label: 'Incidents',
name: 'incidents',
heading: 'Incidents',
table: 'incident',
field: 'assigned_to',
filter: 'active=true'
}]
Now that we have a configuration script, we can create a page and try it out, even though we have not yet done any coding to support the new configuration attributes. At this point, we just want to see if the list comes up, and since nothing will be looking for our new data, that portion of the configuration object will just be ignored. I grabbed a copy of the Location Roster page from the User Directory project and then cloned it to create a page that I called Aggregate Test. Then I edited the configuration for the table to change the Configuration Script value to our new Script Include, AggregateTestConfig. I also removed the value that was present in the State field, as we only have one state, so no value is needed.
With that saved, we can run out to the Service Portal and pull up the new page and see how it looks.
Well, that’s not too bad for just a few minutes of effort. Of course, that was just the easy part. Now we have to tinker with the widgets to actually do something with this new configuration data that we are passing into the modules. That’s going to be a bit of work, of course, so we’ll start taking a look at how we want to do that next time out.
