Hi,
I have a before insert business rule that calculates YTD for records in a fiscal year(Apr-march)
what i intent to do is, get the month of the current record and set flag to false for any record of that month. then I will calculate the Risk priority number(RPN ) for the fiscal year and calculate the YTD and store latest YTD in current record and set flag to true.
Now the problem is that the business rule works fine when i use GlideRecord but fails when I try to use GlideAggregate. The query just doesn't seem to execute and I am getting YTD value as 0. Someone please guide me on what am I doing wrong. I have been on it for hours. Also, RPN is also calculated using a before insert BR of low order than this. below are the two codes for comparison
1. Using glode record:
(function executeRule(current, previous) {
var created = new GlideDateTime(current.sys_created_on);
gs.info('Creation time of record' + created);
var month = parseInt(created.getMonthLocalTime(), 10); // 1–12
gs.info('Month of created record' + month);
var year = parseInt(created.getYearLocalTime(), 10);
gs.info('Year of created record' + year);
var now = new GlideDateTime();
var monthStart = new GlideDateTime(year + "-" + month + "-01 00:00:00");
var gr = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');
gr.addQuery('sys_created_on', '>=', monthStart);
gr.addQuery('sys_created_on', '<=', now);
gr.addQuery('is_month_latest', true);
gr.query();
gs.info('Records with true flag' + gr.getRowCount());
while (gr.next()) {
gr.is_month_latest = false;
gr.update();
}
var fiscalYear = (month < 4) ? year - 1 : year;
var fiscalStart = new GlideDateTime(fiscalYear + "-04-01 00:00:00");
gs.info('Fiscal start is' + fiscalStart);
var fiscalMonth = month - 3;
if (fiscalMonth <= 0)
fiscalMonth += 12;
var ga = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');
ga.addQuery('sys_created_on', '>=', fiscalStart.getValue());
ga.query();
gs.info('records found' + ga.getRowCount());
var totalRPN = 0;
while(ga.next()) {
gs.info('if loop entered');
totalRPN += ga.total_rpn;
}
gs.info('total RPN for this fiscal year' + totalRPN);
var ytd = totalRPN / fiscalMonth;
gs.info('YTD VALUE FOR THIS FISCAL YEAR' + ytd);
current.ytd = ytd;
current.is_month_latest = true;
})(current, previous);
2. Using glideaggregate:
(function executeRule(current, previous) {
var created = new GlideDateTime(current.sys_created_on);
gs.info('Creation time of record' + created);
var month = parseInt(created.getMonthLocalTime(), 10); // 1–12
gs.info('Month of created record' + month);
var year = parseInt(created.getYearLocalTime(), 10);
gs.info('Year of created record' + year);
var now = new GlideDateTime();
var monthStart = new GlideDateTime(year + "-" + month + "-01 00:00:00");
var gr = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');
gr.addQuery('sys_created_on', '>=', monthStart);
gr.addQuery('sys_created_on', '<=', now);
gr.addQuery('is_month_latest', true);
gr.query();
gs.info('Records with true flag' + gr.getRowCount());
while (gr.next()) {
gr.is_month_latest = false;
gr.update();
}
var fiscalYear = (month < 4) ? year - 1 : year;
var fiscalStart = new GlideDateTime(fiscalYear + "-04-01 00:00:00");
gs.info('Fiscal start is' + fiscalStart);
var fiscalMonth = month - 3;
if (fiscalMonth <= 0)
fiscalMonth += 12;
// var ga = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');
// ga.addQuery('sys_created_on', '>=', fiscalStart.getValue());
// ga.query();
// gs.info('records found' + ga.getRowCount());
// var totalRPN = 0;
// while(ga.next()) {
// gs.info('if loop entered');
// totalRPN += ga.total_rpn;
// }
// gs.info('total RPN for this fiscal year' + totalRPN);
var ga = new GlideAggregate('x_upl2_zlp_audit_zlp_audit_creation');
ga.addQuery('sys_created_on', '>=', fiscalStart.getValue());
ga.addAggregate('SUM', 'total_rpn');
ga.query();
var totalRPN = 0;
if (ga.next()) {
totalRPN = parseFloat(ga.getAggregate('SUM', 'total_rpn')) || 0;
}
gs.info('total RPN (GA) for this fiscal year: ' + totalRPN);
var ytd = totalRPN / fiscalMonth;
gs.info('YTD VALUE FOR THIS FISCAL YEAR' + ytd);
current.ytd = ytd;
current.is_month_latest = true;
})(current, previous);
