Select values for a reference field, based on a table and another reference field

Question

I wish to select a field and based on the linkage for that field, only choose values from another field which have not been linked earlier.

We have 4 tables and forms.

• 2 are reference data tables.

  • These have data for Users and Goods
  • UsersTbl:
    • UserID: Auto Number
    • UserName: String
    • UserEmail: String
    • UserAddress: String
  • GoodsTbl:
    • GoodsID: Auto Number
    • GoodsName: String

• 1 is a linking table (LinkTbl). This has a 1 to many relation between the Users and Goods

  • For each User, there can be multiple Goods
  • Both the fields on this table are reference fields to the 2 tables for Users and Goods
    • LinkID: Auto Number
    • GoodsRef: Reference - GoodsName
    • UserRef: Reference - UserName
    • DateCreated: Date/Time

• The final table (NewLinkTbl) is for creating data for the linking of Users with Goods. Due to business requirements, we are keeping this separate.

  • NewLinkID: Auto Number
  • UserRef: Reference - UserName
  • GoodsRef: Reference - GoodsName
  • LinkID: Number
  • DateCreated: Date/Time

• For the final table, I want to select and use a value from the the Users (Reference) first. - Based on that, when I click the GoodsRef field, I only wish to show the data values, where the LinkTbl DOES NOT have a link already between the User and the Goods.

I tried this:

• Client Script
• onChange in NewLinkTbl
• Field: UserRef

    function onChange(control, oldValue, newValue, isLoading, isTemplate) {
        if (isLoading || newValue === '') {
            return;
        }
        // Call the script to fetch the records which are not matching
        var ga = new GlideAjax('ScriptIncludeJS');

        // Fetch the value of the User selected
        var user_ref = g_form.getValue('user_name');
        ga.addParam('sysparam_name', 'getNotLinkedGoods');
        ga.addParam('sysparam_user', newValue);
        ga.getXMLAnswer(DisplayGoods);
    }

    // Callback function to process the response returned from the server
    function DisplayGoods(response) {
        var jsonResultObj = JSON.parse(response);
        //alert(jsonResultObj);
        g_form.setValue('linked_goods_name', jsonResultObj);
    }

• Script Include
• Client Callable
• Accessible from: All application scopes

    var ScriptIncludeJS= Class.create();
    ScriptIncludeJS.prototype = Object.extend(global.AbstractAjaxProcessor, {

        getNotLinkedGoods: function() {
            // Declare and initialize the return JSON
            var jsonString = {};

            // Retrive the value of the User record
            var userRef = this.getParameter('sysparam_user');
            var goods = [];

            // Query and get all the linked Customs Brokers
            var grAMObj = new GlideRecord('x_478634_att_pro_0_NewLinkedTbl');
            grAMObj.addQuery('NewLinkedTbl.UserRef', '<>' + userRef);
            grAMObj.query();

            // Loop through all the values, which do not match and store them
            while (grAMObj.next()) {
                goods.push(grAMObj.get_values());
            }
            return goods;
        },
        type: 'ScriptIncludeJS'
    });

I tried with the above, but it is not working.

Answer 1

Got an answer from the ServiceNow Community forums, which I have modified a bit and it is working for me.

For the field which needs to show the reduced data set, first select it on 'Studio' and then change the 'Reference Specification' by clicking on the field and in the field for 'Use reference qualifier' to 'Advanced'. In the 'Reference qual' field, put the following:

    javascript: (new ScriptIncludeJS()).getNotLinkedGoods(current.user_name);

Then in the Script Include, create a new script and use the following for it:

    var ScriptIncludeJS= Class.create();
    ScriptIncludeJS.prototype = Object.extend(global.AbstractAjaxProcessor, {

        getNotLinkedGoods: function(userID) {
            // Create a variable for the Array of the Goods to be shown
            var goods = [];

            // Select the LinkedTbl table
            var grObj = new GlideRecord('x_1234_pro_0_linkedtbl');

            // Get the User details from the form on which the field is displayed as 'userID'
            // For the table selected above, use the linked-table
            grObj.addQuery('user_name', userID);
            grObj.query();

            while(grObj.next()) {
                // prepare the array of goods names as a PUSH with the table object
                goods.push(grObj.goods_name.toString());
            }
            return 'sys_idNOT IN' + goods.toString();
        },
        type: 'ScriptIncludeJS'
    });

This works correctly for me.

I select the 'User Name' reference field, which brings up the list of all the users from the data table.

Then I select the 'Goods Name' reference field, which shows only those goods that have not been already linked in the table.