This looks like a regular sql query, would you be able to do a GlideRecord query instead?
Discussion
Yes, I can definitely help with a GlideRecord query! Here's an example query that should achieve the same result as the SQL query I provided earlier:
```
var gr = new GlideAggregate('sc_req_item');
gr.addAggregate('SUM', 'total_price');
gr.addQuery('stage', 'requested');
gr.addQuery('request', 'IN', 'sc_task.request');
gr.addQuery('sc_task.assignment_group', 'your_group_name_here');
gr.groupBy('request');
gr.query();
if (gr.next()) {
var totalPrice = gr.getAggregate('SUM', 'total_price');
gs.print('Total price: ' + totalPrice);
}
```
This query uses GlideAggregate to perform an aggregate function (SUM) on the total_price field of sc_req_item records that meet the specified conditions. It also uses GlideRecord to join the sc_req_item and sc_task tables together based on the request field. Let me know if you have any questions or if this helps!
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?
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!