Problems with GlideAggregate and GROUP_CONCAT_DISTINCT on reference fields

[Boris Livertovs]

Hello all,

i have a problem that the GROUP_CONCAT_DISTINCT setting for .getAggregate on a reference field to the user or group table doesn't return a SysId but the name instead.

i have the requirement to get all users and groups which are referenced in a table based on a certain criteria and put together the following query:

    //result has the following format:
    // result = {
    //  "userIDs" : array of user ids,
    //  "groupIDs" : array of group ids,
    // }
    var result = {};

    var recordsInTable= new GlideAggregate(table);
        recordsInTable.addQuery('criteria_field', criteria);
        recordsInTable.addAggregate('GROUP_CONCAT_DISTINCT', 'u_user'); // u_user is a reference field to user table
        recordsInTable.addAggregate('GROUP_CONCAT_DISTINCT', 'group'); // group is a reference field to group table
        recordsInTable.groupBy('criteria_field');
    recordsInTable.query();
   
    if(recordsInTable.next()){
        result.userIDs = recordsInTable
                    .getAggregate('GROUP_CONCAT_DISTINCT', 'u_user')
                    .split(",");
        result.groupIDs = recordsInTable
                    .getAggregate('GROUP_CONCAT_DISTINCT', 'group')
                    .split(",");
    }
   
    return result;

Based on the documentation ".getAggregate('GROUP_CONCAT_DISTINCT', 'u_user')" should return a comma-separated string with distinct values (in this case SysIds) but I get the full name of the user instead. Same for groups.

Does anyone know how to get the SysIds?

[Boris Livertovs]

As an addendum with user and group table I mean the [sys_user] and [sys_user_group] table respectively

[Boris Livertovs]

On a bit of further testing I saw that ".getAggregate('GROUP_CONCAT_DISTINCT', 'reference_field')" on any reference field always returns the display value except the SysId

[Mohammed_Lais · Accepted solution]

try using reference_field.sys_id in addAggregate and getAggregate functions and it will give the sys_id.