Advanced Reference qualifier not retrieving correct records.

[Community Alums]

Hi all,

I have Kb_knowledge table for articles with a kb_knowledge_base and can_read_user_criteria fields, boths are fields doing Reference to another table. My requirement is to show User criteria List depending on the selected Knowledge base, for example:
For Union Associates KB, the list should only show specific user criteria records: Union - Phillipsburg, NJ Associates, Union - Bridgeport, NJ Union Associates, etc.

To accomplish this requirement, I used a client script, script include and an Advanced reference qualifier, but the data shown in User criteria list is still all records in User criteria table.

I will show my script and relevant records, if you have phased this issue let me know how you resolve it, thank you!

Script include:

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

    getUserCriteria: function(knowledge_base) {
        var knowledgeBase = this.getParameter(knowledge_base);
        var options = [];

        var gr = new GlideRecord('user_criteria');
        if (knowledgeBase == '136ccd4847f41210da77b5ea216d43f3') {
            //gr.addActiveQuery();
            gr.addEncodedQuery('sys_idIN69b5137c47019290da77b5ea216d437a,1b2a62b447c95290da77b5ea216d4340,3a17a72293309e10bed232974dba1012,b456672293309e10bed232974dba10b1');
        } else if (knowledgeBase == '4595dc4447701210da77b5ea216d43b6' || knowledgeBase=='37ce8d4c47f41210da77b5ea216d432a' || knowledgeBase== '4091bb73932cd650bed232974dba108e') {
        gr.addEncodedQuery('nameLIKEAmericas^ORnameLIKEEurope^ORnameLIKEUSA^ORnameLIKEBrazil^ORnameLIKEChile^ORnameLIKECosta Rica^ORnameLIKEMexico');

        }
        else return [];
       
        gr.query();
       
        while (gr.next()) {
            options.push(gr.sys_id);
        }

        var ucId = 'sys_idIN';
        for(var i=0 ; i<options.length ; i++) {
            ucId = ucId + options[i] + ",";
        }
        return ucId;

        //return "sys_idIN" + options.join(',');
    },

    type: 'getoptionsforUsercriteria'
};

Client script:

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue === '') return;
   
    var ga = new GlideAjax('getoptionsforUsercriteria');
    ga.addParam('sysparm_name', 'getUserCriteria');
    ga.addParam('knowledge_base', newValue);
    ga.getXMLAnswer(function(response) {

        var options = response.split(',');
        //var options = JSON.parse(response);
        options.forEach(function(option) {
            if(option){
            g_form.addOption('can_read_user_criteria', option);
           
            }
        });
    });
}

and my Reference qualifier is next:

Any useful info? thanks

[Brad Bowman · Accepted solution]

addOption is not valid on List fields, and a Client Script cannot affect a reference qualifier in this way, so your client script is not needed.  In the Script Include, the argument in the function declaration, knowledge_base, is the value that should be received from the reference qualifier, and the one you need to use in your script, not using this.getParameter that comes from a Client Script, if this script were Client callable.  I've made various other changes like forcing the sys_id to a string when pushing to an array which is a must, reverting the return to what you had commented, and adding some temporary logging in case it's still not working, you can confirm the value passed in from the reference qualifier, see if/which records are returned, etc.

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

    getUserCriteria: function(knowledge_base) {
        gs.info('SI running - kb : ' + knowledge_base);
        var options = [];
        var gr = new GlideRecord('user_criteria');
        if (knowledge_base == '136ccd4847f41210da77b5ea216d43f3') {
            //gr.addActiveQuery();
            gr.addEncodedQuery('sys_idIN69b5137c47019290da77b5ea216d437a,1b2a62b447c95290da77b5ea216d4340,3a17a72293309e10bed232974dba1012,b456672293309e10bed232974dba10b1');
        } else if (knowledge_base == '4595dc4447701210da77b5ea216d43b6' || knowledge_base == '37ce8d4c47f41210da77b5ea216d432a' || knowledge_base == '4091bb73932cd650bed232974dba108e') {
            gr.addEncodedQuery('nameLIKEAmericas^ORnameLIKEEurope^ORnameLIKEUSA^ORnameLIKEBrazil^ORnameLIKEChile^ORnameLIKECosta Rica^ORnameLIKEMexico');
        } else {
			return '';
		}
        gr.query();
        while (gr.next()) {
			gs.info('SI running - record found: ' + gr.name);  
            options.push(gr.sys_id.toString());
        }
		gs.info('SI running - return: ' + options.join(','));  
        return "sys_idIN" + options.join(',');
    },

    type: 'getoptionsforUsercriteria'
};

Also add to the Attributes on the List field to ensure it is updated when the reference field value changes:

ref_qual_elements=kb_knowledge_base

[Community Alums]

Thanks Brad, it worked out!