Creating Union and Join Operations in ServiceNow

[jiml17055120604]

Is it possible to create a union or join between two forms in ServiceNow?

We have a customized implementation for change management with around 13,000 records in the sys_user table, each representing a network user. We also need to track an additional 2,000 individuals who are external system users, separate from the core network users.

Initially, we planned to build a custom table for the non-network users, manually adding them alongside records copied from the sys_user table. However, this approach seemed inefficient. Our next idea was to incorporate these non-users into the system with a flag indicating they're not actual system users. However, we'd need to adjust existing functionality to exclude these individuals from lookup lists (possibly through roles or by adding filters on specific screens), while ensuring that both user types are available for our new functionality.

Ideally, we'd like to implement something similar to an SQL union or join—essentially, a new table (u_person) for the 2,000 external users, combined with the sys_user table into a unified data source for lookups.

I’ve seen this capability in Remedy and was wondering if ServiceNow has a similar feature built in.

[Community Alums]

Hi @jiml17055120604 ,

Yes, it's possible, what i do personally is create a Database View but primarily i use it for Reporting purposes , please refer to the Doc link : Database view creation 

Please refer to the below threads :

https://www.servicenow.com/community/developer-forum/implementing-a-union-or-join-form-in-service-no...

https://www.servicenow.com/community/developer-blog/servicenow-admin-101-how-to-do-a-union-query-usi...

[Ankur Bawiskar]

@jiml17055120604

yes you can use the database view feature as shared by Sandeep

Let us know if you require any help post going through those links/documents.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[David - Mawer]

Hi @jiml17055120604,

The sad news is that ServiceNow doesn't allow union joins using Database Views.

(if you look at the Database view creation page - you can see that the closest we get is a left join.)

The best work-around I've found is this: -

There is a way to use two GlideRecords to create an array which is the effective union of two tables - as shown in this blog by sabell2012.

So - you can hack a union with the following steps: -

1. use a "Remote Table" (this is a [sys_db_object] where "Remote Table" [scriptable_table] is true)
to define the columns for the resulting view.

2. Use a "Remote Table Definition" [sys_script_vtable] to create the union resulting data.

(write the script here that creates the union result as an array - there is an OOTB definition called "Portal Favorite" (sys_script_vtable with sys_id = 4664defdeb322010e0ef83c45e522876) that shows how to build the array for the table).

The problems with this hack: -

1. You are limited to 1000 rows - since that's what Remote tables can handle by design.

2. it can be slow - depending on how long it takes to run your script.