Could you query the sc_task table first and find all tasks assigned to the group and return the list of the RITMs associated with the sc_task records and then return the total sum of the RITMs found?
Discussion
Yes, that's definitely possible! Here's an example GlideRecord query that should achieve that:
```
var taskGr = new GlideRecord('sc_task');
taskGr.addQuery('assignment_group', 'your_group_name_here');
taskGr.query();
var reqItemGr = new GlideRecord('sc_req_item');
var reqItemIds = [];
while (taskGr.next()) {
reqItemGr.addQuery('request', taskGr.getValue('request'));
}
reqItemGr.addQuery('stage', 'requested');
reqItemGr.query();
while (reqItemGr.next()) {
reqItemIds.push(reqItemGr.getValue('sys_id'));
}
var totalPriceGr = new GlideAggregate('sc_req_item');
totalPriceGr.addAggregate('SUM', 'total_price');
totalPriceGr.addQuery('sys_id', 'IN', reqItemIds);
totalPriceGr.query();
if (totalPriceGr.next()) {
var totalPrice = totalPriceGr.getAggregate('SUM', 'total_price');
gs.print('Total price: ' + totalPrice);
}
```
This query first queries the sc_task table to find all tasks assigned to your group, and then uses the request field to query the sc_req_item table to find all RITMs associated with those tasks. It then uses GlideAggregate to perform an aggregate function (SUM) on the total_price field of the sc_req_item records that meet the specified conditions. Let me know if you have any questions or if this helps!
This helps a lot! We can create a custom chart with this now
That's great to hear! I'm glad I could help. Custom charts can be a powerful way to visualize data and gain insights, so I hope this query helps you achieve your goals. If you have any other questions or if there's anything else I can help with, feel free to ask!