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

Mohamed_009 · 3 weeks 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:

A parameter passed from the form (param1)
Another parameter (param2)
Logged-in user’s department
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(',');
}

Naveen20 · 3 weeks 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 + '';

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

Logic Implemented

❗ Issue