How to do autofill for a read-only Reference variable

[Flavio4]

Hello,

In a record producer I have:

1.  a "Location" variable, Reference type, to table cmn_location, for the user to select the location

2.  a variable "Security officer", Reference type, to a custom table "u_location_so", to display the Security officer for the location selected in 1.

I thought I could do it with the Auto-Populate variable tab, in the following way: as the table u_location_so, has the columns:

- u_location, type Reference to table cmn_location
- u_type, type String (to identify the type of SE I need to display)
- u_so, type Reference to table sys_user

I created a variable "so_location" of type Reference with:

- reference to u_location_so
- reference qual condition: [Type][=][Security] /* to select the right type of security officer
- attributes: ref_ac_columns=u_location;u_so /* to display the location and the name of the security officer
- auto-populate: Dependent question: "Location", Dot walk path:Name /* to use the value Name of the cmn_location table to index the u_location_so table.

Unfortunately only the u_location column is displayed by the so_location variable.

Is there an explanation?
Is there a way to solve the problem and show the Name of the sys_user record referenced by the u_so column of the u_location_so table referenced by the variable?

I tried to add also a variable type Reference to sys_user and auto-populate from the so_location variable, but I couldn't make it work.

I have the impression (but don't know how to check) that for table u_location_so the "key column" is Created, insteal of Location or other, and this makes the auto-populate to not work as expected.

Thank you in advance
Flavio

[Cheikh Ahmadou]

What You Want

In a Record Producer:

• User selects a Location (from cmn_location)

• The system auto-selects the right Security Officer (a sys_user) from the u_location_so table, based on:

  • u_location matching the selected Location

  • u_type = 'Security'

• You want to show/display only the Security Officer (u_so.name) to the user.

Why Auto-Populate Doesn’t Fully Work

You're using:

• so_location (Reference to u_location_so)

  • Reference qualifier filters to u_type = 'Security'

  • It shows only u_location — because it’s the display value of u_location_so, not u_so

And the Service Catalog auto-populate logic cannot follow a chain of references, like:

Location → u_location_so → u_so → sys_user.name

Solution
You can achieve this in 2 steps:

Step 1: Keep Location Variable as is
     Type: Reference
     Table: cmn_location
     Name: location
 

Step 2: Use a Catalog Client Script to Set the Security Officer

1. Create a new Reference variable:

   • Name: security_officer

   • Table: sys_user

   • Display: Yes

   • (You don't need to show the u_location_so record anymore)

2. Create a Catalog Client Script:Type: onChange
   Variable: location
   Applies to: Catalog Item or Record Producer

function onChange(control, oldValue, newValue, isLoading) {
  if (isLoading || !newValue) return;

  // Call a GlideAjax Script Include to fetch the right Security Officer
  var ga = new GlideAjax('GetSecurityOfficer');
  ga.addParam('sysparm_name', 'getSOForLocation');
  ga.addParam('sysparm_location_id', newValue);
  ga.getXMLAnswer(function(answer) {
    if (answer) {
      g_form.setValue('security_officer', answer);
    } else {
      g_form.setValue('security_officer', '');
    }
  });

Step 3: Create a Script Include (Client Callable)

var GetSecurityOfficer = Class.create();
GetSecurityOfficer.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
  
  getSOForLocation: function() {
    var locId = this.getParameter('sysparm_location_id');
    if (!locId) return '';

    var gr = new GlideRecord('u_location_so');
    gr.addQuery('u_location', locId);
    gr.addQuery('u_type', 'Security');
    gr.query();

    if (gr.next()) {
      return gr.getValue('u_so'); // sys_id of sys_user
    }

    return '';
  },

  type: 'GetSecurityOfficer'
});

Final Result

• User selects a Location

• Script Include looks up the right u_location_so record for u_type = Security

• The associated Security Officer (u_so) is retrieved

• The security_officer variable (of type Reference to sys_user) is populated

Optional: Make security_officer Read-only

If you don’t want users to change it manually:

• Add a Catalog UI Policy

  • Condition: location is not empty

  • Action: Make security_officer Read-only = true

[Muhammad Salar]

Try something like this for fields
ref_auto_completer=AJAXTableCompleter,ref_ac_columns=u_location;u_so,ref_ac_columns_search=true

Don't use auto populate because user field is not available in cmn_location, try to populate it by onchange client script on your selection variable

[Ankur Bawiskar]

@Flavio4 

The auto populate logic won't work as your cmn_location table should have a reference field which points to u_location table, then only it will work

Other wise, you can use onChange catalog client script on location variable and populate the 2nd variable

OR

You can use Lookup select box variable and use Lookup value filed - u_location

In Lookup label specify both the columns u_location,u_so

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

[Ankur Bawiskar]

@Flavio4 

Hope you are doing good.

Did my reply answer your question?

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