Implementing a union or join form in Service-now

[#1]

Is it possible to implement a union or join between two forms in Service-now?

We have a current implementation that has been heavily customised and is used for change management. There are around 13000 records in the sys_user table, all of which relate to users of the network. We also want to record the details of another couple of thousand individuals who are system users of the system but external contacts.

We were originally going to create a custom table into which the non-users would be entered manually and the users copied from the sys_user table. This didn't seem very efficient, so we then decided to add the non-users into the system with some sort of flag to denote that they are not system users. However, the existing functionality would need to be modified to exclude these individuals from look-up lists (e.g. using roles or adding filters on a screen-by-screen basis), whilst both types of records should be available to our new functionality.

Ideally, what we would like is the equivalent of a SQL union or join so that we can have a new u_person table holding our 2000 extra individuals, and another table that is a union of sys_user and u_person. We could then use this table for look-ups.

I know Remedy has this ability, so wondered if it had found its way into Service-now.

Thanks,
Simon

[john_roberts]

You could add them to sys_user and have a flag or choice list to identify the differences or you can create a new table that is inherited from sys_user. With the inherited table option, if you reference sys_user you will get all of the users in both tables.
Now as for referencing one or both types of users (regardless of above option used) you will need a reference qualifier. The qualifier script would check for the table that the form is on then filter the sys_user list if needed.
Here's an example I've used in the past to define different qualifers on the assigned_to field based on the task type.

//global business rule
//set field reference qualifier to javascript:filterUserType()
function filterUserType() {
//no filter if this is on the incident table
if (current.sys_class_name == 'incident')
return;
//do not show external users
return 'u_external=false';
}

I'm not sure if this covers your case but you could do something similar depending on how you are identifying the initial trigger if it's not based on table name.

Disclaimer: by default the table creator prevents you from extending system tables such as sys_user. This would have to be overridden by modifying the table_columns UI Page.