Database view of catalog items where related SCTASK there's no SLA attached
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-17-2024 05:35 AM
Hi All
I created a database view using the task_sla and sc_task table. Both tables have the fields that I want to report on. I am stuck on joining the tables together to get the information I am looking for. Where the related SCTASK must give me the name of the catalog item and report on whether the task SLA field is populated or not. Please help i am new to creating database views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 06:32 AM
did you add logs and see what came in that while loop?
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 06:44 AM
yes i did, and still nothing
function getRecords() { var catItemArr = []; var taskGR = new GlideRecord('sc_task'); taskGR.query(); while (taskGR.next()) { var slaGR = new GlideRecord('task_sla'); slaGR.addQuery('task', taskGR.sys_id); slaGR.query(); if (!slaGR.hasNext()) { var catItemSysId = taskGR.request_item.cat_item.sys_id.toString(); catItemArr.push(catItemSysId); gs.log('Catalog Item sys_id: ' + catItemSysId); } else { gs.log('SLA record found for task: ' + taskGR.sys_id); } } return catItemArr.toString(); }
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 07:17 AM
try running in background script
use gs.info() instead of gs.log()
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 07:41 AM
Still nothing gs.info() doesn't give me any records
so, I created a database view eventually, please check where clause option. This option gives me all the records including incidents and the sla is attached. How do I filter this further to give me records which are catalog task with no SLA attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 07:46 AM
add this in where clause
sla.task != item.sys_id && sla.task.sys_class_name == 'sc_task'
If my response helped please mark it correct and close the thread so that it benefits future readers.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader