Remote table + GlideQuery POC: most used items

Requirements

  1. Catalog Items and Record Producer usage is stored in two different tables
    • Catalog Items: sc_req_item
    • Record Producers: sc_item_produced_record
  2. 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.