Make a Reference value depend on a List Choice value

[Chronos]

Hi,

I have a table Company(core_company) which has 2 fields Name(name,type : String) and Area(u_area, type : Choice). In Area there are 10 values(or more).
For example, when Area = Russia, there will be 3 Name(A,B,C); Area = Japan will have 3 Name(C,D,E)
I create a custom table called Test, which also has 2 fields, Location(u_choice, type : Choice) and Place (u_place, type : references).

In Location I connect to Choice Table (core_company) of Area and get the choice value of Area.
In Place I refer to the core_company table, and the Place field depends on Location.

I would like that when the value of Location changes, the value of Place( same Name (core_company)) appears only the value that matches Location. Is there a way to do it with Script ?.
Thanks

[Ratnakar7]

Hi @Chronos ,

Yes, it is possible to achieve this with a script. You can use a client script to filter the available options in the Place field based on the selected value in the Location field.

Below is an example script that you can use:

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue === '') {
        return;
    }
    
    // Get the selected value from the Location field
    var location = g_form.getValue('u_choice');
    
    // Query the core_company table to get the matching records
    var gr = new GlideRecord('core_company');
    gr.addQuery('u_area', location);
    gr.query();
    
    // Populate the Place field with the matching records
    var placeField = g_form.getReference('u_place');
    placeField.clearOptions();
    while (gr.next()) {
        placeField.addOption(gr.getDisplayValue('name'), gr.getUniqueValue());
    }
}

You can add this script as a client script on the Test table, and set it to run on the onchange event of the Location field. When the Location field is changed, the script will query the core_company table to get the matching records based on the selected location, and populate the Place field with the matching records.

If my response helps you to resolve the issue close the question by ✅Accepting solution and hit 👍thumb icon. From Correct answers others will get benefited in future.

Thanks,

Ratnakar

[Chronos]

Hi, @Ratnakar7 

I followed what you said but when I change Location, the screen will appear like this 

[Ratnakar7]

Hi @Chronos ,

You can have another option as below:

Please update reference qualifier for Place field on your custom table as below:

Thanks,

Ratnakar

[Jim Coyne · Accepted solution]

It can actually be done without code.  I quickly added the new "Area" field on the Company table and created a new "Test" table with both "Location" and "Place" fields like you have.

Here are the Companies that have an Area selected:

And here's the new "Test" table, configured with the "Location" field pointing back to the "Area" choices on the "Company" table:

The trick is to configure the "Place" Reference field with the following setup:

1. set the "Use reference qualifier" field to "Advanced" and the "Reference qual" field to "javascript:'u_area=' + current.u_location" (you might have to switch to the "Advanced" view if not already)

2. add an attribute to the field (at the bottom of the form) which tells the system to send back the value of the "Location" field so it can be used in the Reference Qualifier

So now when you select a Location, only those matching Companies can be selected:

Make sure any field names I am using here match what you actually have in your instance and change accordingly.