Use Reference Qualifier to Display Values from Two Different Tables

[appstorm]

To make a reference field's display value depend on another reference field's value (from a different table).  I am using a Client Callable Script Include and an Advanced Reference Qualifier, but not having any luck.

var GetStudentInfoByCnum = {
    getFilter: function(current, table) {
        // 'current' is the record of the Source Table
        // 'table' is the table of the Target Field
        
        // Extract the relevant value from the Source Field (u_cnumber)
        var sourceFieldValue = current.u_cnumber; // Assuming 'u_cnumber' is the source field name

        // Log the source value
        gs.info('GetFilter: Extracted u_cnumber from current record: ' + sourceFieldValue);

        // Build the filter condition based on the Source Field value (u_cnumber)
        var filter = 'cnum=' + sourceFieldValue; // 'cnum' is the display field on the form
        
        // Log the filter being built
        gs.info('GetFilter: Filter condition built: ' + filter);

        return filter;
    },

    getStudentInfoByCnum: function(current) {
        var cnum = current.u_cnumber; // Get the value from the source field 'u_cnumber'
        
        // Log the cnum value
        gs.info('GetStudentInfoByCnum: Searching for student with cnum: ' + cnum);
        
        // If no cnum is provided, return empty string
        if (!cnum) {
            gs.info('GetStudentInfoByCnum: No cnum provided, returning empty string');
            return '';
        }

        // Create a GlideRecord to query the sn_uni_req_larf_banner_stu table
        var gr = new GlideRecord('sn_uni_req_larf_banner_stu');
        gr.addQuery('cnum', cnum); // Filter by the cnum value (the display field)
        gr.query();

        // Log query result
        gs.info('GetStudentInfoByCnum: Query executed for cnum: ' + cnum);
        
        // If a matching record is found, return the u_cnumber value
        if (gr.next()) {
            gs.info('GetStudentInfoByCnum: Found matching record, returning u_cnumber: ' + gr.u_cnumber);
            return gr.u_cnumber + ''; // Return the u_cnumber value (the source field)
        }

        gs.info('GetStudentInfoByCnum: No matching record found for cnum: ' + cnum);
        return ''; // No matching record found
    }
};

var filter = new GetStudentInfoByCnum().getFilter(current, 'sn_uni_req_larf_banner_stu');
gs.info('Reference Qualifier: Generated filter: ' + filter);
answer = filter;

I've also tried a CS with a SI using GlideAjax and get no result.  Is there another way to achieve this and if so, can someone provide me with a good example? 

Thank you!

[Medi C]

Hi @appstorm,

Please have non-client callable script include and adjust your reference qualifier as follow:

javascript: new GetStudentInfoByCnum().getFilter(current, 'sn_uni_req_larf_banner_stu');

But taking a look at your code, you would need to adjust the function as the field is on a different table:

    getFilter: function(current, table) {
        // 'current' is the record of the Source Table
        // 'table' is the table of the Target Field
        
        // Extract the relevant value from the Source Field (u_cnumber)
        var sourceFieldValue = current.u_cnumber; // Assuming 'u_cnumber' is the source field name

		var sysIds = [];
		var gr = new GlideRecord(table);
		gr.addQuery("cnum", sourceFieldValue);
		gr.query();
		while(gr.next()){
			sysIds.push(gr.getValue("sys_id"))
		}

        // Build the filter condition based on the Source Field value (u_cnumber)
        var filter =  "sys_idIN" + sysIds; 
        
        // Log the filter being built
        gs.info('GetFilter: Filter condition built: ' + filter);

        return filter;
    }

[Medi C]

@appstorm 

You can also try:

javascript: 'cnum=' + current.u_cnumber

Which could be also achievable by Simple Reference Qualifier

[AshishKM]

@appstorm , this option is best one, add this filter condition to  "ref qual" of second reference field. When both field are reference type, dont need to write separate script.

[appstorm]

So, just confirming the Script Include, below is not required (which still doesn't work, btw).  

var GetStudentInfoByCnum = {
    getFilter: function(current, table) {
        // 'current' is the record of the Source Table
        // 'table' is the table of the Target Field
        
        // Extract the relevant value from the Source Field (u_cnumber)
        var sourceFieldValue = current.u_cnumber; // Assuming 'u_cnumber' is the source field name
        gs.info('GetFilter: Extracted u_cnumber value from current record: ' + sourceFieldValue);
        
        if (!sourceFieldValue) {
            gs.info('GetFilter: u_cnumber is empty or undefined, returning no filter.');
            return ''; // No filter if u_cnumber is not provided
        }

        // Initialize an array to hold the sys_ids
        var sysIds = [];
        var gr = new GlideRecord(table);
        gr.addQuery("cnum", sourceFieldValue);
        gr.query();

        // Log the number of records being queried
        gs.info('GetFilter: Executing query for cnum=' + sourceFieldValue + ' in table ' + table);

        // Loop through the query results and collect sys_id values
        while (gr.next()) {
            var sysId = gr.getValue("sys_id");
            sysIds.push(sysId);
            gs.info('GetFilter: Found sys_id: ' + sysId); // Log each sys_id found
        }

        // Check if any sys_ids were found
        if (sysIds.length === 0) {
            gs.info('GetFilter: No records found for cnum=' + sourceFieldValue);
        } else {
            gs.info('GetFilter: Found ' + sysIds.length + ' records with cnum=' + sourceFieldValue);
        }

        // Build the filter condition based on the sys_id values
        var filter = sysIds.length > 0 ? "sys_idIN" + sysIds.join(',') : ""; // Join sys_ids for the filter condition
        
        // Log the filter being built
        gs.info('GetFilter: Filter condition built: ' + filter);

        return filter;
    }
};

This isn't even returning any logs.