How to write a condition comparing a value from the current record to a value in a related record

[Donnie]

I have a requirement to write a report on incidents requiring cross-team action.  In other words, I need to show all incidents where there exists an active incident task whose assignment group is different than the incident's assignment group.

Is there a way to do this through the UI using the condition builder in a report?  I see that I can add a related list condition, but I don't know how to compare the Assignment Group values between the incident record and the incident task record.

If this can't be done through the condition builder via normal means, I'm assuming it would need to be scripted.  Any thoughts on the best way to do this?  I don't need the full solution, just the general method for comparing values across related fields and being able to use that method in the condition builder (e.g. dynamic filter option, script include, etc.).

If I was doing this in a database, the SQL would look something like this:

select
   * 
from 
   incident 
where 
   exists (
      select
         * 
      from 
         incident_task 
      where 
         incident_task.incident = incident.sys_id 
         and incident_task.active = true 
         and incident_task.assignment_group != incident.assignment_group
   )

Thanks!

[Ankit P]

You need a report on Incident Task table like the below one.

After selecting "is Different" in the condition.

In From field, select "Show Related Fields"

Select "Incident--> Incident Fields"

Select "Assignment Group"

Mark correct if that helps.

[Donnie]

Unfortunately, I can't write the report on incident_task.  It has to be on incident.

Any ideas why the right side of the condition in the below related list comes up with "--None--" for options instead of a value?

[Abhinay Erra · Accepted solution]

You will have to create a script include and put that in the filter conditions.

craete a report on incident table and put this in the filter conditions

sys_id Is One of javascript: new GetIncidents().getSysIds();

Create this client callable script include with Name GetIncidents and put the following code in it

var GetIncidents = Class.create();
GetIncidents.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
getSysIds: function(){
var arr=[];
var gr= new GlideRecord("incident_task");
gr.addQuery('parent','!=','');
gr.addQuery('assignment_group','!=','');
gr.query();
while(gr.next()){
if(gr.parent.assignment_group!=gr.assignment_group)
arr.push(gr.getValue('parent'));
}
return arr.join();
},

type: 'GetIncidents'
});

[Donnie]

This is exactly what I needed.  Thank you!