Sorting Request Items by a VARIABLE

[Droid101]

So, one of our managers wants to run a report on all Request Items that contain a particular value in one of the variables. I can only think of how to run reports using the form values, but not variable values.

Is there a way to do this?

[jfarrer]

Out of the box there is not a way to do this well.

We had a requirement for searching the variable values but it would work for at least some reporting needs. We basically created a table with a reference to Request Item, the variable name, and variable value. We then wrote a business rule that populates the table with display values for all the variables that are used. The business rule is based on the underlying table that stores the variables.

We've only had it live for a few weeks, but it looks like it's working well for us so far. It does require a little bit of explanation for most users, but we put a knowledge base article together for that.

If you think it's something you'd be interested in I can dig up more details.

[Jace Benson]

I'd be interested in this too.

[jfarrer]

We created a Business Rule on the sc_item_option table that runs on Update with the condition:

current.sc_item_option.item_option_new.getDisplayValue() != ""

and does this:

var gr = new GlideRecord("sc_item_option_mtom");
var req;
gr.addQuery("sc_item_option", current.sys_id);
gr.query();
if (gr.next()) {
  req = gr.request_item;

  var new_record = new GlideRecord("u_sc_form_fields");
  new_record.addQuery("u_req_item", req);
  new_record.addQuery("u_field_name", current.item_option_new.getDisplayValue());
  new_record.query();

  if (new_record.next()) {
    if (current.item_option_new.type.getDisplayValue() == "Reference") {
      var gr2 = new GlideRecord(current.item_option_new.reference);
      gr2.get('sys_id', current.value);
      new_record.u_field_value = gr2.getDisplayValue();
    } else if (current.item_option_new.type.getDisplayValue() == "Lookup Select Box" || current.item_option_new.type.getDisplayValue() == "Lookup Multiple Choice") {
      var gr2 = new GlideRecord(current.item_option_new.lookup_table);
      gr2.get('sys_id', current.value);
      new_record.u_field_value = gr2.getDisplayValue();
    } else {
      new_record.u_field_value = current.value.getDisplayValue();
    }
 
    new_record.update();
  }
}

We then made a link to the Form Field Search table that this populates and provided a page with instructions for how to use it.