Duplicate records at weekly aggregate table

[joshuastanley]

Hi All,

We are having duplicates at weekly aggregates table when the timesheet submitted from the timesheet portal. Have anyone faced similiar situation. Is so how to track this down and find out the script creating the duplicates.

Regards, Joshua

[Ankur Bawiskar]

@joshuastanley 

please share what's the report condition and the screenshots etc

If my response helped please mark it correct and close the thread so that it benefits future readers.

[joshuastanley]

Thanks Ankur, we are getting duplicate values at resource_aggregate_weekly table for unique of user, task,categlry &  week_starts_on. one record updated by system and other record updated by admin.

[joshuastanley]

The query which I use to find the duplicates as below

// Identifying the aggregate duplicates

var dupeCount = 0;
var gaMyTableDuplicate = new GlideAggregate('resource_aggregate_weekly');
gaMyTableDuplicate.addQuery('week_starts_on', '>', '2024-11-01');
gaMyTableDuplicate.addAggregate('COUNT');
gaMyTableDuplicate.groupBy('user');
gaMyTableDuplicate.groupBy('task');
gaMyTableDuplicate.groupBy('category');
gaMyTableDuplicate.groupBy('week_starts_on');
gaMyTableDuplicate.addHaving('COUNT', '>', 1);
gaMyTableDuplicate.query();

while (gaMyTableDuplicate.next()) {
dupeCount = gaMyTableDuplicate.getAggregate('COUNT');
gs.info(
dupeCount + ' records found with the following identical values:\n' +
'•User: ' + (gaMyTableDuplicate.user.getDisplayValue() || '(blank)') + '\n' +
'•Category: ' + (gaMyTableDuplicate.category.getDisplayValue() || '(blank)') + '\n' +
'•Task: ' + (gaMyTableDuplicate.task.getDisplayValue() || '(blank)') + '\n' +
'•Week Starts: ' + (gaMyTableDuplicate.week_starts_on.getDisplayValue() || '(blank)') + '\n\n'
);

}

[joshuastanley]

Is it a good idea to enable the audit on the resource_weekly_aggregate table, which undergoes 20000 times updated over a week time. Please let me know your suggestions.