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.