Vault Queries for Data Tables
Vault Queries
When you create Custom JavaScript columns, you can check if an object exists in the Vault using pne of the the custom JavaScript functions below.
vaultObjectExists
Return value: Boolean
Table 1.
| Argument Name | Required | Data Type |
|---|---|---|
| System Name | True | String |
| Table Name | True | String |
| Lookup Value | True | String |
This function checks if the keyed column in the given system and table contains a row whose primary key matches the specified lookup value. It returns a Boolean value. See Keys.
Example Code:
return vaultObjectExists('hr500','employees','10023963');
The example code above looks for a record in the "employees" table of the "hr500" system with a primary key value of "10023963". If the record exists, a Boolean value of true is returned. Otherwise, the function returns false.
This function is useful in scripts that need to base a decision on whether an object exists or not. For example, when provisioning users into an Active Directory system, you can use this function to generate a column returns the user's target OU' path if it exists, or returns a default OU path if it does not.
vaultObjectExistsByRef
Return value: Boolean
Table 2.
| Argument Name | Required | Data Type |
|---|---|---|
| System Name | True | String |
| Table Name | True | String |
| Reference Column | True | String |
| Lookup Value | True | String |
This function checks the given system and table for a row that matches the specified lookup value inside the specified reference column. It returns a Boolean value. See Keys.
Example Code:
return vaultObjectExistsByRef('hr500','employees','ssn','999-888-7777');
The example code above looks for a record in the "employees" table of the "hr500" system for an "ssn" value of "999-888-7777". If the record exists, a Boolean value of true is returned. Otherwise, the function returns false.
Much like vaultObjectExists, this function is useful in scripts that need to base decisions on whether or not a record exists in the vault. This function differs, however, in that the reference column does not need to be a primary key of the table. It does, however, need to be marked as a Reference column in the table's properties.
vaultObjectFind
Return value: any[]
Table 3.
| Argument Name | Required | Data Type |
|---|---|---|
| System Name | True | String |
| Table Name | True | String |
| Reference Column | True | String |
| Lookup Value | True | String |
| Case Sensitive Flag | False | Boolean |
This function checks the given system and table for a row that matches the specified lookup value inside the specified reference column. It returns an object containing the found record(s). If no record is found, the object is empty.
Example Code:
let obj = vaultObjectFind('hr500','employees','ssn','999-888-7777');
return obj[0]['FirstName'];
The example code above looks for a record in the "employees" table of the "hr500" system for an "ssn" value of "999-888-7777". The record is then stored in the obj variable, which contains an array of objects matching the supplied arguments. The script then returns the "FirstName" field of the first object in the array.
This function is computationally expensive, and its use should be avoided in production. However, it may be useful during development, or in particular edge-cases.