Hide a Catalog Item Reference Variable value if the same value is found on another table.

[mattmm]

1. I have a Catalog Item Variable Referencing the cmdb_ci_server table's name field (e.g. value "WindowsServer1" ).

2. I also have another table cmdb_group_contains_ci table with a reference field called configuration_item, referencing the cmdb_ci table's 'name' field (e.g. value "LinuxServer1")

My Variable is currently showing all name values in the cmdb_ci_server table (point 1. above), but I want to exclude/hide any values that are already present on the cmdb_group_contains_ci table (point 2. above)

.....so that a user cannot choose a server (name) that already exists on cmdb_group_contains_ci table.

I am hoping someone can point me in the direction of the right thing to do and also a possible script/Ref qualifier, or the like, to do this?

Thankyou in advance!

[Maddysunil]

@mattmm You can try with below steps

Create a Script Include:

Create a Script Include named something like ServerReferenceUtil.

var ServerReferenceUtil = Class.create();
ServerReferenceUtil.prototype = {
   initialize: function() {
   },

   // Function to get the list of CI names from cmdb_group_contains_ci
   getGroupContainsCI: function() {
      var ciNames = [];
      var gr = new GlideRecord('cmdb_group_contains_ci');
      gr.query();
      while (gr.next()) {
         ciNames.push(gr.configuration_item.name.toString());
      }
      return ciNames;
   }
};

Write a Reference Qualifier Function:

In the same Script Include, add a function that will serve as your Reference Qualifier. This function will filter out the values from cmdb_ci_server that are already present in cmdb_group_contains_ci.

// Function to exclude CI names from cmdb_group_contains_ci
getAvailableServerCI: function() {
   var groupContainsCI = this.getGroupContainsCI();
   var gr = new GlideRecord('cmdb_ci_server');
   gr.addQuery('name', 'NOT IN', groupContainsCI);
   gr.query();
   return gr;
}

Apply the Reference Qualifier:

In the Reference Qualifier field of your Catalog Item Variable (referencing the cmdb_ci_server table), enter the following script:

javascript:new ServerReferenceUtil().getAvailableServerCI()

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Community Alums]

Usually ServiceNow uses classless script includes for reference qualifiers. Common practice which is neglected by the majority of the developers. Also I would try a direct RefQual first if possible. But we dont know the use case in details so this are only specualtions.

Cheers,

Joro

[mattmm]

Thanks @Maddysunil , this didn't work unfortunately. Please refer to my screenshots for setup - 

1. variable - 

2. Script Include 

3. name (configuration_item) example on cmdb_group_contains_ci table

4. name example on cmdb_ci_server table

5. Still shows in variable drop down

[Community Alums]

// Function to exclude CI names from cmdb_group_contains_ci
getAvailableServerCI: function() {
var arr = [];
   var groupContainsCI = this.getGroupContainsCI();
   var gr = new GlideRecord('cmdb_ci_server');
   gr.addQuery('name', 'NOT IN', groupContainsCI);
   gr.query();
while(gr.next()){
arr.push(gr.sys_id + "");
}
   return arr.toString();
}

You miss next() which actually iterates throu the records.