How to get reference field Display value

[Sironi]

Hi All,

I request you , please help me on below issue.

 "cmdb_ci_service_discovered" table record has 'Corporation' field which referencing "u_corporation" table

Request : Corporation record has "Node" reference field which is referencing same table of record itself  .

Here Hierarchy:  CMDB ServiceDiscovered has "corporation"  field which referencing "Test 2" ----> Test 2 record of Node field Referencing "Test 3", Test 3 record of Node field Referencing "Test 4".......etc..

So now we need to check till which corporation record of Node field  is empty . so that record display need to Display in CMDB ServiceDiscovered.

i tried below code , but not working

var corSYSID ='';
var bs = new GlideRecord('cmdb_ci_service_discovered');
bs.addQuery('sys_id','04398b559f030200fe2ab0aec32e70fa');
bs.addQuery('u_corporationISNOTEMPTY');
bs.query();
if (bs.next()) 
{
gs.print('Gate 1 :' + bs.u_corporation.getDisplayValue());

var cor = new GlideRecord('u_corporation');
cor.addQuery('u_nodeISNOTEMPTY');
cor.addQuery('sys_id', bs.u_corporation);
cor.query();
if (cor.next()) 
{
gs.print('Gate 2 :' + cor.u_node.getDisplayValue());

corSYSID = cor.u_node;
for (var i = 0; i < repeat; i++) 
{
var cor2 = new GlideRecord('u_corporation');
cor2 .addQuery('u_nodeISNOTEMPTY');
cor2 .addquery('u_node', corSYSID);
cor2 .query();
if(cor2 .next()) 
{
repeat ++;
corSYSID = cor2.u_node;
gs.print('Gate 3 :' + cor2 .u_node.getDisplayValue() + '--' + repeat);
}
 }
  }
}

[Subrahmanyam2]

Hi Sironi,

Yes! I took your base code and changed it.
There is an issue here with line "cor2.addquery('u_node', corSYSID);" in while loop.
If u_node is referencing "u_corporation" table, then the gliderecord addQuery condition mentioned above in while is not correct.
You might have to change it as below, I made few more modifications along with the mentioned line so that corSYSID has last found node in the hierarchy :

var corSYSID = '',
    repeat = 0;
var bs = new GlideRecord('cmdb_ci_service_discovered');
bs.addQuery('sys_id', '04398b559f030200fe2ab0aec32e70fa');
bs.addQuery('u_corporationISNOTEMPTY');
bs.query();
if (bs.next()) {
    gs.print('Gate 1 :' + bs.getDisplayValue("u_corporation"));
    var cor = new GlideRecord('u_corporation');
    cor.addQuery('u_nodeISNOTEMPTY');
    cor.addQuery('sys_id', bs.getValue("u_corporation"));
    cor.query();
    if (cor.next()) {
        gs.print('Gate 2 :' + cor.getDisplayValue("u_node"));
        if (cor.getValue("u_node") != "") {
            corSYSID = cor.getValue("u_node");
            while (corSYSID != "") {
                var cor2 = new GlideRecord('u_corporation');
                cor2.addQuery('u_nodeISNOTEMPTY');
                cor2.addquery('sys_id', corSYSID);
                cor2.query();
                if (cor2.next()) {
                    if (++repeat == 20) break; // You may want to remove this line later after your test is successful.
                    if (cor2.getValue("u_node") == "")
                        break;
                    corSYSID = cor2.getValue("u_node");
                    gs.print('Gate 3 :' + cor2.getDisplayValue("u_node") + '--' + repeat);
                } else {
                    break;
                }
            }
        }
    }
}

Please note that the code if you have circular relation between corporations like:
corporation 1 has corporation 2 as node and corporation 2 has corporation 3 as node and corporation 3 has corporation 1 as node then also system will go in infinite loop.

In parent child incidents servicenow OOB does not allow us to establish relationships in this manner.

You might want to check if there are no circular relationships for corporation table, and then implement this code.

If there are no circular relationships or chance of creating those remove "if (++repeat == 20) break;" line.

Thanks and regards,

Subrahmanyam

[Sironi]

1. As per Client system setup Definitely Last corporation record of u_node field will be empty .So we need that last corporation record of Name.

 i have ran your code but it was not giving any result

2. Are we increasing "repeat " value ? is that defined some where in script ? kindly help me where you defined that "repeat " increasing line.

------------------------------------------------------------------------------------

i have created small script to check u_node sys_id and name . but not sure it is giving wrong u_node .

var Hgr = new GlideRecord('u_corporation');
Hgr.addquery('sys_id', 'af501518071e011022f2f16c7c1ed070'); //test 1, Test1 of u_node is Test2
Hgr.query();
if (Hgr.next()) {

gs.addInfoMessage(gs.getMessage('Gate:' + Hgr.getDisplayValue('u_node') + "---" + Hgr.u_node));
}

[Sironi]

As Per Client system setup , in Corporation records chain last record of u_node will be empty. So we need that last record of Corporation name 

ServiceRecord --> Corporation 1, Corporation 1 of node is Corporation2, Corporation 2 of node is Corporation 3, Corporation 3 of node is Corporation 4, Corporation 4 of node is EMPTY.

now we need that Corporation 4 of sys-id and name.

i don't see TEST4 details in below result.

But when i tried to run script Second time it is taking around 100+ secns, but it was not ran.

i have removed below code and tried to ran code, but it was not ran.

if (++repeat == 20)
break; // You may want to remove this line later after your test is successful.

[Subrahmanyam2 · Accepted solution]

var corSYSID = '',
    repeat = 0;
var bs = new GlideRecord('cmdb_ci_service_discovered');
bs.addQuery('sys_id', '04398b559f030200fe2ab0aec32e70fa');
bs.addQuery('u_corporationISNOTEMPTY');
bs.query();
if (bs.next()) {
    corSYSID = bs.getValue("u_corporation");
    gs.print('Gate 1 :' + bs.getDisplayValue("u_corporation"));
    var cor = new GlideRecord('u_corporation');
    cor.addQuery('u_nodeISNOTEMPTY');
    cor.addQuery('sys_id', corSYSID);
    cor.query();
    if (cor.next()) {
        gs.print('Gate 2 :' + cor.getDisplayValue("u_node"));
        corSYSID = cor.getValue("u_node");
        while (corSYSID != "") {
            var cor2 = new GlideRecord('u_corporation');
            cor2.addQuery('u_nodeISNOTEMPTY');
            cor2.addQuery('sys_id', corSYSID);
            cor2.query();
            if (cor2.next()) {
                corSYSID = cor2.getValue("u_node");
                gs.print('Gate 3 :' + cor2.getDisplayValue("u_node") + '--' + (repeat++));
                if (repeat >= 20) break; // You may want to remove this line later after your test is successful.
            } else {
                // Corporation with empty node
                break;
            }
        }
    }
}

Thanks for clarification!

Please try this. 
In the code "addquery" was used instead of "addQuery", system might be ignoring that part of query and may be always landing at same node.

Thanks and regards,

Subrahmanyam Satti

[Sironi]

Hi Subhrahmanyam,

can we do like below ? kindly check once please advise if somewhere wrong inside.

 because code is not running , not sure why taking more time.

var corSYSID = '', repeat = 0;

var bs = new GlideRecord('cmdb_ci_service_discovered');
bs.addQuery('sys_id', '04398b559f030200fe2ab0aec32e70fa');
bs.addQuery('u_corporationISNOTEMPTY');
bs.query();
if (bs.next())
{
gs.print('Gate 1 :' + bs.getDisplayValue("u_corporation"));
var cor = new GlideRecord('u_corporation');
cor.addQuery('sys_id', bs.getValue("u_corporation"));
cor.query();
if (cor.next())
{
gs.print('Gate 2 :' + cor.getDisplayValue("u_node"));
if (cor.getValue("u_node") != "")
{
corSYSID = cor.getValue("u_node");

while (corSYSID != "")
{
var cor2 = new GlideRecord('u_corporation');
cor2.addQuery('sys_id', corSYSID);
cor2.query();
if (cor2.next())
{
if (cor2.getValue("u_node") != "")
{
corSYSID = cor2.getValue("u_node");
gs.print('Gate 3 :' + cor2.getDisplayValue("u_node") + '--' + repeat);
}else if (cor2.getValue("u_node") == ""){
gs.print('Gate 3 :' + cor2.getDisplayValue("u_corporation_name"));
break;
}
}
}
}
}
}