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-17-2024 10:28 PM
seems you accidentally removed the where clause for sc_task table
it should be
item.sys_id=sla.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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-17-2024 10:51 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 12:51 AM
you want catalog items where catalog task doesn't have SLA?
try this
item.sys_id!=sla.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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 01:03 AM
yes that's what I want.
I tried this, item.sys_id!=sla.task still nothing no records pull through
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 01:13 AM
you can use report on sc_cat_item table and use script include function
Something like this in report filter condition
Sys ID {IS ONE OF} javascript: getRecords()
Script Include: It should be client callable
function getRecords() {
var catItemArr = [];
// Initialize GlideRecord for Catalog Task (sc_task)
var taskGR = new GlideRecord('sc_task');
taskGR.query();
while (taskGR.next()) {
// Initialize GlideRecord for Task SLA (task_sla)
var slaGR = new GlideRecord('task_sla');
slaGR.addQuery('task', taskGR.sys_id);
slaGR.query();
// Check if there are no SLA records for the task
if (!slaGR.hasNext()) {
catItemArr.push(taskGR.request_item.cat_item.sys_id.toString());
}
}
return catItemArr.toString();
}
Report:
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