How do I do a union with GlideRecords?

lesnettl · ‎12-16-2014

How do I do a union with GlideRecords? This is what I'm trying to accomplish:

If I query the sys_choice table to display the value column, I could do this with SQL:

select value from sys_choice where name='incident' and inactive='false' and element='subcategory' and dependent_value='Hardware-Desktop'

It returns these values

Desktop

Laptop

Monitor

Printer

Scanner

If I want to add "Other" to the result set, using SQL, I can write the query this way:

select value from sys_choice where name='incident' and inactive='false' and element='subcategory' and dependent_value='Hardware-Desktop' union 'Other' as value

How do I do this using GlideRecords? I have a lookup select box that displays the above list in a catalog form. This is the ref qual:

name=incident^inactive=false^element=subcategory^dependent_value=Hardware-Desktop

How can I get "Other" to display in the select box without adding it to the table?

tltoulson · ‎12-16-2014

Hi Larry,

There isn't really an option in ServiceNow to use UNION in reference qualifiers or to perform such a union to build a lookup select box. The design of the Lookup Select box is specifically to lookup values in a table. That said, you do have a couple of options:

1. Use a catalog client script on load that adds the option using g_form.addOption

2. Use the --None-- option as your "Other" if it isn't confusing for users

3. Use a regular Select Box variable and manually copy the options into Question Choices related list (or automate syncing of the options)... tedious but effective

4. Use a Macro variable type that builds a dropdown selection that meets your specific requirements

5. Submit an enhancement request to include an "Include Other" option on such dropdown similar to the Include None option... seems like a reasonable request and not likely uncommon.

I hope this helps. Please let me know if you need further assistance.

View solution in original post

tltoulson · ‎12-18-2014

Hi Larry,

I think you may have mis-marked the correct and helpful on this one. You want to make sure to mark the comments that were helpful or correct so that others who may have the same question can see which answers might help them.

lesnettl · ‎12-18-2014

Thanks for catching that, Travis. It should be correctly marked, now.