Get active user count from location

[shaik23]

Hello all,

i have below requirement,

Add a field to use in reporting, list, and form views that holds the current number of users in the current location and child locations.

Table: cmn_location

As i understand we need to get the active users from location from parent and child location, lets say india is a parent location and has child location like Hyderabad, banglore...etc, Baglore has childs like (ex: electronic city,HSR layout etc..) so i want total active user when i opened childs in childs location and overal count in parent location an example screens hot added below for reference.

Parent:

California is a parent in related list u can child location the totao active user are 174 from 14 location is show in Active user count

child:

Home Office - CA is child of California 

Home Office - CA has 71 active users of 122 

i hope i will get help.

[Bhuvan]

@shaik23 

You do not need to have custom field to get this information for reports and you can get this out of box when you build data visualization or report using 'count' feature. 

If this is a functional requirement, maintain a relationship between parent and child locations and do a user record count using parent location that should give the required information.

Thanks,

Bhuvan

[shaik23]

@Bhuvan   Thanks for reply 

I need to ask this with client why the custom field is required how ever as apart of my learning it would help me.
Below code i have tried but it gaves only users count in child locations but not parent

(function executeRule(current, previous /* null when async */) {

/**
* Get all child locations recursively for a given location sys_id
*/
function getLocationHierarchy(locationSysId, collectedIds) {
collectedIds.push(locationSysId);

var locGR = new GlideRecord('cmn_location');
locGR.addQuery('parent', locationSysId);
locGR.query();
while (locGR.next()) {
getLocationHierarchy(locGR.sys_id.toString(), collectedIds);
}
}

/**
* Recalculate active user count for a location (including child locations)
*/
function updateLocationCount(locationSysId) {
if (!locationSysId) return;

var allLocationIds = [];
getLocationHierarchy(locationSysId, allLocationIds);

var userGR = new GlideAggregate('sys_user');
userGR.addAggregate('COUNT');
userGR.addQuery('active', true);
userGR.addQuery('location', 'IN', allLocationIds.join(','));
userGR.query();

var count = 0;
if (userGR.next()) {
count = parseInt(userGR.getAggregate('COUNT'), 10);
}

var locRecord = new GlideRecord('cmn_location');
if (locRecord.get(locationSysId)) {
locRecord.u_active_users_count = count;
locRecord.update();
}
}

// --- Main Execution ---
var affectedLocations = [];

// 1. If location changed, update both old and new
if (previous.location != current.location) {
if (previous.location) affectedLocations.push(previous.location.toString());
if (current.location) affectedLocations.push(current.location.toString());
}
// 2. If only active flag changed, just update current location
else if (previous.active != current.active && current.location) {
affectedLocations.push(current.location.toString());
}
// 3. On delete, update old location
else if (current.operation() === 'delete' && previous.location) {
affectedLocations.push(previous.location.toString());
}

// Remove duplicates
affectedLocations = Array.from(new Set(affectedLocations));

// Update all affected locations
affectedLocations.forEach(function (locId) {
updateLocationCount(locId);
});

})(current, previous);

[Dr Atul G- LNG]

Hi @shaik23 

I’m not 100% sure, but you could try something like this:

• Create a list-type report.

• Add the parent field in the columns.

• Then add the related list like this.