How can remove duplicated records from report

[I_aki_Maria]

Hello experts,

I need to know all users who are managers from my Company.

To obtain it, I have created a report using table [sys_user]  with fields:company,department and manager. But the problem is that I obtain duplicated reports (as many as the number of users managed by each manager). How could I remove this records from  my report? How could I obtain this report with only one record per manager?

Thanks in advance!

[Allen Andreas]

Hi,

If it's just to get some sort of count, you can just use "group by" and user. This way they all stack in one line for you.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Roger Poore]

From  https://www.servicenowelite.com/blog/2013/11/22/duplicate-record-scripts 

Script below will give you a report, you'll just have to throw in the deleteRecord() function (https://developer.servicenow.com/app.do#!/api_doc?v=kingston&id=r_ScopedGlideRecordDeleteRecord)

gs.print(getDuplicates('sys_user','user_name'));
function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}

[I_aki_Maria]

But I need to obtain a report to be exported to Excel file...

[Allen Andreas]

Well, worse come to worse, you can always export it to Excel, then use the remove Duplicates feature in Excel (you get to pick the field on which to compare for duplicate).

I know that's the worst case scenario, but would end your issue.

Please mark reply as Helpful/Correct, if applicable. Thanks