Hello Community,
I came across the following blocker, customer is sending data to ServiceNow in this format (see image1)
Basically this is an inventory, the idea is to summarize the inventoryCount values and insert them into another table, as you may noticed there are duplicate products which is okay. I tried using glide aggreate but it only supports 1 Group by, if I group by ProductID when inserting on the other table I won't be able to add the product Name and any other column value.
If you run the following script it will Summarize the inventoryCount by Product ID but we still are unable to pass the Product name, because these products are not inserted or exist in servicenow we don't have a "reference" field to tied them and pull their value via dot walking.
var inventory = new GlideAggregate('u_ivm_inventory');
inventory.addNotNullQuery()
inventory.groupBy('u_productid');
inventory.addAggregate('SUM', 'u_inventorycount');
inventory.query();
while (inventory.next()) {
var summary = new GlideRecord('u_ivm_product_display');
summary.setValue('u_id', inventory.getValue('u_productid'));
summary.setValue('u_in_stock_count',inventory.getAggregate('SUM', 'u_inventorycount'));
summary.update();
}
In other words I'm trying to do an SQL Rollup like on the following images
The data we get:
Once the transform is compelte, then we trigger a onComplete transform script to build the other table, the idea is to create something like:
With the current servicenow GlideAggregate has been impossible to achieve this.
