Hello experts,
I am seeking a help to achieve the below requirement:
We have a custom table as "u_cisco_management", if there are multiple records in that table with same Reference numbers.
From those records, initially created record that has checkbox field "Received" is FALSE and another string field called "Amend To" is EMPTY.
Example: Assume below are the records with same reference numbers:
CIS1009 || RefXXX10 || Recieved is TRUE || Ament To is CIS1001 || Created on Jan 9th 2024
CIS1001 || RefXXX10 || Recieved is FALSE || Ament To is EMPTY|| Created on Jan 1st 2024
CIS1004 || RefXXX10 || Recieved is TRUE || Ament To is CIS1001 || Created on Jan 3rd 2024
CIS1008 || RefXXX10 || Recieved is TRUE || Ament To is CIS1001 || Created on Jan 6th 2024
Highlighted record should be sent in scheduled report in excel format.
I've created a script include as below to
var RecievedFALSERecords = Class.create();
RecievedFALSERecords.prototype = Object.extendsObject(AbstractAjaxProcessor, {
Recieved: function() {
var arr =[];
var agg = new GlideAggregate('u_cisco_management');
agg.addEncodedQuery('u_received=false^u_amend_toISEMPTY^sys_created_onONLast quarter@javascript:gs.beginningOfLastQuarter()@javascript:gs.endOfLastQuarter()^u_reference_numberISNOTEMPTY');
agg.addAggregate('COUNT');
agg.groupBy('u_reference_number');
agg.query();
//gs.print("group by ref field count : "+agg.getRowCount());
while (agg.next()) {
// gs.print("group by ref field: "+agg.u_reference_number);
//var category = agg.u_reference_number;
var gr = new GlideRecord('u_cisco_management');
gr.addQuery('u_reference_number', agg.u_reference_number);
gr.orderBy('sys_created_on');
//gr.setLimit(1);
gr.query();
//gs.print("Each group by ref field count : "+gr.getRowCount());
if(gr.next() && gr.getRowCount()>1 )
{
//gs.print("rec numbers : "+gr.u_number);
arr.push(gr.u_number + ',');
}
return arr.join(',');
}
type: 'RecievedFALSERecords'
}
});
