addJoinQuery issue

anilkumarsharma · ‎02-24-2023

Hello Team,

i need to know that how addJoinQuery work and pass the sys_id.

var thisCI='dfgh1111'// this is CI

var grParentCIName = new GlideRecord('cmdb_ci_business_app');
grParentCIName .addEncodedQuery('u_active=true^sys_class_name=cmdb_ci_business_app');
var jq1 = grParentCIName .addJoinQuery('cmdb_rel_ci','sys_id','parent');
jq1.addCondition('child', thisCI);
grParentCI.query();

raphaelcrv · ‎02-26-2023

It looks like there are a couple of issues with the provided code. Here's a version of the code with some corrections:

var grParentCI = new GlideRecord('cmdb_ci_business_app');
grParentCI.addQuery('u_active', true);
grParentCI.addQuery('sys_class_name', 'cmdb_ci_business_app');
var jq1 = grParentCI.addJoinQuery('cmdb_rel_ci', 'sys_id', 'parent');
jq1.addCondition('child', thisCI);
grParentCI.query();

Here are the changes I made:

I removed the extra space between grParentCIName and the . in the first line.
I changed grParentCIName to grParentCI to be consistent with the rest of the code.
I replaced the addEncodedQuery method with addQuery and passed the values as separate arguments. This is a more standard way to add queries to a GlideRecord.
I fixed the typo in the last line where grParentCI was mistakenly referred to as grParentCIName.
These changes should help ensure that the query runs as intended. Note that I assume thisCI is a variable that contains the sys_id of a CI, as it is used in the join condition. If that is not the case, you may need to adjust the code accordingly.

If my response was helpful and/or provided a solution, please consider marking it as such. Thank you!

View solution in original post

Allen Andreas · ‎02-24-2023

Hi,

I'd recommend referring to documentation and other community articles, like this one: https://www.servicenow.com/community/developer-articles/servicenow-introduction-to-addjoinquery-meth... for assistance and reference.

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

Rahul RJ · ‎02-24-2023

@anilkumarsharma You can reference this link

var thisCI='Fidelity 401K'// this is CI
var grParentCIName = new GlideRecord('cmdb_ci_business_app');
grParentCIName .addEncodedQuery('u_active=true^sys_class_name=cmdb_ci_business_app');
 grParentCIName .addJoinQuery('cmdb_rel_ci','sys_id','parent');
grParentCIName.addJoinQuery('cmdb_rel_ci','child',thisCI);
grParentCIName.query();

while(grParentCIName.next()){
    gs.info(grParentCIName.name)
}

You also refer community link

Please mark the answer correct/helpful based on Impact.

Regards,

RJ

anilkumarsharma · ‎02-24-2023

Thanks for update.

How to get the sys_id and from which table? Please guide me.

How does this code work?

Rahul RJ · ‎02-26-2023

@anilkumarsharma

var thisCI='Fidelity 401K'// this is CI
var grParentCIName = new GlideRecord('cmdb_ci_business_app');
grParentCIName .addEncodedQuery('u_active=true^sys_class_name=cmdb_ci_business_app');
 grParentCIName .addJoinQuery('cmdb_rel_ci','sys_id','parent'); // cmdb_rel_ci having parent field which is referring to the cmdb_ci_business_app table based on  thisCI value it will return the result where value is parent on the cmdb_rel_ci table and cmdb_ci_busiess_app table 
grParentCIName.addJoinQuery('cmdb_rel_ci','child',thisCI);
grParentCIName.query();

while(grParentCIName.next()){
    gs.info(grParentCIName.name)
}

Here's an example of how to use addJoinQuery in GlideRecord to retrieve records from two related tables:

var gr = new GlideRecord('incident');
gr.addQuery('priority', '1');
gr.addJoinQuery('incident_task', 'sys_id', 'incident');
gr.query();

while (gr.next()) {
  gs.log(gr.number + ' - ' + gr.incident_task.number);
}

1 .GlideRecord object for the incident table and then add a query to filter the records to only those with a priority of 1. We then call the addJoinQuery method to join the incident_task table to the incident table using the 'sys_id' field on incident_task and the 'incident' field on the incident.

Please mark the answer correct/helpful based on Impact.

Regards,

RJ