Efficient way to get name of "something" from sys_id in question_answer?

patk1000 · ‎10-16-2015

I have a simple reference field on a record producer. On submit, the form writes the variables to the question_answer table. In the case of the reference fields, the value column of question_answer contains the sys_id.

Here's the dilemma: I want to be able to able to get the name back of that "thing", but it appears the only way to do that is to then retrieve the sys_id from question_answer -> do another lookup in the workflow to pull the glide record of that item.

Seems kinda silly that I had the strongly typed reference in the form, that writes out a string, that I then have to remember the table the string referred to in order to get more data.

Is there a better way to get something as simple as the name of the thing represented by the sys_id? I don't want to have to do this:

function get_question_answer_ci(sys_id) {
   var sys_id = null;
   var gr = new GlideRecord('question_answer');
   gr.addQuery('table_sys_id', sys_id);
   gr.query();
   while(gr.next()) {
         if(gr.question.name == 'cmdb_ci'){
                 sys_id = gr.value;
         }
   }
   return sys_id;
}

function get_ci_name(sys_id){
   var gr = new GlideRecord('cmdb_ci');
   gr.addQuery('table_sys_id', sys_id);
   gr.query();
   if(gr.next()) {
             gs.print(gr.name);
   }
}

var sys_id = get_question_answer_ci('de5b64286f470e40b5eb36412e3ee421');
if(sys_id){
   get_ci_name(sys_id);
}

patk1000 · ‎10-19-2015

Spoke with Steven (on my team) who advised that the question column in question_answer is actually a reference to the question table. Did some digging and found that the question table has a column for type. The value of type is defined by a choice list, one of which is 'Reference'.

The reference column on the questions table lists the name of the table being reference for that particular question. Its trivial now to get to the actual glide record from the reference table. Here's an example of the code to run, followed by the output if run in a background script:

function print_tables(sys_id) {


   var gr = new GlideRecord('question_answer');


   gr.addQuery('table_sys_id', sys_id);


   gr.query();


   while(gr.next()) {


               if(gr.question.type.getChoiceValue() == 'Reference'){


                         gs.print(gr.question.name + ' ->   ' + gr.question.reference);


                 };


   }


}






var change_sys_id =   'd5b7164c6f1fc280b5eb36412e3ee4c7';


print_tables(change_sys_id);

*** Script: requested_by -> sys_user

*** Script: supporting_team -> sys_user_group

*** Script: ltm_cluster -> u_network_vip_lb_clusters

*** Script: vip_category -> u_network_vip_categories

*** Script: vip_type -> u_network_vip_supported_types

*** Script: pool_trust_zone -> u_network_vip_trust_zones

*** Script: existing_ssl_certificate -> u_cmdb_ci_ssl_cert

Side note: I really like the 'getChoiceValue()' method.

View solution in original post

patk1000 · ‎10-19-2015

A caveat to this solution: It appears that the reference column on the questions table isn't always populated for variable types whose value will always be a sys_id. For example: choosing Lookup Select Box will not populate the column, but choosing a type of Reference will. Interestingly, the column will remain populated if you move from a Reference type to a Lookup Select Box type.

So basically, always make sure the reference column on the questions table is populated if you need it.