That sounds like a good way to balance getting some much-needed rest and staying productive during your workday. Do you have any favorite music or artists that you like to listen to while you eat?
Discussion
I do! I like Coldplay or some old latin pop or a pod cast with doctor Gabor Mate
That's a great variety of music and podcasts! Coldplay is always a good choice for some uplifting tunes, and it's great that you're also interested in podcasts that can help you learn and grow. Have you learned anything interesting from Dr. Gabor Mate's podcast?
Yes I have! How to have patience and really understand where my emotions come from
That's really interesting! It's great that you're taking the time to learn more about yourself and your emotions. Do you have any other favorite podcasts or resources that you like to use to help you learn and grow?
Right now I am also listening to some scripting videos and podcast to help me better program
That's great to hear! It's always good to keep learning and improving your skills, especially in a field like programming where there's always something new to learn. Do you have any specific goals or projects that you're working on right now?
I’m currently working on a query for ServiceNow. Trying to figure out how to get the total price sum of Requested Items that are currently open based on the catalog task assignment. For example, if the catalog task table has 500 tasks assigned to a group, I need to know the total price of the associated RITMs in one single score. A bit challenging
That sounds like a complex query, but it's great that you're taking on the challenge! Have you tried breaking down the problem into smaller steps or working with a colleague to brainstorm possible solutions? Sometimes it can be helpful to bounce ideas off of someone else when you're stuck on a difficult problem.
I have but we’re not able to get the correct sum in a single score format with the given conditions. Maybe you can help me out
Sure, I'd be happy to help! Can you give me some more information about the structure of the ServiceNow tables and the conditions you're working with? It might be helpful to see some sample data or a screenshot of the tables you're working with.
The tables are just the sc_task and the sc_req_item table. The conditions are that there have to be sc_task records associated with an sc_req_item record. Then, it should return the total sum of all the sc_req_item records total price field
Thanks for the additional information! Based on what you've described, it sounds like you might need to use a join statement to link the sc_task and sc_req_item tables together. Here's an example query that might work:
```
SELECT SUM(sc_req_item.total_price)
FROM sc_req_item
JOIN sc_task ON sc_req_item.request = sc_task.request
WHERE sc_task.assignment_group = 'your_group_name_here'
AND sc_req_item.stage = 'requested'
```
This query should return the total sum of all sc_req_item records that are associated with an sc_task record assigned to your group, and that are in the 'requested' stage. Let me know if this helps or if you have any other questions!
This looks like a regular sql query, would you be able to do a GlideRecord query instead?
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!