Hi dev,

Have you ever wondered how the tables you are customizing relate to one another?

How do these tables connect to one another?

In this tutorial, we are going to visually see these connections using a little bit of javascript and a website called plantuml.

Run the following code in servicenow, preferably in xplore::

function mapTables(arrTablesToCheck) {

// STATE VARIABLES var arrTablesChecked = []; var arrTablesRelationships = [];

var arrTablesToCheck = arrTablesToCheck

function getExtendedTables(strTableName) { // returns the table which the input table is extending // ex: if you input incident, you will see task as an output table return new TableUtils(strTableName).getTables().toArray();

}

function getReferenceTablesName(inputTable) {

// get the reference fields from input table, includeding the extended table

var arrExtendedTables = getExtendedTables(inputTable);

var arrColumnLabel = [];

for (val in arrExtendedTables) { var gliSD = new GlideAggregate('sys_dictionary'); gliSD.addQuery('name', arrExtendedTables[val]); gliSD.addQuery('internal_type.label', 'Reference'); //gliSD.addQuery('reference', '!=', arrExtendedTables[val]); gliSD.query(); while (gliSD.next()) { var obj = {}; obj['table_name'] = inputTable; obj['reference'] = gliSD.getValue('reference'); obj['column_label'] = gliSD.getValue('column_label'); obj['extended_table'] = arrExtendedTables[val] == inputTable ? '' : arrExtendedTables[val]; arrColumnLabel.push(obj); } } return arrColumnLabel

}

function checkItemInArray(arr, item) { // insert array and item // if item is not in array return false for (var i in arr) { if (JSON.stringify(arr[i]) === JSON.stringify(item)) { return true } } return false

}

function add_arrTablesToCheck(data) { // only add to arrTablesToCheck if the values does not exist in arrTablesChecked for (val in arrTablesChecked) { if (JSON.stringify(arrTablesChecked[val]) == JSON.stringify(data)) return } arrTablesToCheck.push(data);

}

function addToArrayIfUnique(arr, val) { // insert array and value // if val is not in array, add to array for (var i in arr) { if (JSON.stringify(arr[i]) === JSON.stringify(val)) { return } } arr.push(val);

}

function removeFromArray(arr, val) { if (arr.indexOf(val) != -1) { arr.splice(arr.indexOf(val), 1); }

}

function main() {

//Given a table, map all the reference fields

while (arrTablesToCheck.length != 0) {

var strTargetTable = arrTablesToCheck[0];

// register the table in arrTablesChecked addToArrayIfUnique(arrTablesChecked, strTargetTable); // remove table from tbles to check in arrTablesToCheck

removeFromArray(arrTablesToCheck, strTargetTable);

// get tables to check from target table

var arrTableFieldData = getReferenceTablesName(strTargetTable);

// store relationship for (i in arrTableFieldData) { var objTableField = arrTableFieldData[i]; arrTablesRelationships.push(objTableField);

}

//register new data to analyze for (i in arrTableFieldData) { var objTableField = arrTableFieldData[i]; var booCondition = !checkItemInArray(arrTablesChecked, objTableField['reference']); if (booCondition) addToArrayIfUnique(arrTablesToCheck, objTableField['reference']); } } } main() return arrTablesRelationships

}

function createPlantUML(arrTableRelationships) {

/* outputs a giant string that has the format for creating the diagram

*/

var strTableRelationships = '\n'; var strConnector = ' --|> '; var strExtendo = ' ..> '; for (i in arrTableRelationships) { var strTableName = arrTableRelationships[i]['table_name']; var strReference = arrTableRelationships[i]['reference']; var strExtendedTable = arrTableRelationships[i]['extended_table']; var strColumnLabel = arrTableRelationships[i]['column_label']; if (arrTableRelationships[i]['extended_table'] != '') { strTableRelationships = strTableRelationships + strTableName + strExtendo + strReference + " : " + strExtendedTable + " from field '" + strColumnLabel + "'\n"; } else { strTableRelationships = strTableRelationships + strTableName + strConnector + strReference + ": from field '" + strColumnLabel + "'\n";

}

} return strTableRelationships

}

var arrTablesRelationships = mapTables(['incident']); // INSERT TABLES OF INTEREST

createPlantUML(arrTablesRelationships); // RETURNS GIANT STRING TO USE IN https://www.plantuml.com/plantuml

Once you run the code, Xplore should output a giant string that we need to copy.

Open https://www.plantuml.com/plantuml.

replace text with the following, where ** is where you will paste the giant string copied earlier

@startuml**

@enduml

the mapping should occur underneath the input box.

if there are too many mappings, the website will crash lol. So proceed with caution

How to interpret these diagrams you are probably wondering?

to understand the diagram, I will look at some of the data that is produced by running the code against the incident table.

As you can see, we have two types of connections, here is an example of each::

incident ..> sys_user : task from field 'Closed by'incident --|> sys_user: from field 'Caller'

If the connection is built with the following "..>", it means that the field from which the reference is created came from a table that is being extended. In this case, the field 'Closed by' came from the 'task' table.

The field 'Caller' was on the 'incident' table, so it's built with the following "..>".

Labels: