Need help with reference qualifier with comparing 2 tables

[vijayr2313]

I have two tables - Location and Branch Code Mapping

I have one field common in both the table is Branch Code

this field is not a reference field from Branch Code Mapping table

Now, I'm building the catalog item and having one variable called "branch code"

Now, I want to list all the branch code which is available in "Branch Code Mapping" table but we should use the Lookup select box variable type and table should link to Location table.

Means, we have 2000+ branch code available from location table but we should only display branch code which is available from "Branch Code Mapping".

So, for this I thoough of using the reference qualifer and write the script include. but couldn't get the solution. Can someone help me with script include and refernce qualier?

 getBranchCode: function() {

        var codeList = [];

        var bCode = new GlideRecord('x_branch_con_bcm_branch_rbcm_mapping');

        bCode.query();

        while (bCode.next()) {

            codeList.push(bCode.branch_code);

        }

        return codeList;

    },

[Vishal Birajdar]

Hi @vijayr2313 

If possible can you share some screenshot please...!!

[vijayr2313]

have updated the post

[Vishal Birajdar]

Hi @vijayr2313 

In script include function, 

Can you try below code :

 getBranchCode: function() {
        /*Here we will get the Branch code available from branch mapping table */
        var codeList = [];
        var bCode = new GlideRecord('x_branch_con_bcm_branch_rbcm_mapping');
        bCode.query();
        while (bCode.next()) {
            codeList.push(bCode.branch_code);
        }
        
       /* Now you can glide record on location table and return the location having codelist */
       var LocSysId =[];
       var grLoc = new GlideRecord('cmn_location');
       grLoc.addEncodedQuery('branch_codeIN'+ codeList.toString());  //use your backend name for Branch code

       grLoc.query();

       while (grLoc.next()){
        LocSysId.push(grLoc.getUniqueValue());

       }

       return "sys_idIN" + LocSysId.toString();

    },

In case "grLoc.getUniqueValue()" does not work , update the while loop like below 

 while (grLoc.next()){
        LocSysId.push(grLoc.getValue("branch_code")); // Use your backend value

       }

       return  LocSysId.toString();

Hope this helps....!!!