Indicator with Count Distinct Script

[hectorbg95]

Hello,

The following script checks a reservations table to see if there are users who have never had a reservation with access_building=true and it works correctly

// Object to store users who have at least one access
var usersWithAccess = {};

// First, get all users with access = true
var grAccess = new GlideRecord('x_fru_cir_consolidated_reservation');
grAccess.addEncodedQuery('access_building=true');
grAccess.query();
while (grAccess.next()) {
    usersWithAccess[grAccess.getValue('u_employee')] = true;
}

// Now get all distinct users
var grAll = new GlideRecord('x_fru_cir_consolidated_reservation');
grAll.query();

var usersWithoutAccess = {};
while (grAll.next()) {
    var emp = grAll.getValue('u_employee');
    // Add only if the user does NOT have access (unique)
    if (!usersWithAccess[emp]) {
        usersWithoutAccess[emp] = true;
    }
}

// Get the list of users without access
var result = Object.keys(usersWithoutAccess);
return result;

I am trying to create an automated indicator where I have set Aggregate = Count Distinct, Scripted = true, and Script = "the script mentioned above".

The problem is that the indicator is collecting all records from the table and is not taking the script into account.

[Arun_Manoj]

Hi @hectorbg95 ,

Use a Scripted Indicator with answer

You must:

• Evaluate one record at a time

• Set answer = 1 only when the record should be counted

• Use Count (not Count Distinct)

• Handle distinct users manually

Example Scripted Indicator (Correct Pattern)

Indicator settings

• Aggregate: Sum

// This script runs once per record
// 'current' is available

var emp = current.getValue('u_employee');

// Check if this employee has ANY access_building = true record
var gr = new GlideRecord('x_fru_cir_consolidated_reservation');
gr.addQuery('u_employee', emp);
gr.addQuery('access_building', true);
gr.setLimit(1);
gr.query();

if (!gr.hasNext()) {
// Employee has NEVER had access_building = true
answer = 1;
} else {
answer = 0;
}

This works because:

• Each record contributes either 1 or 0

• PA sums the result

• Only employees with no access history are counted

[hectorbg95]

Thank you for your response.

However, after testing it, it still does not work. Upon reviewing the logs, it appears that current cannot be used in this context.