Scheduled script with GlideAggregate

[Martyna]

Hello All,

I need to modify this scheduled script notification - according to best practice it should contain GlideAggregate instead of getRowCount (line 5). The script should send notification to Portfolio Manager for inactive project managers. The script with getRowCount is working fine but after modification it has stopped and I am not sure what I did wrong.

The script with getRowCount on line 5 is:

var query = "project_manager.active=false^stateNOT IN3,4,7^primary_portfolio.portfolio_managerISNOTEMPTY^project_managerISNOTEMPTY";

var grProject = new GlideRecord('pm_project'); //Pass table name

grProject.addEncodedQuery(query);

grProject.query();

gs.print(grProject.getRowCount());

var projectManagerList = [];

var portfolioManagerList = [];

while (grProject.next()) {

    projectManagerList.push(grProject.project_manager.trim());

    portfolioManagerList.push(grProject.primary_portfolio.portfolio_manager.trim());

    gs.print(grProject.project_manager + '');

    gs.print(grProject.primary_portfolio.portfolio_manager + '');

}

var arrayUtil = new ArrayUtil();

projectManagerList = arrayUtil.unique(projectManagerList);

portfolioManagerList = arrayUtil.unique(portfolioManagerList);

gs.print(projectManagerList.length);

gs.print(portfolioManagerList.length);

for (var i = 0; i < projectManagerList.length; i++) {

    gs.print(projectManagerList[i]);

    if (!projectManagerList[i].equals("")) {

        gs.eventQueue('z_inactive_project_manager',grProject, portfolioManagerList[i]);

    }

}

[Rene El Hamzh]

Hi @Martyna, 

it would be helpful if you could show us your modified script with GlideAggregate.

Best regards,

Rene

[Martyna]

Sure, I have modified the first part of the script:

var query = "project_manager.active=false^stateNOT IN3,4,7^primary_portfolio.portfolio_managerISNOTEMPTY^project_managerISNOTEMPTY";
var grProject = new GlideAggregate('pm_project'); //Pass table name
grProject.addAggregate('COUNT');
grProject.addEncodedQuery(query);
grProject.query();
gs.print(grProject.getAggregate('COUNT'));

[SunilKumar_P]

Hi @Martyna, can you try the below script?

var gaProject = new GlideAggregate('pm_project');
gaProject.addEncodedQuery(query);
gaProject.addAggregate('COUNT');
gaProject.query();

var rowCount = 0;
if (gaProject.next()) {
    rowCount = gaProject.getAggregate('COUNT');
}
var grProject = new GlideRecord('pm_project');
grProject.addEncodedQuery(query);
grProject.query();

while (grProject.next()) {
    projectManagerList.push(grProject.project_manager.trim());
    portfolioManagerList.push(grProject.primary_portfolio.portfolio_manager.trim());
    gs.print(grProject.project_manager + '');
    gs.print(grProject.primary_portfolio.portfolio_manager + '');
}

Regards,

Sunil

[Martyna]

Hi @SunilKumar_P,

Thank you for your response but your script is not working 😞