Using ^JOIN in encoded query

[Mathias Johans1]

Hi!

I have recently bumped in to an unusual encoded query that I need to adjust.

The goal is to query incidents where there are breached Task SLA's, where Task SLA.stage is not cancelled.

In the script include "SOWUpcomingLinkProvider_Incidents", I can find the syntax "^JOINincident.sys_id=task_sla.task!has_breached=false". This is then run on a GlideRecord Query on incident table. I suspect that I need to add the "stage!=cancelled" query, but it seems that this syntax does not work like a normal query.

i have tried "^JOINincident.sys_id=task_sla.task!has_breached=false^stage!=cancelled", but this seems to be wrong.

Can someone help me clarify this?

[Sebastian L]

Hm, that is weird, 

Just tried in my dev to do the query on two incidents, one that has a cancelled SLA and one that does not: 

And then I run the following script: 

var incident = new GlideRecord('incident');
incident.addQuery('number', 'INC0010112').addOrCondition('number', 'INC0000050');
incident.addEncodedQuery('JOINincident.sys_id=task_sla.task!stage!=cancelled');
incident.query();
while (incident.next()) {
	gs.info(incident.number);
}
//Output: 
//*** Script: INC0000050

If I run it without the !stage!=cancelled: 

var incident = new GlideRecord('incident');
incident.addQuery('number', 'INC0010112').addOrCondition('number', 'INC0000050');
incident.addEncodedQuery('JOINincident.sys_id=task_sla.task!');
incident.query();
while (incident.next()) {
	gs.info(incident.number);
}
/*
Output: 
*** Script: INC0000050
*** Script: INC0010112
*/

[Mathias Johans1 · Accepted solution]

Thanks for your help!

I was looking some more at the code and realized that I should add the full ^JOIN syntax once more to get the desired outcome; eg: '^JOINincident.sys_id=task_sla.task!has_breached=true' + '^JOINincident.sys_id=task_sla.task!stage!=cancelled';

You did point me in the right direction, so it was very helpful!

[Sebastian L]

Yeah okay, that doesn't sound right you have to add a joinr query twice, but still think it answered your original question regarding the syntax 🙂

[Abhijit4]

I think its better to use addJoinQuery with GlideRecord which provides more flexibility on how you would like to identify records.

Refer here for more details : addJoinQuery 

Please mark as Correct or Helpful based on impact.

[Bert_c1]

Hi,

There is an OOB Database view named 'incident_sla'. in my PDI there are 21 records when I use "Try It". I then right-clicked on the Stage value cancel, selected 'Show matching' to only see those, and there are two.  I tried to replicate that with a script.

var vrec=new GlideRecord('incident_sla');
vrec.addEncodedQuery('taskslatable_stage=cancelled');
vrec.query();
gs.info("Found " + vrec.getRowCount() + " records.");
while (vrec.next()) {
 gs.info("vrec = " + vrec + ".");
}

and got:

*** Script: Found 2 records.
*** Script: vrec = [object GlideRecord].
*** Script: vrec = [object GlideRecord].

But I wasn't able to access specific fields from either table. I don't see in the documentation for database views that what I tried is possible.  However, that database view can be used to define reports on, an maybe that may meet your needs.