Criteria VQL

Vault Query Language (VQL) is a SQL-like language which allows you to query information in Vault. This article provides detailed information for Vault Admins entering Criteria VQL when configuring:

  • Rule Criteria for Dynamic Access Control on Objects. Learn more in Vault Help.
  • Constrain Records in Referenced Object - Static. Learn more in Vault Help.
  • Constrain Records in Referenced Object - Dynamic. Learn more in Vault Help.

The image below shows the Dynamic Security Rule Criteria in the Vault UI.

Dynamic Security on Objects: VQL Rule Criteria

Use the "token" button to the right of the Criteria VQL text box to search for available tokens on an object. String field values are case-sensitive.

Dynamic Access Control & Static Reference Constraints

The following applies to static constraints; Dynamic Reference Constraints use tokens.

Rules for DAC and Static Reference Constraints use the same criteria. The sections below explain the available fields. Note that nested expressions are not allowed, i.e., Join relationships.

ID Fields

Object record IDs are system-managed fields used in the API and not visible in the Vault UI. If you know the object record ID, you can use it to identify the object record. The object record name, however, serves the same purpose.

Object Object Record Field Name Field Value (example) Rule Criteria Entry
Product CholeCap id 1357663087386 id = 1357663087386
Study VVT485-301 id 1357752641846 id = 1357752641846

We recommend using object record name fields and lookup fields to identify your object records. Examples are provided below.

Text (String) Fields

Enter text field value labels as shown in the object record details (capitals, spaces, special characters, etc.) and enclose all values in single-quotes. These are case-sensitive ("Cholecap" does not equal "CholeCap").

Here are some examples of commonly used criteria:

Object Label Field Label Field Name Field Value (example) Rule Criteria Entry
Product Product Name name__v CholeCap name__v = 'CholeCap'
Country Country Name name__v United States name__v = 'United States'
Study Study Number name__v VVT485-301 name__v = 'VVT485-301'
Study Study Name study_name__vs Cholecap Efficacy Trial study_name__vs = 'Cholecap Efficacy Trial'

Picklist Fields

Do not enter picklist field value labels as shown in the object record details. You must use the picklist value name enclosed in single-quotes. For example, the "Therapeutic Area" picklist field has the picklist value label "Hematology" and the picklist value name "hematology__vs". To find picklist value names, go to Business Admin > Picklists.

Here are some examples of commonly used criteria:

Object Label Field Label Field Name Field Value (example) Rule Criteria Entry
Product Therapeutic Area therapeutic_area__vs hematology__vs therapeutic_area__vs = 'hematology__vs'
Product Product Family product_family__vs wonderdrug_family__c product_family__vs = 'wonderdrug_family__c'
Study Study Type study_type__v safety__vs study_type__v = 'safety__vs'
Study Study Phase study_phase__v phase3__vs study_phase__v = 'phase3__vs'

Object Lookup Fields

Many object records have relationships with other object records. For example, the object record details for study number "VVT485-301" shows that it is associated with the product "CholeCap". When looking at fields configured on a particular object, these have the data type "Object" with the object type in parentheses. For example, on the Study object includes the field name "product__v".

Assume you're configuring rule criteria on the Study object and want to filter on the product named "CholeCap". You cannot enter name__v = 'CholeCap' because the name__v field applies to the Study. If you knew the product ID, you could enter id = '1357663087386'. However, this is most easily achieved by using an "object lookup field" in the form product__vr.name__v = 'Cholecap'. By adding __vr to the product name and using dot-notation to combine it with a product object field, Vault allows you to traverse the relationship between the two objects. You can apply this method to any Vault object.

  • For standard objects (vault_object__v), the lookup name is vault_object__vr.name__v.
  • For custom objects (custom_object__c), the lookup name is custom_object__cr.name__v.

Here are some examples of commonly used criteria:

Object Label Field Label Field Lookup Name Field Lookup Value (example) Rule Criteria Entry
Study Product product__v Wonderdrug product__vr.name__v = 'WonderDrug'
Study Site Study study_number__v VVT485-301 study_number__vr.name__v = 'VVT485-301'
Study Site Study Location location__v UCSF Medical Center location__vr.name__v = 'UCSF Medical Center'
Study Site Study Country study_country__v United States study_country__vr.name__v = 'United States'
Location Country country__v United States country__vr.name__v = 'United States'
Study Country Study Number study_number__v VVT485-301 study_number__vr.name__v = 'VVT485-301'

Note: As a best practice, we recommend using the "token" button to the right of the Criteria VQL text box to search for available object lookup fields.

Date & DateTime Fields

All Dates and DateTimes are entered and returned in UTC (Coordinated Universal Time) and not the user's time zone.

  • Dates formats are YYYY-MM-DD ('2014-12-20')
  • DateTime formats are YYYY-MM-DD'T'HH:MM:SS.SSS'Z' ('2014-12-20T15:30:00.000Z')

Here are some examples of commonly used criteria:

Object Label Field Label Field Name Field Value (example) Rule Criteria Entry
Product Created Date created_date__v 2014-12-20T15:30:00.000Z created_date__v != '2014-12-20T15:30:00.000Z'
Study Start Date study_start_date__vs 2014-12-20 study_start_date__vs >= '2014-12-20'

Numeric Fields

Numeric fields are always used with comparison operators (=, !=, <, >, etc.). You do not need to enclose the field value in single- or double-quotes.

Here are some examples using numeric fields as rule criteria:

Object Label Field Label Field Name Field Value (example) Rule Criteria Entry
Study Enrollment enrollment__vs 5000 enrollment__vs < 5000
Publication Distribution distribution__c 200 distribution__v >= 200

Boolean Fields

Boolean fields have only two possible values: true or false. In Vault Admin, these are referred to as "Yes/No" fields. You do not need to enclose the field value in single- or double-quotes.

Here are some examples using Boolean fields as rule criteria:

Object Label Field Label Field Name Field Value Rule Criteria Entry
Publication Approved approved__c true approved__c = true
Publication Approved approved__c false approved__c = false

Dynamic Reference Constraints

The following applies to dynamic constraints, static reference constraints are defined the same way as DAC.

Criteria VQL for Dynamic Reference Constraints must contain a valid field value token instead of a static field value. Tokens are in the format {{this.field__name}} .

Here are some examples using Dynamic Reference Constraint criteria:

Referring Object Field to Constrain (Controlled Field) Controlling Field Criteria VQL Entry
Brand Country Region region__c = {{this.region__c}}
Brand Product Country > Region region__c = {{this.country_cr.region__c}}
Application* Product Therapeutic Area id in (SELECT id FROM
therapeutic_area_product_join__cr WHERE
therapeutic_area__c = {{this.therapeutic_area__c}}

*In this example, Product and Therapeutic Area are a M:M (Many-to-Many) relationship.

VQL Operators

Comparison Operators

VQL supports the following comparison operators: = (equal to), != (not equal to), < (less than), > (greater than), <= (less than or equal to), and >= (greater than or equal to).

status__v = 'active__v'
study_status__v != 'Not Started'
created_date__v > '2014-12-20'

Logical Operators

The AND operator returns results if the first and second expression are both true.

therapeutic_area__vs = 'cardiology__vs' AND therapeutic_area__vs = 'hematology__vs' 

The OR operator returns results if either expression is true.

therapeutic_area__vs = 'cardiology__vs' OR therapeutic_area__vs = 'hematology__vs' 

Parentheses can be used to enclose searches.

therapeutic_area__vs = 'neurology__vs' AND (therapeutic_area__vs = 'cardiology__vs' OR therapeutic_area__vs = 'hematology__vs')

The CONTAINS operator is used with parentheses to enclose multiple values.

therapeutic_area__vs CONTAINS ('hematology__vs','cardiology__vs')

The BETWEEN operator is used with AND to compare data between two values.

created_date__v BETWEEN '2014-10-15' AND '2014-04-20'

The following logical operators are not supported: NOT, AND NOT, OR NOT, IN.