How To: Script Advanced Relationships (Apply to + Query from) - Scoped & Global

[David Cole1]

Background

ServiceNow's Table Relationship records (and more specifically, the sys_relationship table) aid in the ability to report on, and view connections between tables. This can be extremely useful when designing custom tables or fields, which you want to... relate to... related records.

For purposes of demonstration, my screenshots and examples will be based on a custom table in one of our custom apps. Said custom table has a "parent" field, referencing the task table, which drives the relationship. The relationship target here is seeing Billing Tasks as a related list on the task table (and, by extension - all tables that extend it).

Note that, while example script(s) makes use of EMCAScript 2021, simple for loop replacement of forEach instances, and const replacement with var, negates this necessity.

Basic vs. Advanced

In the "basic" (advanced=false) view, your table selection is bounded, as you are prompted to select both the "Applies to table," (basic_apply_to) as well as the "Queries from table:" (basic_query_from)

Anecdotally, this level of specifity is enough for most relationships I have needed to build in the system. However, if you check the "Advanced" box on the relationship record, the Reference fields for these disappear, and you are instead provided two script fields to create definitions:

• Apply to (apply_to)
• Query from (query_from)

ServiceNow's documentation on "Create defined  related lists" slightly mentions this:

Apply to	Type a script that sets the answer variable to true when a user opens a form on the desired table. This field is only visible with the Advanced check box selected.
Query from	Type a script that sets the answer variable to the name of the table from which the related list retrieves data. This field is only visible with the Advanced check box selected.

The docs, however, fail to provide indication or references for what variables are available to use, and some other rather necessary information to work with relationships. That is what this post covers.

Why use Advanced?

There are three reasons that would necessitate this, each correlating to one of the script fields on the form. Each will be explained below, along with the example from our use case, with the exception of Query From, which will follow the same principles explained, but [we] do not have a solid example of.

"Query with" (query_with)

• Most common
• Refining the "connection" between the tables.
• Manipulate the current object to add/remove filters, cancel entirely, etc.
• If the relationship between your Parent & Child record(s) is not static, and thus, needs to be scripted, you will need to toggle Advanced, and use the Query with script to alter this behavior.
• Our example of this, is that our Billing Task table "floats to the top" of a task. That is to say, it will always relate to the highest-level parent it can find (e.g., SCTASK - float -> RITM):
  

(function refineQuery(current, parent) {
	// For billing tasks, they should be related to the top-level task.
	// For example(s): 
	//	- SCTASK should show Billing Tasks for the RITM, not SCTASK
	//  - WOT should show Billing Tasks for WO, not WOT
	//  - etc.

Expand to see the full "query_with" script

Spoiler

(function refineQuery(current, parent) {
	// For billing tasks, they should be related to the top-level task.
	// For example(s): 
	//	- SCTASK should show Billing Tasks for the RITM, not SCTASK
	//  - WOT should show Billing Tasks for WO, not WOT
	//  - etc.

	const currentTable = parent.getValue('sys_class_name');
	
	const failOut = () => {
		current.addQuery('parent=anonexistentsys_id');
	};

	const addOTOParent = () => {
		current.addQuery('parent', parent.getValue('sys_id'));
	};
	
	switch (currentTable) {
		case 'sc_req_item':
			if (!parent.cat_item.billable) return failOut();
			current.addQuery('area', parent.cat_item.getRefRecord()?.u_area ?? '');
			//Intentional fall-through.
		case 'wm_order':
		case 'incident':
			addOTOParent();
			break;
		case 'sc_task':
			var ritmGr = gs.nil(parent.request_item) ? null : parent.request_item.getRefRecord();
			if (!ritmGr || !ritmGr.cat_item.billable) return failOut();
			current.addQuery('parent', ritmGr.getValue('sys_id'));
			current.addQuery('area', ritmGr.cat_item.getRefRecord()?.u_area ?? '');
			break;
		case 'wm_task':
			var woGr = gs.nil(parent.work_order) ? null : parent.work_order.getRefRecord();
			if (!woGr) return failOut();
			current.addQuery('parent', woGr.getValue('sys_id'));
			break;
		default:
			failOut();
			break;
	}
})(current, parent);

• Script variables available
  • parent - The GlideRecord of the table you are querying from. In our case, this would be a Task GlideRecord.
  • current - The GlideRecord of the table you are querying against. In our case, this would be a Billing Task GlideRecord.
    • Note that, despite this being a GlideRecord, ServiceNow "uses it" as a query.
    • You should solely use query-refining functions, e.g. addQuery()

"Query from" (query_from)

• Refining the table you are querying from (i.e., the records you will see in the related list).
• Set the answer variable to the name of the table to query from.
• If you want to show records from different tables depending on certain conditions, you will need to write a script to do so here.
  • Note that you cannot source data from more than one table per relationship, unless they extend a common parent, and you target said parent.
• Although, as stated above, we do not have a concrete example of a complex script for this case, it would follow the same pattern, and for simple cases, you would simply do something like this:

answer = 'x_riot_rit_cost_wd_billing_task';

 

"Apply to" (apply_to)

• Refining the table(s) where the Related List appears as an option.
• It's important to note that correctly configuring apply_to does not in itself make the Related List appear on the form. You will need to configure the table to add the Related List, once configured.
• Set the answer variable to true/false - True will allow the related list to appear, False will not.
• If you only want the related list to appear on certain tables, but want to define it on a shared parent, you can use this script to define that behavior.
• Our example here, is that only certain tables can have Billing Tasks created for them, so we only want the list to appear on those:

answer = (() => {
// ... some above code excluded for brevity.
	return [
		'sc_task',
		'sc_req_item',
		'incident',
		'wm_task',
		'wm_order',
	].includes(tableName); // Note tableName, versus table_name.
})();

• Script variables available
  • table_name- The name of the table that is currently being "checked" for whether or not the Relationship should apply.
    • ONLY for relationships in the global scope. See below section.

So, What's the Complication?

Notably, in our case, it stems from a seemingly out-of-left-field error, when attempting to use the table_name variable when in a Scoped Application.

If you came here from a search, you may have come across one (or multiple) of these, or similar posts:

• Advanced Relationship - by @Yashaswini3
• Variables available in the "Query from" script on the "sys_relationship" table? - by @bbarrowes
• Metrics Related List is only on INC records, how to add to other record types? - by @Mike Beardsley
• How does the Advanced Applies to field work on the related list form? - by @r_t_bryan
• Advanced Relationship Query - by @davidwilhelm-in
• How to create Scripted Relationships or Defined Related lists in ServiceNow - by @Mohit Kaushik

Some of these posts do not, but many do mention the table_name variable. So, what's up with that?
Looking at some relationships that ServiceNow provides OOB, you will see that table_name is used rather frequently, across the platform. Querying the sys_relationship table with:

advanced=true^apply_toLIKEtable_name

You will see some related lists you may recognize, or at least may have used before:

• Audit History - 366eda7b0a0004970a7d925c9258806b
• Updates - df364130c3110100bac1addbdfba8f78
• Variables - 366eda7b0a0004970a7d925c9258806b

All of these have in common the fact that they make use of the table_name variable in their Apply to script. So what's the catch? Well, let's try it ourselves; Creating our relationship record, with an Apply to script as follows:

answer = [
	'sc_task',
	'sc_req_item',
	'incident',
	'wm_task',
	'wm_order',
].includes(table_name);

If you now open the Configure -> Related Lists window, you will note the absense of your relationship in the list. Digging further into the logs, you will see that a Rhino error has occured:

com.glide.script.RhinoEcmaError: "table_name" is not defined.
<refname> : Line(7) column(0)
1. answer = [
2.	'sc_task',
3.	'sc_req_item',
4.	'incident',
5.	'wm_task',
6.	'wm_order',
==> 7. ].includes(table_name);

com.glide.script.RhinoEcmaError: "table_name" is not defined.

So why doesn't it work for Scoped Apps? Speculatively, my best guess is that there is a Security rule placed around slushbucket.do (where Related List selection occurs), that fully prevents a Scoped App from accessing this variable. Despite best efforts to insert cross-scope access for our custom app, we could not get this error to resolve. A case was opened with HI, and this was not labelled as a defect, so for now, we must rely on workarounds.

How do we get the Table Name?

Short answer? Painfully.
Long answer? We extract it from the URL parameters that slushbucket.do receives from ServiceNow, making use of the gs API. If we add the following debug line to our Apply to script:

// Note the use of gs.debug, instead of gs.log - Scoped Apps.
gs.debug('Relationship Apply to Glide URI: ' + gs.action.getGlideURI()); 

We are able to see the full, parameterized URI that we have access to inside of the script. For example:

slushbucket.do?sysparm_collection_relationship=&sysparm_form=related_list&sysparm_list=sc_task&sysparm_referring_url=sc_task.do%3fsys_id%3db7faeb2193d2ba10e150f5bd1dba103a%4099%40sysparm_record_rows%3d299656%4099%40sysparm_record_target%3dsc_task%4099%40sysparm_record_list%3dORDERBYDESCsys_updated_on%4099%40sysparm_record_row%3d1&sysparm_sys_id=undefined&sysparm_view=

Decoding this URI (decodeUriComponent()), gives us a much more human-readable and consumable string:

slushbucket.do?sysparm_collection_relationship=&sysparm_form=related_list&sysparm_list=sc_task&sysparm_referring_url=sc_task.do?sys_id=b7faeb2193d2ba10e150f5bd1dba103a@99@sysparm_record_rows=299656@99@sysparm_record_target=sc_task@99@sysparm_record_list=ORDERBYDESCsys_updated_on@99@sysparm_record_row=1&sysparm_sys_id=undefined&sysparm_view=

Splitting this further, by parameter, to view each:

slushbucket.do?
sysparm_collection_relationship=
sysparm_form=related_list
sysparm_list=sc_task
sysparm_referring_url=sc_task.do?sys_id=b7faeb2193d2ba10e150f5bd1dba103a@99@sysparm_record_rows=299656@99@sysparm_record_target=sc_task@99@sysparm_record_list=ORDERBYDESCsys_updated_on@99@sysparm_record_row=1&sysparm_sys_id=undefined&sysparm_view=

These URIs will (generally) follow a repeatable and predictable pattern, with a couple of the parameters being applicable to our problem here:

• sysparm_list will be the table name you are querying from
• sysparm_referring_url will (double-encodedly) also contain the table name you are querying from

Putting this together, and with the use of some carefully crafted Regex, we can handily extract the current table name, using the following code:

/**
 * Regex expressions that attempt to match known patterns where the referring table will appear.
 * This is not an exhaustive list, and may be "trickable" using URL params, however, this is not a massive concern.
 * 
 * Top -> Bottom, goes from "most trusted" to "least trusted".
 * 
 */
const tableExtractionRegexes = [
	/^.+&sysparm_list=([^&]+)(?:&?.+)/,
	/^.+sysparm_record_target=([^=@]+)(?:[=]?.+)/,
	/^.+sysparm_referring_url=([^\.]+)(?:\.do.+)/,
];

function getTableName(decodedUrl) {
	var tableResult = null;
	tableExtractionRegexes.forEach(regex => {
		if (!gs.nil(tableResult)) return;
		const regMatch = regex.exec(decodedUrl);
		if (regMatch && regMatch.length == 2) tableResult = regMatch[1];
	});
	return tableResult;
}

// Extract URL of current action
const rawUrl = gs.action.getGlideURI();
// If there is no URL at all/we can't fetch it, always false
if (gs.nil(rawUrl)) return false;

// Decode the URL, as regexes are written to work with decoded.
const decodedUrl = String(decodeURIComponent(rawUrl));

// Attempt to extract table name from URL
const tableName = getTableName(decodedUrl);

With this in place, we can now use the tableName variable in our script:

answer = (() => {
	// above code excluded for brevity...

	// Attempt to extract table name from URL
	const tableName = getTableName(decodedUrl);

	return [
		'sc_task',
		'sc_req_item',
		'incident',
		'wm_task',
		'wm_order',
	].includes(tableName);
})();

And you will see no more errors!

Going a step further, we can morph this into a re-usable code section, in a Script Includes (be sure to create it in the Global scope, if you do!):

Spoiler

Name: ScopedAppUtils
Accessible from: All application scopes
Script:

var ScopedAppUtils = Class.create();
ScopedAppUtils.prototype = {
    initialize: function() { },

	/**
	 * Meant to be called from the `apply_to` field of a sys_relationship record.
	 * @returns {string|null} Extractd table name, if found. Otherwise, null.
	 */
	getRelApplyToTableName: function () {
		// === Function/constant setup
		
		/**
		 * Regex expressions that attempt to match known patterns where the referring table will appear.
		 * This is not an exhaustive list, and may be "trickable" using URL params, however, this is not a massive concern.
		 * 
		 * Top -> Bottom, goes from "most trusted" to "least trusted".
		 */
		const tableExtractionRegexes = [
			/^.+&sysparm_list=([^&]+)(?:&?.+)/,
			/^.+sysparm_record_target=([^=@]+)(?:[=]?.+)/,
			/^.+sysparm_referring_url=([^\.]+)(?:\.do.+)/,
		];

		/**
		 * Fetches the table_name that is "trying" to use this Related List.
		 * In practice, this is the same as the `table_name` variable from ServiceNow, that seems to not work in Scoped Apps.
		 * 
		 * @returns {string|null} The table name, if it was able to be extracted, otherwise, null.
		 */
		const getTableName = (decodedUrl) => {
			var tableResult = null;
			tableExtractionRegexes.forEach(regex => {
				if (!gs.nil(tableResult)) return;
				const regMatch = regex.exec(decodedUrl);
				if (regMatch && regMatch.length == 2) tableResult = regMatch[1];
			});
			return tableResult;
		};

		// === Actual processing begins here.

		// Extract URL of current action
		const rawUrl = gs.action.getGlideURI();
		// If there is no URL at all/we can't fetch it, always false
		if (gs.nil(rawUrl)) return false;

		// Decode the URL, as regexes are written to work with decoded.
		const decodedUrl = String(decodeURIComponent(rawUrl));

		return getTableName(decodedUrl);
	},

    type: 'ScopedAppUtils'
};

And now we can call that, instead, making sure you deal with the potential of a null return value:

answer = (() => {
	const tableName = new global.ScopedAppUtils().getRelApplyToTableName();
	if (gs.nil(tableName)) return false;

	return [
		'sc_task',
		'sc_req_item',
		'incident',
		'wm_task',
		'wm_order',
	].includes(tableName);
})();

Wrapping Up

Hopefully this is helpful to others. The hardest part of most of this was the lack of substantial documentation, leading to developing in the dark. If there are any other pitfalls or catches to watch for, I'll try to keep this post updated.