GlideAggregate and grouping records

[raed1]

Hey guys,

I need some advice on how I should go about this.

The Problem: These are the two tables I currently have ( I have left out the fields that are not important):

Case Table

HR Number	State
HRC0001000	Processed
HRC0001000	Cancelled
HRC0001001	Processed
HRC0001001	Processed
HRC0001001	Processed
HRC0001002	Cancelled
HRC0001002	Cancelled
HRC0001002	Cancelled
HRC0001003	Cancelled
HRC0001003	Cancelled
HRC0001004	Processed
HRC0001005	Processed
HRC0001005	Processed

hr_case Table: Case has to be unique

Case	Status
HRC00010000	Closed
HRC00010001	Open
HRC00010002	Resolved
HRC00010003	Resolved
HRC00010004	Resolved
HRC00010005	Resolved

Case Table is the table I get after a transform map, and I want to use that table to update another table called hr_case. In the Case Table, if a particular HR Number has State set as 'Processed' for all the records associated to it, I want that HR Case Number Status to be set to Closed on the hr_case Table. So for e.g. Since all records with HR Number HRC0001001 have state as 'Processed' on Case Table, I want the status of Case HRC0001001 on hr_case table to be set to Closed. (Similarly with HRC0001004 and HRC0001005).

I currently have an onComplete script that runs well, however it also counts HRC0001000 in my code logic, as one of the records associated to it has state Processed.

This is my onComplete script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
   // Add your code here
   var ot = new GlideAggregate('case_table');
   ot.addQuery('active',true);
   ot.groupBy('u_state');
   ot.groupBy('case_number');
   ot.addQuery('u_state',3);
   ot.query();
   while(ot.next()) {
             log.info('Case: ' + ot.case_number + ' and state: ' + ot.u_state);
                       var gr = new GlideRecord('hr_case');
                       gr.addQuery('number',ot.case_number);
                       gr.query();
                       while (gr.next()) {
                                 log.info('In!');
                                 gr.setValue('status',3);
                                 gr.update();
             }
   } })(source, map, log, target);
})(source, ma
})p, log, target);

Note: The numerical value of State 'Processed' is 3. The numerical value of Status 'Closed' is 3.

Could someone help me out with how this could be done using GlideAggregate?

Thanks,

Raed

[Shishir Srivast]

Hi Raed,

I am not very much sure with GlideAggregate, but can we try with below script,

My Assumptions,

        - you have to update the hr_case table when all the record of the same HR Number in case_table have state as Processed, if not then don't update.

        - There are only two state in case table (cancelled and Processed)

var bStatus;
var ot = new GlideRecord('case_table');   
ot.addQuery('active',true);   
ot.groupBy('case_number', source.u_hr_number);   
ot.query();   
while(ot.next()) {
if(ot.state == 'Cancelled'){
bStatus = false;
break;
}
else if(ot.state == 'Processed')
bStatus = true;
log.info('Case: ' + ot.case_number + ' and state: ' + ot.u_state);
}   


if(bStatus == true){
var gr = new GlideRecord('hr_case');   
gr.addQuery('number',ot.case_number);   
gr.query();   
while (gr.next()) {   
log.info('In!');   
gr.setValue('status',3);   
gr.update(); 
}