Advanced Script Examples

Participant Lookup

ITSM Example

/****************************************************************************************************************************
* ServiceNow ITSM Participant Lookup
* Searches in two tables. The tables are user and company
****************************************************************************************************************************/
/* Change the searchTables if needed, there is no need to change anything else */
var searchTables = [
  {
    table: 'sys_user',
    searchQuery: 'phoneENDSWITH' + workitem.ani + '^ORhome_phoneENDSWITH' + workitem.ani + 
    '^ORmobile_phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'title',
    companyField: 'company'
  },
  {
    table: 'core_company',
    searchQuery: 'phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'city',
    companyField: 'state'
  }
];

/****************************************************************************************************************************
* The function "getParticipantLookupResult" is used to query the actual tables in ServiceNow and then 
* return the results as an array of objects.
****************************************************************************************************************************/
var getParticipantLookupResult = function(table, searchQuery, nameField, roleField, companyField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      Name: nameField ? query.getDisplayValue(nameField) : '',
      Role: roleField ? query.getDisplayValue(roleField): '',
      Company: companyField ? query.getDisplayValue(companyField): ''
    });
  }
  return resultArray;
};

var r = [];

if (workitem.ani) {
  for (var tIndex in searchTables) {
    var tbl = searchTables[tIndex];
    r = r.concat(getParticipantLookupResult(tbl.table, tbl.searchQuery, tbl.nameField, tbl.roleField, tbl.companyField));
  }
}

result = r;

CSM Examples

CSM Example 1

/****************************************************************************************************************************
* ServiceNow CSM Participant Lookup
* Searches in three tables. The tables are customer, consumer and account
****************************************************************************************************************************/
/* Change the searchTables if needed, there is no need to change anything else */
var searchTables = [
  {
    table: 'customer_contact',
    searchQuery: 'phoneENDSWITH' + workitem.ani + '^ORmobile_phoneENDSWITH' + workitem.ani + 
    '^ORhome_phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'title',
    companyField: 'account'
  },
  {
    table: 'csm_consumer',
    searchQuery: 'business_phoneENDSWITH' + workitem.ani + '^ORmobile_phoneENDSWITH' + workitem.ani + 
    '^ORhome_phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'prefix',
    companyField: 'state'
  },
  {
    table: 'customer_account',
    searchQuery: 'phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'city',
    companyField: 'state'
  }
];

/****************************************************************************************************************************
* The function "getParticipantLookupResult" is used to query the actual tables in ServiceNow and then 
* return the results as an array of objects.
****************************************************************************************************************************/
var getParticipantLookupResult = function(table, searchQuery, nameField, roleField, companyField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      Name: nameField ? query.getDisplayValue(nameField) : '',
      Role: roleField ? query.getDisplayValue(roleField): '',
      Company: companyField ? query.getDisplayValue(companyField): ''
    });
  }
  return resultArray;
};

var r = [];

if (workitem.ani) {
  for (var tIndex in searchTables) {
    var tbl = searchTables[tIndex];
    r = r.concat(getParticipantLookupResult(tbl.table, tbl.searchQuery, tbl.nameField, tbl.roleField, tbl.companyField));
  }
}

result = r;

CSM Example 2

/****************************************************************************************************************************
* ServiceNow CSM Participant Lookup
* Searches for the case number, if this is empty it will search in the three tables. The tables are customer, consumer and account.
****************************************************************************************************************************/
/* Change the searchFirstInTable and searchTables if needed, there is no need to change anything else */
var searchFirstInTable = function() {
  var table = 'sn_customerservice_case';
  var searchQuery = 'number=' + workitem['callVariable7'] + '^ORDERBYnumber';
  var nameField = 'number';
  var roleField = 'short_description';
  var companyField = 'urgency';
  return getParticipantLookupResult(table, searchQuery, nameField, roleField, companyField);
};
var searchTables = [
  {
    table: 'customer_contact',
    searchQuery: 'phoneENDSWITH' + workitem.ani + '^ORmobile_phoneENDSWITH' + workitem.ani + 
    '^ORhome_phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'title',
    companyField: 'account'
  },
  {
    table: 'csm_consumer',
    searchQuery: 'business_phoneENDSWITH' + workitem.ani + '^ORmobile_phoneENDSWITH' + workitem.ani + 
    '^ORhome_phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'prefix',
    companyField: 'state'
  },
  {
    table: 'customer_account',
    searchQuery: 'phoneENDSWITH' + workitem.ani + '^ORDERBYname',
    nameField: 'name',
    roleField: 'city',
    companyField: 'state'
  }
];

/****************************************************************************************************************************
* The function "getParticipantLookupResult" is used to query the actual tables in ServiceNow and then 
* returns the results as an array of objects.
****************************************************************************************************************************/
var getParticipantLookupResult = function(table, searchQuery, nameField, roleField, companyField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      Name: nameField ? query.getDisplayValue(nameField) : '',
      Role: roleField ? query.getDisplayValue(roleField): '',
      Company: companyField ? query.getDisplayValue(companyField): ''
    });
  }
  return resultArray;
};

result = searchFirstInTable();

if(result.length === 0) {
  var r = [];

  if (workitem.ani) {
    for (var tIndex in searchTables) {
      var tbl = searchTables[tIndex];
      r = r.concat(getParticipantLookupResult(tbl.table, tbl.searchQuery, tbl.nameField, tbl.roleField, tbl.companyField));
    }
  }

  result = r;
}

Directory Lookup

ITSM Example

/****************************************************************************************************************************
* ServiceNow ITSM Directory Lookup
* Searches in two tables. The tables are user and company
****************************************************************************************************************************/
/* Change the searchTables if needed, there is no need to change anything else */
var searchTables = [
  {
    table: 'sys_user',
    searchQuery: 'nameLIKE' + search + '^ORcompanyLIKE' + search + '^ORmobile_phoneLIKE' + search + 
    '^ORphoneLIKE' + search + '^ORhome_phoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'company',
    homePhoneField: 'home_phone',
    mobilePhoneField: 'mobile_phone',
    phoneField: 'phone',
    businessMobileField: ''
  },
  {
    table: 'core_company',
    searchQuery: 'nameLIKE' + search + '^ORcityLIKE' + search + '^ORphoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'city',
    homePhoneField: '',
    mobilePhoneField: '',
    phoneField: 'phone',
    businessMobileField: ''
  },
];

/****************************************************************************************************************************
* The function "getDirectoryLookupResult" is used to query the actual tables in ServiceNow and then 
* returns the results as an array of objects.
****************************************************************************************************************************/
var getDirectoryLookupResult = function(table, searchQuery, nameField, companyField, homePhoneField, mobilePhoneField, phoneField, businessMobileField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      Name: nameField ? query.getDisplayValue(nameField) : '',
      Company: companyField ? query.getDisplayValue(companyField) : '',
      Home: homePhoneField ? query.getDisplayValue(homePhoneField) : '',
      Mobile: mobilePhoneField ? query.getDisplayValue(mobilePhoneField) : '',
      Work: phoneField ? query.getDisplayValue(phoneField) : '',
      BusinessMobile: businessMobileField ? query.getDisplayValue(businessMobileField) : '' 
    });
  }
  return resultArray;
};

var r = [];

for (var tIndex in searchTables) {
  var tbl = searchTables[tIndex];
  r = r.concat(getDirectoryLookupResult(tbl.table, tbl.searchQuery, tbl.nameField, tbl.companyField, tbl.homePhoneField, tbl.mobilePhoneField, tbl.phoneField, tbl.businessMobileField));
}

result = r;

CSM Example

/****************************************************************************************************************************
* ServiceNow CSM Directory Lookup
* Searches in three tables. The tables are customer, consumer and account
****************************************************************************************************************************/
/* Change the searchTables if needed, there is no need to change anything else */
var searchTables = [
  {
    table: 'customer_contact',
    searchQuery: 'nameLIKE' + search + '^ORaccountLIKE' + search + '^ORphoneLIKE' + search + 
    '^ORmobile_phoneLIKE' + search + '^ORhome_phoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'account',
    homePhoneField: 'home_phone',
    mobilePhoneField: 'mobile_phone',
    phoneField: 'phone',
    businessMobileField: ''
  },
  {
    table: 'csm_consumer',
    searchQuery: 'nameLIKE' + search + '^ORstateLIKE' + search + '^ORbusiness_phoneLIKE' + search + 
    '^ORmobile_phoneLIKE' + search + '^ORhome_phoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'state',
    homePhoneField: 'home_phone',
    mobilePhoneField: 'mobile_phone',
    phoneField: 'business_phone',
    businessMobileField: ''
  },
  {
    table: 'customer_account',
    searchQuery: 'nameLIKE' + search + '^ORstateLIKE' + search + '^ORphoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'state',
    homePhoneField: '',
    mobilePhoneField: '',
    phoneField: 'phone',
    businessMobileField: ''
  }
];

/****************************************************************************************************************************
* The function "getDirectoryLookupResult" is used to query the actual tables in ServiceNow and then 
* return the results as an array of objects.
****************************************************************************************************************************/
var getDirectoryLookupResult = function(table, searchQuery, nameField, companyField, homePhoneField, mobilePhoneField, phoneField, businessMobileField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      Name: nameField ? query.getDisplayValue(nameField) : '',
      Company: companyField ? query.getDisplayValue(companyField) : '',
      Home: homePhoneField ? query.getDisplayValue(homePhoneField) : '',
      Mobile: mobilePhoneField ? query.getDisplayValue(mobilePhoneField) : '',
      Work: phoneField ? query.getDisplayValue(phoneField) : '',
      BusinessMobile: businessMobileField ? query.getDisplayValue(businessMobileField) : '' 
    });
  }
  return resultArray;
};

var r = [];

for (var tIndex in searchTables) {
  var tbl = searchTables[tIndex];
  r = r.concat(getDirectoryLookupResult(tbl.table, tbl.searchQuery, tbl.nameField, tbl.companyField, tbl.homePhoneField, tbl.mobilePhoneField, tbl.phoneField, tbl.businessMobileField));
}

result = r;

Directory Numbers Example

/****************************************************************************************************************************
* ServiceNow ITSM Directory Lookup with Directory Numbers
* Searches in the two tables. The tables are sys_user and directory number
****************************************************************************************************************************/
/* Change the searchTables if needed, there is no need to change anything else */
var searchTables = [
  {
    table: 'sys_user',
    searchQuery: 'nameLIKE' + search + '^ORcompanyLIKE' + search + '^ORmobile_phoneLIKE' + search + 
    '^ORphoneLIKE' + search + '^ORhome_phoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'company',
    homePhoneField: 'home_phone',
    mobilePhoneField: 'mobile_phone',
    phoneField: 'phone',
    businessMobileField: ''
  },
  {
    table: 'x_busag_cnx_directory_number',
    searchQuery: 'service_layout_listLIKE' + serviceLayout + '^nameLIKE' + search + '^ORtypeLIKE' + search + 
    '^ORmobile_phoneLIKE' + search + '^ORphoneLIKE' + search + '^ORhome_phoneLIKE' + search + '^ORDERBYname',
    nameField: 'name',
    companyField: 'type',
    homePhoneField: 'home_phone',
    mobilePhoneField: 'mobile_phone',
    phoneField: 'phone',
    businessMobileField: ''
  }
];

/****************************************************************************************************************************
* The function "getDirectoryLookupResult" is used to query the actual tables in ServiceNow and then 
* return the results as an array of objects.
****************************************************************************************************************************/
var getDirectoryLookupResult = function(table, searchQuery, nameField, companyField, homePhoneField, mobilePhoneField, phoneField, businessMobileField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      Name: nameField ? query.getDisplayValue(nameField) : '',
      Company: companyField ? query.getDisplayValue(companyField) : '',
      Home: homePhoneField ? query.getDisplayValue(homePhoneField) : '',
      Mobile: mobilePhoneField ? query.getDisplayValue(mobilePhoneField) : '',
      Work: phoneField ? query.getDisplayValue(phoneField) : '',
      BusinessMobile: businessMobileField ? query.getDisplayValue(businessMobileField) : '' 
    });
  }
  return resultArray;
};

var r = [];

for (var tIndex in searchTables) {
  var tbl = searchTables[tIndex];
  r = r.concat(getDirectoryLookupResult(tbl.table, tbl.searchQuery, tbl.nameField, tbl.companyField, tbl.homePhoneField, tbl.mobilePhoneField, tbl.phoneField, tbl.businessMobileField));
}

result = r;

Record Lookup

ITSM Example

/****************************************************************************************************************************
* ServiceNow ITSM Record Lookup
* Searches in the two tables. The tables are incident and request
****************************************************************************************************************************/
/* Change the searchTables if needed, there is no need to change anything else */
var searchTables = [
  {
    table: 'incident',
    searchQuery: 'caller_id=' + workitem.participantId + '^active=true^ORDERBYopened_at',
    baseTable: 'task',
    titleField: 'number',
    descriptionField: 'short_description'
  },
  {
    table: 'sc_request',
    searchQuery: 'requested_for=' + workitem.participantId + '^active=true^ORDERBYopened_at',
    baseTable: 'task',      
    titleField: 'number',
    descriptionField: 'short_description'
  }
];

/****************************************************************************************************************************
* The function "getLookupResult" is used to query the actual tables in ServiceNow and then 
* return the results as an array of objects.
****************************************************************************************************************************/
var getLookupResult = function(table, searchQuery, baseTable, titleField, descriptionField) {
  var resultArray = [];
  var query = new GlideRecordSecure(table);
  query.addEncodedQuery(searchQuery);
  query.query();
  while(query.next()) {
    resultArray.push({
      Id: query.getUniqueValue(),
      Table: table,
      BaseTable: baseTable,
      Title: titleField ? query.getDisplayValue(titleField) : '',
      Description: descriptionField ? query.getDisplayValue(descriptionField) : ''
    });
  }
  return resultArray;
};

var r = [];

for (var tIndex in searchTables) {
  var tbl = searchTables[tIndex];
  r = r.concat(getLookupResult(tbl.table, tbl.searchQuery, tbl.baseTable, tbl.titleField, tbl.descriptionField));
}

result = r;