# Criteria VQL

Vault Query Language (VQL) is a SQL-like language which allows you to query information in Vault. Criteria VQL is a slimmed-down version of VQL used in the Vault UI. This article provides detailed information for Admins on using Criteria VQL to configure:

* [Rule criteria](/en/lr/25494/) for custom sharing rules on objects.
* [Static reference constraints](/en/lr/75340/#static-reference-constraints) to restrict records available for selection.
* [Dynamic reference constraints](/en/lr/75340/#dynamic-reference-constraints) to restrict records available for selection.
* [Filter criteria](/en/lr/62154/#create-query-rule) on query object rules.
* Criteria VQL in [related object](/en/lr/26387/#related-object) or [document](/en/lr/26387/#related-documents) sections.

The following image shows a _Criteria VQL_ input field for creating custom sharing rules in the Vault UI:

<a href="https://platform.veevavault.help/assets/images/platform-criteria-vql-1.png" data-lightbox="platform-criteria-vql-1.png" data-title="" data-alt="Criteria VQL input field">
  <img class="docimage" src="https://platform.veevavault.help/assets/images/platform-criteria-vql-1.png" alt="Criteria VQL input field" style="max-width: 500px;"  />
</a>

Click **Token Helper** (<img class="inline" src="https://platform.veevavault.help/assets/images/platform_criteriaVQL_tokenhelper.png" alt="Token Helper button" style="" />) to the right of the _Criteria VQL_ field to search for available tokens on an object. String field values are case-sensitive.

Use a backslash (`\`) to escape <a class="external-link " href="https://developer.veevavault.com/vql/#Special_Characters" target="_blank" rel="noopener">special characters<i class="fa fa-external-link" aria-hidden="true"></i></a> in VQL.

## Dynamic Access Control & Static Reference Constraints {#static-reference-constraints}

The following applies to static constraints; [dynamic reference constraints][2] use tokens.

Rules for dynamic access control and static reference constraints use the same criteria. The sections below explain the available fields.

Nested expressions (join relationships) are not allowed. Additionally, `OR` is not supported for constraints on documents.

### ID Fields

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

| Object | Object Record | Field Name | Field Value (example) | Rule Criteria Entry |
| --- | --- | --- | --- | --- |
| Product | CholeCap | `id` | `ABC000000001001` | `id = ABC000000001001` |
| Study | VVT485-301 | `id` | `DEF000000001002` | `id = DEF000000001002` |

We recommend using object record name fields and [lookup fields][4] to identify your object records.

### Text (String) Fields

Enter text field value labels as shown in the object record details (capitals, spaces, special characters, and so on) and enclose all values in single quotes (`'`). These are case-sensitive (meaning _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

When querying picklists, the behavior varies slightly between documents, objects, and workflows.

#### Workflows

To query workflow picklists, use the picklist value label enclosed in single quotes (`'`). For example, the _Therapeutic Area_ picklist field has the picklist value label _Hematology_, so you would enter `therapeutic_area__vs = 'Hematology'`.

If you supply a value that is not a valid label, VQL treats the result as undefined. This means inequalities return nothing. For example, `workflow_type__v {=,>,<} 'Invalid Label'` returns nothing because `'Invalid Label'` is not a valid value of the picklist.

#### Documents

To query document picklists, use the picklist value label enclosed in single quotes (`'`). For example, the _Therapeutic Area_ picklist field has the picklist value label _Hematology_, so you would enter `therapeutic_area__vs = 'Hematology'`.

If you supply an invalid value for the label, VQL treats the label as a string. This means inequalities operate alphabetically and return results for invalid picklist values. For example, if a picklist named `p` contains values `{'k', 'g', 'a'}`, then `p < 'h'` evaluates to `true` for values `'a'` and `'g'`.

#### Objects

To query object picklists, do not enter picklist value labels as shown in the object record details. Instead, 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`, so you would enter `therapeutic_area__vs = 'hematology__vs'`. To find picklist value names, navigate to **Business Admin > Picklists**.

If you supply an invalid value for the label, VQL treats the label as a string. This means inequalities operate alphabetically and return results for invalid picklist values. For example, if a picklist named `p` contains values `{'k', 'g', 'a'}`, then `p < 'h'` evaluates to true for values `'a'` and `'g'`.

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 {#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, 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 Name | Field 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'` |


<div class="note-border alert-info">
  <div class="alert alert-info" role="alert">
    <div><i class="far fa-info-circle"></i></div>
    <div class="alert-text">
      <p><strong>Note</strong>: As a best practice, we recommend using the <strong>Token Helper</strong> (<img class="inline" src="https://platform.veevavault.help/assets/images/platform_criteriaVQL_tokenhelper.png" alt="Token Helper button" style="" />) button to the right of the <em>Criteria VQL</em> text box to search for available object lookup fields.</p>
    </div>
  </div>
</div>



### 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` (for example, `2014-12-20`)
* DateTime formats are `YYYY-MM-DD'T'HH:MM:SS.SSS'Z'` ( for example, `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 (`=,` `!=`, `<`, `>`, `<=`, `>=`). 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` |

### Expression Limitations on Static Reference Constraints

You can use the following standard VQL operators when defining static reference constraints: `=,` `!=`, `>`, `<`, `>=`, `<=`.

To use the `AND` clause in your static reference constraint on a document field, you must use a comma (`,`). For example:

`id IN (SELECT id FROM countryproduct__cr WHERE country__c CONTAINS {{this.country__v}}, state__v = 'approved_state__c')`

There are several <a class="external-link " href="https://developer.veevavault.com/vql/#Operator_Limitations" target="_blank" rel="noopener">limitations on operations<i class="fa fa-external-link" aria-hidden="true"></i></a> when querying multi-value picklists in raw objects.

Learn more about [Criteria VQL operators][1] below.

## Dynamic Reference Constraints {#dynamic-reference-constraints}

Criteria VQL and filter expressions for dynamic reference constraints must contain a valid field value token instead of a static field value. For static reference constraints, see [above][3].

Tokens are in the format `{{this.field__name}}`. The field must be an object or picklist field or a lookup field referencing an object or picklist field.

### Object Reference Constraints

Here are some examples using dynamic constraints for objects:

| Description | Controlling Field Location | Field to Constrain (Controlled Field) | Relationship between Controlling and Controlled | Criteria VQL |
| --- | --- | --- | --- | --- |
| Only show countries relevant for the selected region | _Region_, on the referring object | _Country_, on the referring object | _Country_ has a reference field to _Region_, indicating the region in which a country belongs. _Country_ and _Region_ has a many-to-one relationship. | `region__v = {{this.region__v}}` |
| Only show applications relevant for the region of the selected country | _Country_, on the referring object | _Application_, on the referring object | _Country_ has a reference field to _Region_, indicating the region in which a country belongs. _Application_ has a reference to _Region_, indicating the region of the application. | `region__v = {{this.country__vr.region__v}}` |
| Only show applications relevant for the selected product | _Product_, on the referring object | _Application_, on the referring object | _Product_ and _Application_ objects have a many-to-many relationship and are related by the join object `product_application__v`. | `id IN (SELECT id FROM product_applications__rimr WHERE product__v = {{this.product__v}})` |

### Document Reference Constraints

Here are some examples using dynamic constraints for documents:

| Description | Controlling Field Location | Field to Constrain (Controlled Field) | Filter Expression |
| --- | --- | --- | --- | --- |
| Only show indications relevant for the selected region | On the referring document | A document object reference field, _Indication_ | `region__v CONTAINS {{this.region__v}}` |
| Only show applications relevant for the region of the selected country | On the referenced object | A document object reference field, _Application_ | `region__v CONTAINS {{this.document_country__vr.countries__vr.region__v}}` |
| Only show applications relevant for the selected product | On an object related to the referenced object | A document object reference field, _Application_ | `id IN (SELECT id FROM product_applications__rimr WHERE product__rim CONTAINS {{this.product__v}})` |

### Expression Limitations on Dynamic Reference Constraints

Dynamic tokens support the `=` and `!=` comparison operators only.

Dynamic reference constraints support object and picklist fields and lookup fields referencing object and picklist fields. Other field types are supported in [static reference constraints][3] only.

There are several <a class="external-link " href="https://developer.veevavault.com/vql/#Operator_Limitations" target="_blank" rel="noopener">limitations on operations<i class="fa fa-external-link" aria-hidden="true"></i></a> when querying multi-value picklists in raw objects.

Learn more about [Criteria VQL operators][1] below.

## Query Object Rules Filter Clause

When working with integration rules, Admins can define [_Query Object Rules_](/en/lr/62154/#create-query-rule) which are additional operations to perform against field rules. While field rules provide the `SELECT` portion of a query, query object rules provide the `WHERE`. To do this, an Admin enters Criteria VQL as the _Filter Clause_ for the query object rule. For example, a field rule can select the _Country_ field on _Product_ object records. By defining your query object rule's _Filter Clause_ as `WHERE status__v = 'active__v'`, you can filter for object records where the _Status_ is _Active_.

Filter clauses support the following operators in addition to the [standard Criteria VQL operators][1]:

| Name | Description |
| --- | --- |
| `IN` | Determines whether or not a value is in the list of values provided after the `IN` operator. Can be used for inner join relationship queries on documents and objects only. |
| `LIKE` | Used with the wildcard character `%` to search for matching field values when you don't know the entire value. VQL does not support fields which begin with a wildcard. |
| `OR` | Returns results when any of the values are true. |

### Validate Limitations

To check your syntax, click **Validate** after entering the Criteria VQL. This link validates the following syntax:

* VQL syntax for the target Vault, which may differ from the source Vault
  * For example, a general release Vault on version 26R1.0 uses VQL version v26.1, while a limited release Vault on version 26R1.3 uses VQL version v26.2. If you're using a Vault to Vault connection between these two Vaults, valid VQL syntax may differ between VQL v26.2 and v26.3.
* VQL syntax as distinct from runtime validity
  * For example, `number_field__c ='custom String text'` is valid VQL syntax, but this would fail at runtime if `number_field__c` is a _Number_ type field, because _Number_ fields cannot accept string values.

### Filter Clause Example

The following is an example of _Filter Clauses_ in object query rules:

| Description | Filter Clause |
| --- | --- |
| Only query for objects (or documents) which are in the _Complete_ lifecycle stage and have a _Status_ of _Active_. | `stage__v = 'complete__c' AND status__v = 'active__v'` |

## Related Sections

If your object's page layout is configured to display [related sections](/en/lr/26387/#filter-sections), you can filter the items that users can select in these sections with Criteria VQL.

Related sections also support dynamic tokens, such as `{{this.field__c}}`. Dynamic tokens support the `=` and `!=` comparison operators only. For example, `max_dosage__c = {{this.dosage__c}}` is supported, while a comparison such as `max_dosage__c > {{this.dosage__c}}` is not supported.

In addition to the standard [Criteria VQL operators][1], related sections also support the following operators:

| Name | Description |
| --- | --- |
| `LIKE` | Used with the wildcard character `%` to search for matching field values when you don't know the entire value. VQL does not support fields which begin with a wildcard. |

There are several <a class="external-link " href="https://developer.veevavault.com/vql/#Operator_Limitations" target="_blank" rel="noopener">limitations on operations<i class="fa fa-external-link" aria-hidden="true"></i></a> when querying multi-value picklists in raw objects.

### Related Object Sections

Criteria VQL for related object sections also support the following unique functions:

| Name | Description |
| --- | --- |
| `{{IN_LAST(numberOfDays)}}` | Used to specify a date field that falls between the current date and a number of days beforehand. Dates are inclusive. For example, `created_date__v {{IN_LAST(7)}}` queries for object records created in the last seven days. |
| `{{IN_NEXT(numberOfDays)}}` | Used to specify a date field that falls between the current date and a number of days afterwards. Dates are inclusive. For example, `expiration_date__v {{IN_NEXT(7)}}` queries for object records expiring in the next seven days. |

Learn more about [token selection](/en/lr/6382/).

These functions cannot be included in a Criteria VQL statement which also contains dynamic tokens. For example, the following Criteria VQL is valid:

* `name__v = {{this.name__v}}`
* `created_date__v {{IN_LAST(7)}}`

While the following Criteria VQL is not valid:

* `name__v = {{this.name__v}} AND created_date__v {{IN_LAST(7)}}`

You can check your Criteria VQL syntax by clicking **Validate**.

## Standard Criteria VQL Operators {#criteria-vql-operators}

Criteria VQL supports various standard operators.

### Comparison Operators

Criteria VQL supports the following comparison operators:

| Operator | Description |
| --- | --- |
| `=` | Equal to |
| `!=` | Not equal to |
| `<` | Less than |
| `>` | Greater than |
| `<=` | Less than or equal to |
| `>=` | Greater than or equal to |

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

### Logical Operators

Criteria VQL supports the logical operators below.

#### AND

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'`

Parentheses can be used to enclose searches.

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

#### CONTAINS

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

`therapeutic_area__vs CONTAINS ('hematology__vs','cardiology__vs')`

#### BETWEEN

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

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

#### Unsupported

The following logical operators are not supported in Criteria VQL: `NOT`, `AND NOT`, `OR NOT`, `FIND`.

[1]: #criteria-vql-operators
[2]: #dynamic-reference-constraints
[3]: #static-reference-constraints
[4]: #object-lookup-fields