iIs it possible to fill a GlideRecord dot walk with a variable?

[sleepycrown]

Hi hi,

I have a script that looks up a Lookup Multiple Choice variable then looks up valid items for that variable based on the table it pulls its choices from and the reference qualifier set. The part I'm stuck on is making use of the Lookup value field, which says which column to get data from. 

See my script below:

var lookupChoices = []; //array to hold the found choices
var catItem = some_sys_id;
var fieldName = lookup_multiple_choice;
var gr = new GlideRecord('item_option_new');
gr.addQuery('cat_item', catItem);
gr.addQuery('name', fieldName);
if  (gr.next()) {
  var fieldValue = gr.lookup_value; // in this case 'name'
  var gr2 = new GlideRecord(gr.lookup_table);
  gr2.addEncodedQuery(gr.reference_qual);
  gr2.query();
  while (gr2.next()) {
    lookupChoices.push(gr2.<column_name>.toString());
  }
}

I would like to be able to set <column_name> dynamically with whatever field the Lookup value field is looking at (in this case 'name'). I don't want to code a single column name because the table and column this variable could reference will vary in each place it's used. 

I store the column name pulled from the variable in

var fieldValue = gr.lookup_value;

However, it cannot be used like this in the dot walk: 

lookupChoices.push(gr2.fieldValue.toString());

It makes sense why this doesn't work, so I'm not necessarily looking for an explanation there, but rather another way to accomplish this without having to first know the column name. The second GlideRecord gives me the correct list, but I can't select the required column. 

Any ideas?

[Benjamin A]

Hello @sleepycrown,

I think this script will provide you with what you are looking for.

var lookupChoices = []; //array to hold the found choices
var catItem = 'ec80c13297968d1021983d1e6253af32'; // Catalog Item ID
var varName = 'color'; // Name of variable you want the choices of

// Query for item_option_new
var gr = new GlideRecord('item_option_new');
gr.addQuery('cat_item', catItem);
gr.addQuery('name', varName);
gr.query();

// If result found...
if(gr.next()){
    
    // Query for question_choices associated with sys_id of item_option_new
    var gr2 = new GlideRecord('question_choice');
    gr2.addQuery('question', gr.sys_id);
    gr2.query();

    // For each choice
    while(gr2.hasNext()){
        gr2.next();
        // Add choice text (or switch to value) to array
        lookupChoices.push(gr2.text.toString());
    }
}
gs.info(lookupChoices);

[sleepycrown]

Hi Benjamin!

Thanks for your reply! Unfortunately, the choices for a Lookup Multiple Choice field don't exist on the question_choice table. They are generated when the form loads based on the table, reference qualifier, and column specified. 

For example, if Lookup from table is set to sys_user, Lookup value field is set to name, and add a reference qualifier of department=a581ab703710200044e0bfc8bcbe5de8 (baseline sys_id for the finance department), it will populate a list of names from that table meeting that condition.

When I query the Lookup from table, I am able to do so with all of the conditions, but can't dot-walk to the correct column name. I would have to know in advance that the Lookup value field is name. 

[Bert_c1]

Hi,

Trying you script (in Scripts - Background) with values for a record where 'lookup_value' is not empty:

var lookupChoices = []; //array to hold the found choices
var catItem = '79c99042b7321010e54deb56ee11a907';
var fieldName = 'lookup_value';
var gr = new GlideRecord('item_option_new');
gr.addQuery('cat_item', catItem);
gr.addQuery('name', fieldName);
gr.query();
if  (gr.next()) {
  gs.info('fieldValue = ' + fieldName + ", lookup_table = " + gr.lookup_table);
  var fieldValue = gr.lookup_value; // in this case 'name'
  var gr2 = new GlideRecord(gr.lookup_table);
  gs.info("querying " + gr.lookup_table + " with: " + gr.reference_qual);
  gr2.addEncodedQuery(gr.reference_qual);
  gr2.query();
  gs.info("Found " + gr2.getRowCount() + " records.");
  while (gr2.next()) {
    lookupChoices.push(gr2.column_name.toString());
  }
  gs.info("lookupChoices length = " + lookupChoices.length);
}

The lookup table is 'sys_dictionary', there is no 'reference_qual' value and I get:

*** Script: fieldValue = lookup_value, lookup_table = sys_dictionary
*** Script: querying sys_dictionary with: 
*** Script: Found 185819 records.
*** Script: lookupChoices length = 185819

which is the number of sys_dictionary records in my instance.

I don't think it is possible to dynamically update javascript for where you have "<column_name>". But you could add logic to query sys_dictionary for fields in 'lookup_table' to get those and code using those results to load 'lookupChoices'.

You could provide more details as what values to use in the script, so it could be tested. I'm not clear on what you are trying to achieve.

[sleepycrown]

Hi Bert! 

Thanks for your reply! The goal would be for the script to provide an appropriate choice based on the table and column it references.

I think you're right in that I can't dynamically update the script where I need to dot-walk. The primary hurdle is that I can't provide a response without knowing the column name. It will technically still work and just insert a choice, but obviously that isn't quite my intended goal. It's a shame we can't select a single column in a GlideRecord 😕