Reference Qualifier Script Include not returning records (multi-table filtering with arrays)

Mohamed_009
Tera Contributor

3 hours ago

Hi Team,

I am trying to implement a Reference Qualifier using a Script Include, but it is not returning the expected records.

When opening a reference field, records from the main table should be filtered based on:

  1. A parameter passed from the form (param1)
  2. Another parameter (param2)
  3. Logged-in user’s department
  4. Values derived from:
    • A mapping table (group + code relationship)
    • A configuration table (filter values)

      Logic Implemented

      1. Query mapping table using param2
      2. Collect group values and related codes
      3. Re-query mapping table using codes to expand group values
      4. Query config table using param1 to get filter values
      5. Query main table with conditions:
        • Type = fixed value
        • Status = A OR B
        • Category NOT IN certain values
        • Department = logged-in user
      6. Loop through results and match:
        • group field
        • filter field
      7. Return sys_idIN...

      ❗ Issue

      • Reference field is not showing any records
      • Script Include runs but returns empty result (sys_idIN)
      • Not sure if issue is:
        • Query conditions
        • Array matching (indexOf)
        • Reference qualifier call
        • Data mismatch (sys_id vs display value)

Code : 

getFilteredRecordIds: function(param1, param2) {

gs.info('Step1 ' + param1);
gs.info('Step2 ' + param2);

var resultIds = [];
var userDept = gs.getUser().getDepartmentID();

if (gs.nil(param1) || gs.nil(param2)) {
return 'sys_idIN';
}

// =====================================================
// STEP 1: Get Group Values (ARRAY ONLY)
// =====================================================
var groupList = [];
var codeList = [];

var mapGR = new GlideRecord('custom_mapping_table');
mapGR.addQuery('reference_field', param2);
mapGR.query();

while (mapGR.next()) {

var groupVal = mapGR.getValue('group_field');
var codeVal = mapGR.getValue('code_field');

if (groupVal && groupList.indexOf(groupVal) === -1) {
groupList.push(groupVal);
}

if (codeVal && codeList.indexOf(codeVal) === -1) {
codeList.push(codeVal);
}
}

// Second query using code
if (codeList.length > 0) {

var mapGR2 = new GlideRecord('custom_mapping_table');
mapGR2.addQuery('code_field', 'IN', codeList.join(','));
mapGR2.query();

while (mapGR2.next()) {

var groupVal2 = mapGR2.getValue('group_field');

if (groupVal2 && groupList.indexOf(groupVal2) === -1) {
groupList.push(groupVal2);
}
}
}

if (groupList.length === 0) {
return 'sys_idIN';
}

// =====================================================
// STEP 2: Get Filter Values
// =====================================================
var filterValues = [];

var configGR = new GlideRecord('custom_config_table');
configGR.addQuery('config_field', param1);
configGR.query();

while (configGR.next()) {
var val = configGR.getValue('filter_field');

if (val && filterValues.indexOf(val) === -1) {
filterValues.push(val);
}
}

if (filterValues.length === 0) {
return 'sys_idIN';
}

// =====================================================
// STEP 3: Main Table Filtering
// =====================================================
var mainGR = new GlideRecord('custom_main_table');

// Condition 1
mainGR.addQuery('type_field', 'X');

// Condition 2
var qc = mainGR.addQuery('status_field', 'A');
qc.addOrCondition('status_field', 'B');

// Condition 3 (FIXED)
mainGR.addQuery('category_field', 'NOT IN', 'value1,value2');

// Condition 4
mainGR.addQuery('user.department', userDept);

mainGR.query();

while (mainGR.next()) {

var filterVal = mainGR.getValue('filter_field');
var groupVal = mainGR.getValue('group_field');

if (filterValues.indexOf(filterVal) !== -1 &&
groupList.indexOf(groupVal) !== -1) {

resultIds.push(mainGR.getUniqueValue());
}
}

return 'sys_idIN' + resultIds.join(',');
}

0 Helpfuls
  • 54 Views
1 REPLY 1

Naveen20
ServiceNow Employee

2 hours ago

Check if this is the root cause

Parameter passing — GlideElement vs String

When you call this from a reference qualifier like:

 
 
javascript
javascript:new MyScriptInclude().getFilteredRecordIds(current.field1, current.field2)

current.field1 passes a GlideElement object, not a string. Your addQuery('reference_field', param2) might not match because it's comparing a GlideElement against the field. Fix:

 
 
javascript
// At the top of your function, force string conversion
param1 = param1 + '';  // or String(param1) or param1.toString()
param2 = param2 + '';
0 Helpfuls