Join Two Tables

[SoudarapuN]

Hi team,

I have a requirement where I need to display the number of records in one table that are associated with each record in a referenced table.

For example, Table A is a reference field in Table B. I want to show, for each record in Table A, how many related records exist in Table B.

Specifically, if Table A is the User table and Table B is the Incident table, I need to display how many incidents have been created by each user. The output should be in a list format showing each user along with the count of incidents associated with them.
it is not possible using reports aggregation.it is an urgent requirement.Please help if anyone knows
Thank you

[SuyashJ41731830]

Hi
@SoudarapuN 
Background script

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT');
ga.groupBy('caller_id'); // Assuming caller_id references sys_user
ga.query();

while (ga.next()) {
var userName = '';
var userGR = new GlideRecord('sys_user');
if (userGR.get(ga.getValue('caller_id'))) {
userName = userGR.name + ' (' + userGR.user_name + ')';
} else {
userName = 'Unknown User';
}

gs.print(userName + ' has ' + ga.getAggregate('COUNT') + ' incidents');
}

Regards,
Suyash

[SuyashJ41731830]

Hi
@SoudarapuN 
Background script

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT');
ga.groupBy('caller_id'); // Assuming caller_id references sys_user
ga.query();

while (ga.next()) {
var userName = '';
var userGR = new GlideRecord('sys_user');
if (userGR.get(ga.getValue('caller_id'))) {
userName = userGR.name + ' (' + userGR.user_name + ')';
} else {
userName = 'Unknown User';
}

gs.print(userName + ' has ' + ga.getAggregate('COUNT') + ' incidents');
}

Regards,
Suyash

[SoudarapuN]

thanks for your effort @SuyashJ41731830 
how can i show them as list in dashboard