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]

@appstorm,

Yes, that’s not required.

Please try:

javascript: 'cnum=' + current.u_cnumber

[appstorm]

Tried and still nothing.

Just to reiterate 'cnum' should return the matching u_cnumber value from the 'LARF Banner Student' table and display it in the student_information field.  Both fields are references returning strings, albeit different tables.

[Amit Verma]

Hi @appstorm 

Did you got a chance to explore Auto-Populate feature for reference variables? Refer https://www.servicenow.com/community/developer-articles/auto-populate-a-variable-based-on-a-referenc...

Thanks and Regards

Amit Verma

[Ankur Bawiskar]

@appstorm 

few questions

1) field on which ref qualifier is applied is on which table?

2) share the dictionary screenshot of the field on which it's applied and it's referring to which table

3) share some more details about the other table screenshots etc

If my response helped please mark it correct and close the thread so that it benefits future readers.