Database view of catalog items where related SCTASK there's no SLA attached

Carol2
Tera Contributor

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 

 

Carol2_0-1734442214128.png

 

 

Carol2_2-1734442279196.png

 

 

Carol2_1-1734442505984.png

 

 

 

24 REPLIES 24

@Carol2 

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.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@Ankur Bawiskar 

still, no records displayed 

Carol2_0-1734504644952.pngCarol2_1-1734504657992.png

 

@Carol2 

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.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@Ankur Bawiskar 

yes that's what I want. 

I tried this, item.sys_id!=sla.task still nothing no records pull through 

@Carol2 

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();
}

AnkurBawiskar_0-1734513099217.png

Report:

AnkurBawiskar_1-1734513197373.png

 

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader