How do I do a union with GlideRecords?

[lesnettl]

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?

[Brad Tilton]

I think this blog post may help you out:

ServiceNow Admin 101: How to Do a UNION Query Using GlideRecord | Cloud Sherpas

[lesnettl]

Thanks Brad for the quick response. Very helpful information. I will try your suggestions later this week.

[tltoulson · Accepted solution]

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.

[lesnettl]

Thanks, Travis for the detailed information. I will try your suggestions later this week.