glide Aggregate on a string field to get the count

RudhraKAM · ‎06-01-2020

I have a requirement where I need to count the values( string but only numbers allowed in that field ) u_actual_count

We have after BR on insert or update of that field we need to aggregate that field and check if the count is greater than or equal to 100 then we need to trigger an event ( is it possible to trigger a flow using that ?)

(function executeRule(current, previous /*null when async*/ ) {

    var totalCostSum = new GlideAggregate('u_environment');
    totalCostSum.addAggregate('SUM', 'u_actual_count');
   totalCostSum.groupBy('u_actual_count');
    totalCostSum.query();
    if (totalCostSum.next()) { // in case there is no result
        var allTotalCost = 0;
        allTotalCost = totalCostSum.getAggregate('SUM', 'u_actual_count');
        gs.print('SUM of total_cost: = ' + allTotalCost);
        if (allTotalCost >= "100") 
            gs.eventQueue('incident.commented');
        
    }

})(current, previous);

Can some one help me if there is any script error ?

and how to trigger a flow based on the event

I see some articles for the trigger flow from BR

sn_fd.FlowAPI.executeFlow('global.My_flow_internal_name, inputs);

What is the input we need to provide?

B_35 · ‎06-01-2020

Sorry about that. I guess that parseFloat() doesn't work for this, but Number() will. Number() does the same thing, just converts whatever is inside of the parentheses to a number. Here's that corrected script:

(function executeRule(current, previous /*null when async*/ ) {
    // Go check all u_environment records to see if the u_total_cost is above 100
        // Variable to keep running count
            var runningCount = 0;
        // Query and loop
            var G = new GlideRecord('u_environment');
                G.query();
            while( G.next() ){
                // Add the u_total_cost to runningCount
                    runningCount += Number( G.u_total_cost.toString() ); 
            }
        // Check if the total is over 100
            if( runningCount >= 100 ){
                // Do whatever you want based on meeting this conditions
                    // Print it out, if you want
                        gs.print( "The total cost is over 100" );
                    // Trigger an event, if you want
                        gs.eventQueue('incident.commented');
            }
})(current, previous);

View solution in original post

B_35 · ‎06-01-2020

(1) What table is the business rule on?

(2) How is that table from #1 related the u_environment?

RudhraKAM · ‎06-01-2020

its on the same table

B_35 · ‎06-01-2020

So, when a record is edited or inserted, if the value of u_actual_value (your question says "u_actual_value", but your code says "u_actual_count", if that changes anything) is greater than 100, you want to execute some code?

Or is it that any time a record is inserted or edited on that u_environment table, you want to check all records on that u_environment table to see if the u_actual_cost values all add up to 100 or more, and execute some code based off of that?

#1 for individual record, or #2 for checking the whole table

RudhraKAM · ‎06-01-2020

I am looking for 2nd option