Requirements
- Catalog Items and Record Producer usage is stored in two different tables
- Catalog Items: sc_req_item
- Record Producers: sc_item_produced_record
- We need to merge the data from both tables and show it in a Report
Solution
Let’s combine two cool features from recent releases:
Create a new remote table (System Definition → Remote Tables → Tables) and add fields: Name, Type, Count

Create remote table definition (System Definition → Remote Tables → Definitions) and set the cache to 1 hour (3600 seconds). We don’t need real-time data.

(function executeQuery(v_table, v_query) {
var months = 12; //This value should be set in a system property.
var startDate = gs.monthsAgo(months);
addUsage('sc_req_item', 'cat_item.name'); //You might want to group by sys_id instead of name
addUsage('sc_item_produced_record', 'producer.name'); //You might want to group by sys_id instead of name
function addUsage(table, groupBy) {
new GlideQuery(table)
.where('sys_created_on', '>', startDate)
.aggregate('count')
.groupBy(groupBy)
.select()
.forEach(function(result) {
v_table.addRow({
u_count: result.count,
u_name: result.group[groupBy],
u_type: table
});
});
}
})(v_table, v_query);
Code language: JavaScript (javascript)
This is the data in the temporary data in our new remote table.

Finally, we just need to use this data to create a nice report.
Table: u_st_catalog_item_usage
Group by: name
Aggregation: Sum → Request count

Conclusions
- “Remote table” is a confusing name. Data doesn’t need to be remote, you can get it from the same instance.
- We all should start getting used to GlideQuery. It takes some time to make the switch from GlideRecord and GlideAggregate, but it is worth it.