Portal widget to list closed + resolved tickets

[Henri Muldre]

Hi,

I have a portal widget which lists tickets (tables incident, 2 custom app tables and task table with sys_class_name query) with defined query (with buttons "Show open", "Show closed" and "All issues"). When user clicks on any of the buttons, the below queries are added.

1. "Show open" button will list all active=true tickets except stateNOT IN6,20 (not in state "Resolved"). This is working as expected now, as I filtered out resolved tickets.
   

   var taskGR = new GlideRecord('task');
   taskGR.getEncodedQuery() + '^active=true^stateNOT IN6,20', //getEncodedQuery() has previous queries such as sys_class_name etc

2. "Show closed" button previously had query active=false, but Resolved tickets in ServiceNow are considered active=true and customer wanted to also show closed + resolved tickets. I tried building query for this:
   

   var taskGR = new GlideRecord('task');
   taskGR.getEncodedQuery() + '^stateIN6,3,7,4,8,106,107,20',

   The issue here is that state value "3" is in conflict, as in ServiceNow, incident table state value 3 resepresents "On Hold".
   Then I tried the following query:

   var taskGR = new GlideRecord('task');
   taskGR.getEncodedQuery() + '^stateIN6,3,7,4,8,106,107,20^ref_incident.incident_state!=3',

   The issue with this is that when using ref_incident.incident_state!=3, it only shows incidents.

So in conclusion:

1) I cannot use active=false as then only Closed/Cancelled tickets are shown and not Resolved tickets
2) I cannot use query stateIN6,3,7,4,8,106,107,20 because then it will show also incidents with "On Hold"
3) I cannot use query stateIN6,3,7,4,8,106,107,20^ref_incident.incident_state!=3 because then it shows only incidents and not tickets from other tables

Any suggestions? Thank you in advance.

[TrevorK · Accepted solution]

What I would do is an encoded query with a giant OR statement like this:

sys_class_name!=incident^stateIN-5,1^NQsys_class_name=incident^ref_incident.incident_state!=100^stateIN-5,1

Basically, you can use the Task Type attribute to split apart Incidents from the rest of the tasks. One half is "if not an incident AND all my conditions" while the other half is "if an incident AND all my conditions".

We end up having to do the same sort of thing all the time with Incident, the whole Incident State value really causes us these query problems. But if I am understanding your problem correctly (where you are querying on the whole Task table but need a different query for incidents vs non incidents), the above should help get you started.

[TrevorK]

Sorry, for some reason there was an error on upload of the image in my original post. Here it is.

[Henri Muldre]

Thanks!

I ultimately used the following query:

var taskGR = new GlideRecord('task');
taskGR.getEncodedQuery() + '^active=false^NQsys_class_name=incident^stateIN6,7,8^' + var1 + '^NQsys_class_name=sc_req_item^stateIN3,4,6,7^opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORref_sc_req_item.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^NQsys_class_name=sn_hr_core_case^stateIN3,4,7,20^opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORref_sn_hr_core_case.opened_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^NQsys_class_name=CUSTOM_TABLE^stateIN4,6,7^' + var2 + '^NQsys_class_name=CUSTOM_TABLE^stateIN3,6,7^' + var3;

The reason why I used this was because I had some conditions which added different queries before as well, hence the getEncodedQuery() there. The var1, var2 and var3 are just queries from the top of the script.

So by using ^NQ in the query, I was able to narrow records down based on the task type.