Can a GlideAggregate query use an or condition

[Robert Hames]

I have a GlideAggregate that gives me a count of the number of projects for a customer (custom tables).

I need to limit the project count to projects of a certain status. I know how to add an or condition to a GlideRecord, but can that same or condition be added to a GlideAggregate? I have example code below, but when I add the or condition to the GlideAggregate, the script blows up.

Code for GlideAggregate:

var gaCustomerProjects = new GlideAggregate(TSSConstants.TABLE_PROJECT);
gaCustomerProjects.addQuery('u_customer', customerSysId);
gaCustomerProjects.groupBy(customerSysId);
gaCustomerProjects.addAggregate('COUNT', customerSysId);
gaCustomerProjects.query();

Code for GlideRecord:

var grProjects = new GlideRecord(TSSConstants.TABLE_PROJECT);
var grProjectsOr = grProjects.addQuery('u_status', gs.getProperty('x_g_irmb_tss.Started Project Status'));
grProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.Received Project Status'));
grProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.In-Queue Project Status'));
grProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.In-Progress Project Status'));
grProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.In-Progress Suspended Project Status'));
grProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.Suspended Project Status'));
grProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.Completed Project Status'));
grProjects.addQuery('u_customer', customerSysId);
grProjects.orderBy('u_project_number');
grProjects.query();

[Jon Barnes]

Yes adding OR conditions works the same in GlideAggregate as it does in GlideRecord. Looking at your script, I think the issue is more likely these 2 lines:

gaCustomerProjects.groupBy(customerSysId);
gaCustomerProjects.addAggregate('COUNT', customerSysId);

Is customerSysId a valid table column on that table? I can't tell because I can't see where you initialized the customerSysId variable, but my guess is that it holds a sys_id. in that case, your addAggregate and groupBy functions will not work as those parameters need to be a valid field name on that table.

[Robert Hames]

I should have mentioned that both sets of example code are working just fine. The "customerSysId" is the sysId of the Customer table.

var customerSysId = grCustomers.getUniqueValue();

What I was trying to do is add the or conditions from the working GlideRecord code to my GlideAggregate code. 

When I do, it quits working.

[Subrahmanyam2]

Hi Robert,

As mentioned by Jon, addAggregate and groupBy methods expect field name as their parameter not the customer sys_id.

You may modify the code as below:

var gaProjects = new GlideAggregate(TSSConstants.TABLE_PROJECT);
var gaProjectsOr = gaProjects.addQuery('u_status', gs.getProperty('x_g_irmb_tss.Started Project Status'));
gaProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.Received Project Status'));
gaProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.In-Queue Project Status'));
gaProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.In-Progress Project Status'));
gaProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.In-Progress Suspended Project Status'));
gaProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.Suspended Project Status'));
gaProjectsOr.addOrCondition('u_status', gs.getProperty('x_g_irmb_tss.Completed Project Status'));
gaProjects.addQuery('u_customer', customerSysId);
gaProjects.addAggregate('COUNT', 'u_customer');
gaProjects.query();
if (gaProjects.next()) {
    var numberOfProjects = gaProjects.getAggregate('COUNT', 'u_customer');
}

There is no need for groupBy method in your case, hence removed that line.
Please check if this works.

Alternatively using IN query you can write it as below:

var gaProjects = new GlideAggregate(TSSConstants.TABLE_PROJECT);
gaProjects.addQuery('u_customer', customerSysId);
gaProjects.addQuery('u_status', 'IN', [gs.getProperty('x_g_irmb_tss.Started Project Status'),
    gs.getProperty('x_g_irmb_tss.Received Project Status'),
    gs.getProperty('x_g_irmb_tss.In-Queue Project Status'),
    gs.getProperty('x_g_irmb_tss.In-Progress Project Status'),
    gs.getProperty('x_g_irmb_tss.In-Progress Suspended Project Status'),
    gs.getProperty('x_g_irmb_tss.Suspended Project Status'),
    gs.getProperty('x_g_irmb_tss.Completed Project Status')
]);
gaProjects.addAggregate('COUNT', 'u_customer');
gaProjects.query();
if (gaProjects.next()) {
    var numberOfProjects = gaProjects.getAggregate('COUNT', 'u_customer');
}

Please mark Jon Barnes reply as solution and my reply as helpful if this works!

Thanks and regards,

Subrahmanyam Satti