Database View report – Handling Short Description conditions across multiple sc_task records

[dd31]

Hi team,

Report on RITM where one task (Short Description starts with X) is Closed Complete and another task (Short Description starts with Y) is not Closed Complete

I have a requirement to create a report where:

• A RITM has one Catalog Task whose Short Description STARTS WITH "Core" and is Closed Complete

• And another Catalog Task whose Short Description STARTS WITH "Approval" and is NOT Closed Complete

Both tasks belong to the same RITM.

So far, I have created a Database View and joined:

• sc_req_item 

• sc_task

However, I’m not sure how to correctly handle the Short Description condition.

[Ankur Bawiskar]

@dd31 

you can configure the filter condition once the database view runs using Try it

what you tried?

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[dd31]

@Ankur Bawiskar 

I did try applying the filter conditions using “Try It” on the Database View. However, since the view joins sc_req_item with sc_task only once, the filter evaluates per task row.

The requirement needs two different task records under the same RITM to satisfy different conditions, which cannot be achieved with a single join.

[Ankur Bawiskar · Accepted solution]

@dd31 

1) create classless script include like this

function getRecords() {
    var ritmSysIds = []; // Array to store matching RITM sys_ids
    var ritm = new GlideRecord('sc_req_item');
    ritm.query(); // Add filters: ritm.addQuery('active', true);
    while (ritm.next()) {
        var hasCoreClosed = false;
        var hasApprovalOpen = false;

        var task = new GlideRecord('sc_task');
        task.addQuery('request_item', ritm.sys_id);
        task.query();

        while (task.next()) {
            var desc = task.short_description.toString();
            if (desc.startsWith('Core') && task.state == 7) {
                hasCoreClosed = true;
            }
            if (desc.startsWith('Approval') && task.state != 7) {
                hasApprovalOpen = true;
            }
        }

        if (hasCoreClosed && hasApprovalOpen) {
            ritmSysIds.push(ritm.sys_id.toString());
        }
    }

    return ritmSysIds.toString();
}

2) then call like this Sys ID [IS ONE OF] javascript: getRecords();

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏