Remote tables and the mobile configuration

At some point of your career you probably heard of remote tables. Their primary usage, according to the documentation, is to connect with external 3rd-party systems and retrieve some data. But we can be much more creative with them. Jace Benson has recently shown how you could use them as a choice table, and I will show you how (and why sometimes it is the only way) you could use them for the mobile configuration.

Mobile config and data

Everything is about data, and so is mobile configuration in ServiceNow. Eventually you will want to display a list of tasks from multiple tables with single filter category. Or, like my case, a record screen showing SCTASK with its variables.

The task itself was pretty simple, I just had to connect couple of tables and display it. Easy to do with a script, could also be done with a database view. But none of these options is viable for a mobile configuration – data available there can only be taken from tables. Including remote tables. If you ever worked with mobile configuration, you may have noticed that in OOB examples (e.g. catalog browser function) data is taken from unusual tables – v_st_sc_category or v_st_sc_catalog. You will find their definition in sys_db_object, they have ACLs, you can also add index – but when you list their content, you will most likely be stumbled. Sometimes it’s empty, sometimes it has 10 rows, sometimes 5. You can imagine how I felt when I first encountered it…

Remote tables definition

When searching for remote tables in the navigator, you have two links – Tables and Definitions. Table is a list of sys_db_object records having the field Remote Table set to true – so it’s a table structure. It is tigthly connected with the Definitions link – it displays the definitions of these tables. Now, what are these definitions? It’s a script that fills the given table structure.

The definition script takes two parameters – v_table and v_query. You may do what you want within the script, but:

• You must use v_table.addRow(Object) to fill you remote table with data
• You should also use v_query.getParameter(remote table field) to add data filtering

Of course it depends on your use case, but I do recommend using the filtered script, because

• A remote table can display up to 1000 records
• Since it’s a script, loading the data can take time and you don’t want your users to wait

Let me share with you an example of my case – an SCTASK with its variables (name and value) :

(function executeQuery(v_table, v_query) {
	const scTaskSysId = v_query.getParameter('catalog_task');

	if (scTaskSysId){
		let id = 0;
		new global.GlideQuery('sc_task')
			.where('active', true)
			.where('sys_id', scTaskSysId)
			.select('assignment_group', 'assigned_to', 'number', 'request_item')
			.forEach((task) => {
				new global.GlideQuery('sc_item_variables_task')
					.where('task', task.sys_id)
					.select('variable')
					.forEach((taskVariable) => {
						new global.GlideQuery('sc_item_option_mtom')
							.where('request_item', task.request_item)
							.where('sc_item_option.item_option_new', taskVariable.variable)
							.select('sc_item_option.value')
							.forEach((variableValue) => {
								const row = {
									sys_id: 'SCTASKVARVALUE' + (id++),
									number: task.number,
									catalog_task: task.sys_id,
									assignment_group: task.assignment_group,
									assigned_to: task.assigned_to,
									variable: taskVariable.variable,
									value: getVariableDisplayValue(variableValue.sc_item_option.value, taskVariable.variable)
								};
								v_table.addRow(row);
							});
					});
			});
	}

	function getVariableDisplayValue(value, variable){
		let ret = value;
		const varRecord = new global.GlideQuery('item_option_new')
			.get(variable, ['type', 'reference', 'list_table'])
			.orElse({
				type: 'unknown',
				reference: '',
				list_table: ''
			});
		
		if (varRecord.type === '7') { // checkbox
			ret = value == 'true' ? 'Yes' : 'No';
		}

		if (varRecord.type === '8') { // reference type
			const tempGR = new GlideRecord(varRecord.reference);
			tempGR.get(value);
			ret = tempGR.getDisplayValue();
		}

		if (varRecord.type === '21') { // list collector
			let tmpRet = [];
			const tempGR = new GlideRecord(varRecord.list_table);
			tempGR.addQuery('sys_id', 'LIKE', variable);
			tempGR.query();
			while (tempGR.next()){
				tmpRet.push(tempGR.getDisplayValue());
			}
			ret = tmpRet.join(',');
		}

		if (varRecord.type === '31') { // requested for
			const tempGR = new GlideRecord('sys_user');
			tempGR.get(value);
			ret = tempGR.getDisplayValue();
		}
		return ret;
	}
})(v_table, v_query);

As you can see, it’s not overly complicated. Interesting lines:

• 2: initial filtering to speed up the data retrieval and make sure that nothing else is in the table when it is being queried. That’s another interesting feature – in this case, no data will be displayed when you list the table’s content unless you provide an SCTASK sys_id
• 21: as you can read in the final note here, you must map a value to the sys_id field
• 29: after assigning values to each field in your table, make sure to add a data row using v_table.addRow(Object)

Conclusion

Remote tables is a cool feature. It is even cooler when you know that it is exempt from table licensing:

You also have to remember about retrieving data from external source – even though remote tables themselves are exempt from custom table licensing, the IntegrationHub transaction limits are still valid: