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 "..>".
