When an application invokes a query call, it passes in a Vault Query Language (VQL) statement (a SQL-like statement) that specifies the object to query (in the FROM clause), the fields to retrieve (in the SELECT clause), and any optional filters to apply (in the WHERE and FIND clauses) to narrow your results. Additional syntax can control how your results display.
For detailed information about VQL syntax, how to structure queries, and how to retrieve fields from a single object (document, product, etc.), see Query Syntax & Structure.
The Query API supports relationship queries (joins) where more than one object is included in a single query. This is covered in Relationship Queries and Many-to-Many Relationship Queries.
The following standards and specifications apply to Vault queries.
name__v, documents, and product__v in lower-case.id field name on documents (only), which may be entered as id or ID.name__v = 'cholecap' (where name__v is a String field type) returns no results if the field value is Cholecap.therapeutic_area__vs = 'Hematology__v' (where therapeutic_area__vs is a Picklist field type) returns no results if the field value is hematology__v.caseinsensitive({FIELD_NAME__V}) to bypass case-sensitivity. For example:caseinsensitive(name__v) = 'cholecap' returns results even if the value is Cholecap.tokenize request parameter to true.'2016-01-16'.'2016-01-15T07:30:00.000Z'..000Z UTC expression. The zeros may be any number.LIMIT and OFFSET parameters to limit the number of results displayed per page and paginate results.next_page and previous_page URL endpoints to paginate results.next_page and previous_page URL endpoints when pagination is required.application/json) is the default response format.Accept to application/xml.API_LIMIT_EXCEEDED error message is returned and no further calls will be processed until the next 5 minute or 24 hour period begins.Submit a query by sending a POST or GET request to the /api/{version}/query endpoint. For example:
Enter $ curl -k -X POST -H "Authorization: {SESSION_ID}" -d "q=SELECT id FROM documents" https://{customer}.veevavault.com/api/{version}/query
Send a GET request to https://{customer}.veevavault.com/api/{version}/query?q=SELECT id FROM documents
$ curl -X GET -H "Authorization: {SESSION_ID}" \
https://myvault.veevavault.com/api/v14.0/query?q=SELECT id FROM documents
| Name | Description |
|---|---|
Accept | application/json (default) or application/xml |
X-VaultAPI-DescribeQuery | Set to true to include static field metadata in the response for the data record. If not specified, the response does not include any static field metadata. |
The above query returns a JSON response. To request an XML response, set the HTTP Request header to Accept to application/xml.
{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 17,
"total": 17
},
"data": [
{
"id": 4
}
]
}
On SUCCESS, the response includes the following information:
| Name | Description |
|---|---|
limit | The number of records displayed per page. This can be modified. Learn more. |
offset | The records displayed on the current page are offset by this number of records. Learn more. |
size | The total number of records displayed on the current page. |
total | The total number of records found. |
previous_page | The Pagination URL to navigate to the previous page of results. This is not always available. Learn more. |
next_page | The Pagination URL to navigate to the next page of results. This is not always available. Learn more. |
data | All records found. |
When you include the X-VaultAPI-DescribeQuery header and set it to true, the query response includes the static metadata description. This option eliminates the need to make additional API calls to understand the shape of query response data.
Note: For formula fields, QueryDescribe should describe the field as specified in the metadata, excluding the formula attribute.
$ curl -X GET -H "Authorization: {SESSION_ID}" \
-H "X-VaultAPI-DescribeQuery: true" \
https://myvault.veevavault.com/api/v14.0/query?q=SELECT id FROM documents
{
"responseStatus": "SUCCESS",
"queryDescribe": {
"object": {
"name": "documents",
"label": "documents",
"label_plural": "documents"
},
"fields": [
{
"type": "id",
"required": true,
"name": "id"
}
]
},
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 17,
"total": 17
},
"data": [
{
"id": 24
}
]
}
On SUCCESS, the response includes the following query description:
| Name | Description |
|---|---|
name | The name of the queryable object. |
label | The label of the queryable object. |
label_plural | The plural label of the queryable object |
The field metadata may include some or all of the following:
| Metadata Field | Description |
|---|---|
name | Name of the field. |
label | The UI label of the field. |
type | The data type, for example, string or int |
max_length | The max length of a string field. |
max_value | The max value a number field. |
min_value | The minimum value of a number field. |
scale | The number of digits after a decimal point in a number field. |
required | Indicates the field is required. |
unique | Indicates if the value must be unique (true/false). |
status | Indicates if the field is active (active/inactive). |
picklist | The picklist name field value. |
In Vault, “objects” are divided into three main categories:
Standard Vault Objects
product__v, country__v, study__v, site__v, and others.__v, e.g., to query the products object, enter product__v.Custom Vault Objects
__c, e.g., to query a “custom object”, enter custom_object__c.Vault Component Objects
accountmessage__sys, signaturepage__sys, objecttype__sys, and others. Other Objects
documents.Queryable objects and the vaults in which they are available are listed below.
To retrieve the collection of Vault Objects, send a GET request to the /api/{VERSION}/metadata/vobjects endpoint. See the Retrieve Object Collection.
The following queryable objects are available in all Vault applications (PromoMats, eTMF, QualityDocs, MedComms, Submissions):
documentsbinders, available in v18.2+ onlyrelationshipseventsusersworkflowsThe following objects are currently not queryable:
groupsroleslifecycle__vpicklistssecuritypoliciesThe following Vault objects are available in all Vault applications (PromoMats, eTMF, QualityDocs, MedComms, Submissions):
product__vcountry__vuser__sys, available in v18.1+ onlyThe following Vault objects are available in PromoMats vaults:
directory__vsurvey__vkey_message__vwebsite__v2253submissionapplicant__vThe following Vault objects are available in eTMF vaults:
study__vstudy_country__vsite__vlocation__vmilestone__vThe following Vault objects are available in QualityDocs vaults:
facility__vdepartment__vAll objects listed below are queryable. However, not all fields on these objects are queryable.
To find queryable fields, send a GET request to the endpoints below and look for fields with the attribute: "queryable": true.
Documents & Binders: /api/{version}/metadata/objects/documents/properties endpoint. See the Retrieve All Document Fields API.
Document & Binder Relationships: /api/{version}/metadata/objects/documents/types/{document_type}/relationships endpoint. See the Retrieve Document Type Relationships API.
Document Events: /api/{version}/metadata/objects/documents/events/{event_type}/events/{event_subtype} endpoint. See the Retrieve Document Event SubType Metadata API.
Workflows: /api/{version}/metadata/objects/workflows endpoint. See the Retrieve Workflow Object Metadata API.
Users: /api/{version}/metadata/objects/users endpoint. See the Retrieve User Metadata.
As of 17.1, the LongText field type allows users to enter text strings up to 32,000 characters. While a FIND clause always searches the entire field, other queries will only return the first 250 characters of a LongText field by default. To return all of the characters, use the LONGTEXT() function. Note that VQL only supports SELECT clauses with LONGTEXT() function.
SELECT id, LONGTEXT(long_text_field__c) FROM object__c
LongText fields do not support wildcard (*) searching on spaces. For example, when trying to match on “Vault Query Language” in a LongText field, the following finds no results:
SELECT id FROM longtext_obj__c FIND('Vault*Language' SCOPE long_text_field__c)
In addition, VQL does not support LIKE that starts with a wildcard (%). For example, name__v LIKE '%_DOC' will not work. If the wildcard is in the middle or the end of the LIKE, it works as expected. For example, name__v LIKE 'DOC%'. All other clauses besides LIKE work as expected with leading wildcards.
A formula field calculates field value based on a formula entered during field configuration. VQL allows you to query formula fields.
Formula fields are not searchable and are not stored, and thus cannot be used with FIND, ORDER BY, WHERE clauses. Since the formulas are evaluated during runtime, if there is an error calculating the formula, null is returned for the field value.
If you’re familiar with SQL queries, you shouldn’t have much trouble structuring VQL queries since they share most of the same syntax: SELECT, FROM, WHERE, FIND, etc. With SQL, you typically query relational database tables with data records and fields in rows and columns. With VQL, the same general idea applies as does the way in which you structure queries.
The examples here use the following naming convention:
SELECT {fields} FROM {object}
Where {fields} is field name or comma-separated list of multiple field names for the specified object. Use the Metadata API endpoints to retrieve queryable fields.
Where {object} is documents, relationships, events, users, workflows, or Vault objects (product, country, etc.). These are the only queryable objects.
Note: The following objects are not queryable: binders, groups, roles, lifecycles, picklists, security policies.
SELECT and FROM work together to form the basis of all queries and can be used entirely on their own.
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM documents | Retrieve fields from all documents. | v1.0+ |
SELECT {fields} FROM relationships | Retrieve fields from all document relationships. | v3.0+ |
SELECT {fields} FROM events | Retrieve fields from all document events. | v6.0+ |
SELECT {fields} FROM users | Retrieve fields from all users. | v1.0+ |
SELECT {fields} FROM workflows | Retrieve fields from all workflows. | v4.0+ |
SELECT {fields} FROM {Vault object} | Retrieve fields from all object records. | v8.0+ |
In all Vault queries, you SELECT object fields FROM their corresponding objects. For example:
SELECT id, name__v, status__v FROM documents -- Returns the IDs, names, and status from all documents
SELECT user_name__v FROM users -- Returns the user names from all users
SELECT id FROM product__v -- Returns the IDs from all products
"queryable": true.While SELECT and FROM form a basic query, they can also produce a very large result set. WHERE allows you to apply search filters to narrow the resulting data.
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM documents WHERE {field}{operator}{value} | Retrieve fields from documents which meet the conditions of the filter. | v1.0+ |
SELECT id, name__v, status__v FROM documents WHERE type__v = 'Promotional Piece'
SELECT user_name__v FROM users WHERE `last_login__v` > '2016-01-01'
SELECT id FROM product__v WHERE name__v = 'Nyaxa'
When querying documents or Vault objects, you can use the following operators in the WHERE filter and FIND operator.
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM {object} WHERE {field} = {'value'} | Field values equal to the specified value. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field} != {'value'} | Field values not equal to the specified value. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field} < {'value'} | Field values are less than the specified value. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field} > {'value'} | Field values are greater than the specified value. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field} <= {'value'} | Field values are less than or equal to the specified value. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field} >= {'value'} | Field values are greater than or equal to the specified value. | v1.0+ |
SELECT id, document_number__v FROM documents WHERE status__v = 'Approved'
SELECT id, name__v, generic_name__vs FROM product__v WHERE therapeutic_area__vs = 'hematology__vs'
SELECT id, user_name__v, security_profile__v FROM users WHERE user_locale__v = 'es_US'
SELECT id, document_number__v FROM documents WHERE status__v != 'Obsolete'
SELECT workflow_id__v, workflow_name__v FROM workflows WHERE workflow_status__v != 'Cancelled'
SELECT user_first_name__v, user_last_name__v FROM users WHERE security_profile__v != 'vault_owner__v'
SELECT id, document_number__v FROM documents WHERE document_creation_date__v < '2016-04-23'
SELECT workflow_id__v, task_id__v FROM workflows WHERE workflow_startDate__v < '2016-04-23'
SELECT id, study_name__vs FROM study__v WHERE study_start_date__vs < '2016-04-23'
SELECT id, document_number__v FROM documents WHERE version_creation_date__v > '2016-04-23'
SELECT id, site_status__v, location__v FROM site__v WHERE modified_date__v > '2016-04-23'
SELECT id, user_email__v FROM users WHERE last_login__v > '2016-04-23'
SELECT id, document_number__v FROM documents WHERE version_modified_date__v <= '2016-04-23T07:30:00.000Z'
SELECT id, document_number__v FROM documents WHERE locked_date__v >= '2016-04-23T07:30:00.000Z'
When querying documents or Vault objects, you can use the following operators in the WHERE filter and FIND operator.
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM {object} WHERE {field_1} = {'value_1'} AND {field_2} = {'value_2'} | Field values are equal to both specified values. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field_1} = {'value_1'} OR {field_2} = {'value_2'} | Field values are equal to either specified value. | v1.0+ |
SELECT {fields} FROM {object} WHERE {field} CONTAINS ({'value_1'},{'value_2'},{'value_3'}) | Field values are equal to any of the specified values. | v1.0+ |
The AND operator returns results when both values meet the conditions. For example:
SELECT id, name__v FROM documents WHERE type__v = 'Reference Document' AND status__v = 'Approved'
The OR operator returns results when any of the values meet the conditions. For example:
SELECT id, name__v FROM documents WHERE version_creation_date__v > '2016-04-23' OR version_modified_date__v > '2016-04-23'
Note: VQL does not support using the OR operator between different query objects in a WHERE clause.
#### CONTAINS
The CONTAINS operator is used to enclose multiple values in parentheses. This uses the OR operator logic. For example:
SELECT id, name__v FROM documents WHERE language__v CONTAINS ('English', 'Spanish', 'French')
The query above returns documents with English OR Spanish OR French set on the language field.
The BETWEEN operator is used with AND to compare data between two different values. For example:
SELECT id, name__v FROM documents WHERE document_creation_date__v BETWEEN '2016-02-14' AND '2016-04-23'
The LIKE operator is used with the wildcard character ’%’ to search for matching field values when you don’t know the entire value. For example:
SELECT id, name__v FROM documents WHERE name__v LIKE 'N%25'
The query above returns documents where the name__v value starts with “N”. In this example, ’%25’ is URL encoded ’%’ character.
The IN operator is used to test whether or not a value (stated before the IN operator) is “in” the list of values provided after the IN operator. For example:
SELECT id FROM product__v WHERE id IN (SELECT id FROM document_product__vr)
The IN operator can only be used for inner join relationship queries on documents and objects.
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM documents FIND ({'keywords'}) | Search document fields. | v8.0+ |
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE CONTENT) | Search document content. | v8.0+ |
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE ALL) | Search document fields and content. | v8.0+ |
SELECT {fields} FROM documents FIND ({'keywords'} SCOPE {field}) | Search specific document field. | v15.0+ |
SELECT {fields} FROM {Vault object} FIND ({'keywords'}) | Search object fields. | v14.0+ |
SELECT {fields} FROM {Vault object} FIND ({'keywords'} SCOPE {field}) | Search specific object field. | v15.0+ |
The following rules apply:
Note: When precise matching is absolutely critical, we recommend using the WHERE filter on fields instead of using the FIND operator. FIND is more appropriate for interactive applications.
When using FIND on documents, all queryable document fields are searched. For example:
SELECT id, name__v FROM documents FIND ('insulin')
When using multiple keywords, Vault places an implicit OR operator between each. The following queries are equivalent:
SELECT id, name__v FROM documents FIND ('insulin diabetes')
SELECT id, name__v FROM documents FIND ('insulin OR diabetes')
Vault also places an implicit wildcard character ’*’ at the end of the last keyword. Therefore:
The query below returns the same results as the two above (or more, as all words starting with 'dia’ will be found):
SELECT id, name__v FROM documents FIND ('insulin dia')
To search document fields containing all keywords, use the AND operator between each. For example:
SELECT id, name__v FROM documents FIND ('insulin AND diabetes')
To search for an exact match to all keywords, enclose the string in double-quotes within the single-quotes. For example:
SELECT id, name__v FROM documents FIND ('"blood sugar"')
To search within the document content, use SCOPE CONTENT. For example:
SELECT id, name__v FROM documents FIND ('insulin') SCOPE CONTENT
To search within the document content and all queryable document fields, use SCOPE ALL. For example:
SELECT id, name__v FROM documents FIND ('insulin') SCOPE ALL
To search within a specific document field, use SCOPE {field}.
You can only include one text/string-type or exactmatchstring-type document field. For example:
SELECT id, name__v FROM documents FIND ('insulin' SCOPE name__v)
SELECT id, name__v FROM documents FIND ('insulin' SCOPE title__v)
SELECT id, name__v FROM documents FIND ('insulin' SCOPE description__v)
The following document fields are not supported here: type__v, subtype__v, classification__v, lifecycle__v, status__v, and filename__v.
When using SCOPE {field} on documents, you can combine it with SCOPE CONTENT. For example:
SELECT id, name__v FROM documents FIND ('cholecap' SCOPE product__v AND 'prescribing information' SCOPE CONTENT)
When using FIND on Vault objects, all text/string-type object fields are searched. For example:
SELECT id, name__v FROM product__v FIND ('phosphate')
SELECT id, name__v FROM product__v FIND ('phosphate sulfate')
To search within a specific object field, use SCOPE {field}.
You can only include one text/string-type object field. For example:
SELECT id, name__v FROM product__v FIND ('phosphate' SCOPE generic_name__vs)
You can look up object reference fields by using dot-notation on the object relationship name (__vr, __cr) and the referenced object field name. For example:
SELECT id, name__v, product__vr.name__v FROM study__v FIND ('phosphate' SCOPE product__vr.name__v)
When using FIND and/or SCOPE, you can use the WHERE filter to narrow results. WHERE must be placed after FIND and SCOPE. For example:
SELECT id, name__v FROM product__v FIND ('phosphate') WHERE therapeutic_area__vs = 'cardiology__vs'
SELECT id, name__v FROM product__v FIND ('phosphate' SCOPE generic_name__vs) WHERE therapeutic_area__vs = 'cardiology__vs'
When searching documents and objects using the FIND operator, use the wildcard character * to find partial matches. For example:
FIND ('ins* dia* glu*') returns documents containing words starting with 'ins’ OR 'dia’ OR 'glu’.You can place the wildcard character in any part of the keyword. For example:
FIND ('*ology') returns documents or objects with words ending with 'ology’, e.g., psychology, urology, etc.FIND ('o*ology') returns documents or objects with words starting with 'o’ and ending with 'ology’, e.g., oncology, ophthalmology, etc.When the search string is not enclosed in parentheses or double-quotes, Vault places an implicit wildcard character * at the end of the last search term by default. Therefore:
FIND ('glu') is the same as FIND ('glu*') - All words starting with 'glu’ will be found. In this case, the wildcard character is not needed.You can use the wildcard character when searching document content: For example:
FIND ('*ology') SCOPE CONTENT returns documents containing words ending in 'ology’ in the document content.When performing keyword searches using API v8.0 or earlier, Vault automatically tokenizes CamelCase, alphanumeric, and delimited strings. For example:
FIND ('CamelCase') becomes FIND ('Camel OR Case')FIND ('abc123') becomes FIND ('abc OR 123')FIND ('abc-def') becomes FIND ('abc OR def')To disable tokenization, set the tokenize request parameter to false. For example (cURL):
q=SELECT id, name__v FROM documents FIND ('CamelCase') "https://myvault.veevavault.com/api/v15.0/query?tokenize=false
You can also disable tokenization by surrounding the search phrase in double-quotes within single-quotes. For example:
SELECT id, name__v FROM documents FIND ('"abc123"')
As of API v9.0, Vault only tokenizes alphanumeric strings. To enable tokenization of CamelCase and delimited strings, set the tokenize request parameter to true.
Values for Date and DateTime field types must be entered in UTC (Coordinated Universal Time) and not the user’s time zone. For example:
'2016-04-23').'2016-04-23T15:30:00.000Z').When using date formats, the time is assumed to be “00:00:00” (midnight of the specified date). For example:
SELECT id, name__v FROM documents WHERE document_creation_date__v > '2015-11-01'
The query above returns all documents completed after October 31st. The completion date of one minute after midnight on November 1st is considered greater than “2015-11-01”.
Boolean (True/False) field values are used to find documents and objects having these values. For example:
SELECT id, name__v FROM documents WHERE crosslink__v = TRUE
SELECT id, name__v FROM documents WHERE locked__v = FALSE
Null field values are used to find documents and objects having no value set on a particular field. For example:
SELECT id, name__v FROM documents WHERE external_id__v = NULL
By default, all field values are case-sensitive. This applies to all field types. For example:
name__v = 'cholecap' (where name__v is a String field type) returns no results if the field value is Cholecap.therapeutic_area__vs = 'Hematology__v' (where therapeutic_area__vs is a Picklist field type) returns no results if the field value is hematology__v.As of API v14.0, Vault allows you to perform “case-insensitive” queries by using the caseinsensitive({FIELD_NAME}) parameter in the WHERE filter. For example:
SELECT id FROM product__v WHERE caseinsensitive(name__v) = 'cholecap' returns results even if the field value is “Cholecap”.Note the following scope and limitations:
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM documents WHERE status__v = STEADYSTATE() | Retrieve fields from all documents in a Steady State (v1.0, v2.0, etc.). | v8.0+ |
SELECT {fields} FROM documents WHERE status__v = OBSOLETESTATE() | Retrieve fields from all documents in a Obsolete State. | v8.0+ |
SELECT {fields} FROM documents WHERE status__v = SUPERSEDEDSTATE() | Retrieve fields from all documents in a Superseded State. | v8.0+ |
SELECT LATESTVERSION {fields} FROM ALLVERSIONS documents | Retrieve fields from the latest version of all documents. | v8.0+ |
Use one of the following document functions to filter on document lifecycle states:
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE status__v = STEADYSTATE()
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE status__v = OBSOLETESTATE()
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE status__v = SUPERSEDEDSTATE()
Note: Document functions are formatted differently in API v8.0 and earlier:
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE STEADYSTATE() = true
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE OBSOLETESTATE() = true
SELECT id, name__v FROM documents SELECT id, name__v FROM documents WHERE SUPERSEDEDSTATE() = true
Vault allows you use the binders and binder_node__sys objects to query binders for the following:
Since the binders object is an extension of the documents object, it supports the same VQL functions.
The binders object exposes the binder_nodes__sysr relationship. This relationship is a “down” relationship and points to binder_nodes__sys child objects.
The binder_node__sys object exposes the following relationships:
| Name | Description |
|---|---|
binder__sysr | This relationship is a parent lookup relationship and points to the binders object. |
document__sysr | This relationship is a lookup relationship to a document at the node. This is applicable only if the node is a document (or a binder, which is a type of a document). |
child_nodes__sysr | This is a self-referencing “down” relationship pointing to binder_node__sys child objects. This is applicable only if the node is a section. |
parent_node__sysr | This is a self-referencing parent lookup relationship pointing to a binder_node__sys object at the parent node. The parent node could either be a node of type section or a null for root node. |
The following fields are queryable for the binder_node__sys object:
| Name | Description |
|---|---|
id | The node ID |
name__v | The section name. This field only has value for nodes of type “section”. You should get a null back in this field for document or binder nodes. The top level node will have a name__v value because it is considered a section. |
parent_binder_id__sys | Reference to the parent binder where this node lives. |
parent_binder_major_version__sys | Reference to the parent binder where this node lives. |
parent_binder_minor_version__sys | The minor version of the parent binder |
parent_node_id__sys | Reference to parent binder_node__sys object. |
order__sys | The ordinal position of the node within its parent. |
content_id__sys | Reference to the Document or Binder. |
content_major_version__sys | The major version of the content. |
content_minor_version__sys | The minor version of the content. |
type__sys | Points to the new standard picklist binder_node_types__sys. |
created_date__v | Timestamp when the node was created. |
created_by__v | ID of a user who created the node. |
modified_date__v | Timestamp when the node was updated. |
modified_by__v | ID of a user who updated the node. |
Use the following to find latest steady-state versions of binders, the documents they contain, and where within the binder structure the document is contained.
SELECT latestversion id, (SELECT parent_node_id__sys, parent_node__sysr.type__sys, parent_node__sysr.name__v, document__sysr.id, document__sysr.name__v FROM binder_nodes__sysr)
FROM allversions binders
WHERE status__v = steadystate()
Find binders containing specific documents.
SELECT binder__sysr.id, binder__sysr.name__v
FROM binder_node__sys
WHERE type__sys = 'document' AND document__sysr.name__v = 'Test'
Find documents within sections named “Test Section”.
SELECT binder__sysr.id, document__sysr.id
FROM binder_node__sys where type__sys = 'document' AND parent_node__sysr.type__sys = 'section' AND parent_node__sysr.name__v = 'Test Section'
Find binders and section names containing specific documents.
SELECT binder__sysr.id, binder__sysr.name__v, parent_node__sysr.name__v, parent_node__sysr.type__sys
FROM binder_node__sys where type__sys = 'document' AND document__sysr.name__v 'Test'
Vault allows you use the renditions object to query rendition properties for a document and document versions.
The following fields are queryable for the renditions object:
| Name | Description |
|---|---|
rendition_type__sys | Public name of Renditiontype, for example, viewable_rendition__v. There is no lookup to Renditiontype metadata. |
document__id | The parent document id. |
major_version_number__sys | parent document major version |
minor_version_number__sys | parent document minor version |
size__sys | Size of unencrypted rendition file |
md5checksum__sys | MD5 checksum of unencrypted file |
filename__sys | Name of the file. |
pending__sys | Indicates if the rendition file is being processed (true) or complete (false). |
format__sys | File format of the rendition file, for example: application/vnd.openxmlformats-officedocument.wordprocessingml.document |
upload_date__sys | The upload date for the rendition. |
document_version_id | Compound document version id field. |
Use the following to get document and rendition properties for a collection of document versions. Note that you will need to respect VQL query size limits and break up your queries.
SELECT id, name__v, (SELECT rendition_type__sys, md5checksum__sys, size__sys, filename__sys FROM renditions__sysr)
FROM ALLVERSIONS documents
WHERE version_id
CONTAINS (102_0_3, 106_1_2, 107_1_0)
Get document and rendition properties for steady state version of a set of documents.
SELECT id, name__v, (SELECT rendition_type__sys, md5checksum__sys, size__sys, filename__sys FROM renditions__sysr )
FROM documents
WHERE status__v = steadystate()
AND id
CONTAINS (101,102,103)
Get document and custom rendition properties for steady state version of a set of documents.
SELECT id, name__v, (SELECT md5checksum__sys, size__sys, filename__sys FROM renditions__sysr WHERE rendition_type__sys = 'my_rendition_type__c' )
FROM documents
WHERE status__v = steadystate()
AND id
CONTAINS (101,102,103)
Query for Renditions of a particular type.
SELECT document_id, size_v, upload_date__sys
FROM renditions
WHERE rendition_type__sys = 'insight_rendition__c'
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM documents ORDER BY {field} ASC | Sort ascending order. | v8.0+ |
SELECT {fields} FROM documents ORDER BY {field} DESC | Sort descending order. | v8.0+ |
SELECT {fields} FROM documents FIND ({'keywords'}) ORDER BY RANK | Sort by keyword relevancy. | v10.0+ |
Results are returned in default order. To enforce a specific order, use the ORDER BY operator. For example:
SELECT id FROM documents ORDER BY id DESC displays the IDs in descending order (99, 98, 97, etc.).SELECT id FROM documents ORDER BY id ASC displays the IDs in ascending order (1, 2, 3, etc.).You can order results using any document or object field. For example:
SELECT name__v FROM documents ORDER BY name__v DESC orders the results by name instead of ID and displays the names in descending order.SELECT name__v FROM documents ORDER BY name__v ASC ascending order.You can enforce both the primary and secondary order by using a comma-separated string of field names. The field sort priority is left to right. For example:
SELECT name__v, type__v FROM documents ORDER BY type__v DESC, name__v DESC sorts the results first by type and then by name, both in descending order.Example response:
{
"type__v": "Resource",
"name__v": "VeevaProm Resource Doc"
}
{
"type__v": "Resource",
"name__v": "Nyaxa Resource Doc"
}
{
"type__v": "Resource",
"name__v": "CholeCap Resource Doc"
}
{
"type__v": "Claim",
"name__v": "VeevaProm Claim Doc"
}
{
"type__v": "Claim",
"name__v": "Nyaxa Claim Doc"
}
{
"type__v": "Claim",
"name__v": "CholeCap Claim Doc"
}
When performing searches with FIND, you can sort the results by relevancy to a search phrase using the ORDER BY rank operator. For example:
SELECT id, name__v FROM documents FIND ('ABC') ORDER BY rank sorts the results in descending order starting with those most closely matching the search phrase.Example response:
{
"id": 99,
"name__v": "Document ABC"
}
{
"id": 97,
"name__v": "Document ABCD"
}
{
"id": 98,
"name__v": "Document ABCDE"
}
| Query | Description | API Version |
|---|---|---|
SELECT {fields} FROM documents LIMIT {number} | Limit number of results per page. | v8.0+ |
SELECT {fields} FROM {OBJECT} LIMIT {number} | Limit number of results per page. | v8.0+ |
By default, the maximum number of results displayed per page is 1000 for documents and 1000 for objects. This can be lowered using the LIMIT operator. For example:
SELECT id FROM documents LIMIT 25 returns 25 documents per page.SELECT id FROM product__v LIMIT 250 returns 250 object records per page.Using LIMIT does not change the total number of results found, only the number displayed per page.
Note: When performing queries with unusually large numbers of fields in the SELECT clause, the API may scale down the number of results per page to reduce stress on the memory limit of the system. When this happens, you may experience an unexpected number of results in your response, e.g., you were expecting 1000 results per page but the system only returned 400 per page. In these cases, the system returns the same total number of results; they are simply distributed across more pages. This applies only to API v11.0 and later.
When the number of results found exceeds the number displayed per page, use the OFFSET operator to display the next and previous pages of results.
For example: Assume a query returns 500 total results and the LIMIT is set to display 100 results per page.
LIMIT 100 OFFSET 100.LIMIT 100 OFFSET 200.As of API v10.0, document and Vault Object queries include the next_page and previous_page URL endpoints when pagination is required. For example:
{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 100,
"offset": 200,
"size": 100,
"total": 500,
"previous_page": "/api/v11.0/query/c2b58293-1606-4c99-925d-b9b89e83670e?limit=100&offset=100",
"next_page": "/api/v11.0/query/c2b58293-1606-4c99-925d-b9b89e83670e?limit=100&offset=300"
The URLs include information from the original query and provide a simple method of automatically paginating to the next and previous pages of results. Simply access the URL with a GET method to retrieve the data.
Note: The next_page and previous_page endpoints only remain active for 20 minutes following query submission. After that, the original query must be resubmitted.
Note: When the query result doesn’t generate the next_page and previous_page URLs, submit additional queries using OFFSET to paginate over the result set.
The Query API is used to construct simple but powerful queries to retrieve data from Veeva Vault. This article covers advanced query use cases using Join relationships. For general information about the Query API, refer to Query Syntax & Structure or Vault Query API Overview. You may also find additional information and help with Vault queries in the Vault Developer Community.
Note: Relationship queries are supported as of API v10.0
VQL is the object query language for querying data in Vault. You can think of it as an object oriented cousin of SQL, where the data model and the way to use joins differs from SQL. Vault’s data model is based on objects that are related to each other by relationships. Navigating these relationships is a fundamental building block in constructing queries that allow you to use data from different objects, thus using implicit joins. In other words, the relationship syntax in VQL allows you to join data using path traversal.
Programmatic access to a result set of a VQL query naturally reflects the object oriented nature of the underlying data model. VQL uses the navigation and relationships and is anchored to a particular object from where the result set is obtained by using the FROM clause. Consequently, the returned result set is a list of objects that reflect the object that constitutes the domain of the VQL query. The result set also contains the list of fields that are included in the SELECT clause, including those that are traversed via the relationships. The SELECT clause can include nested SELECT-FROM statements using relationships and the result set can include nested objects within a result set of objects. This is illustrated in the examples below.
In this tutorial, we’ll look at some of the capabilities and patterns in VQL that allow developers to conceptualize familiar concepts like joins in SQL and puts them in perspective with respect to VQL’s syntax and capabilities.
When performing relationship queries, you must include at least one field in each SELECT statement.
You can use a maximum of 10 relationships in a single query. The way in which Vault counts the number of relationships in a query is based on the way VQL constructs the joins in order to process the query. The join is only needed for the following two conditions:
In the examples below, we’ll use a simplified subset of baseball team rosters.
Object A: Teams team__v
Team ID id | Team Name name__v | Team City city__v |
|---|---|---|
| 101 | Giants | San Francisco |
| 102 | Royals | Kansas City |
| 103 | Yankees | New York |
| 104 | Cubs | Chicago |
Object B: Players player__v
Player ID id | Player Name name__v | Player Position position__v | Team team__v |
|---|---|---|---|
| 44 | Doe | Pitcher | 101 |
| 55 | Post | Catcher | 101 |
| 66 | Daniels | First Base | 102 |
| 77 | Perez | Catcher | 102 |
| 88 | Beltran | Right Field | 103 |
| 99 | Ryan | Short Stop | 103 |
We’ll represent the Teams object as team__v and the Players object as player__v. Each team has a one-to-many (1:M) relationship with its players (one team has many players and one player can be assigned to only one team). These are Parent-Child relationships, where team__v is the parent of player__v. The objects are connected by an inbound and outbound relationship, always looked at from the perspective of the child object. In other words, the child object has a relationship coming “inbound” from the parent object and another relationship going “outbound” to the parent object. The team__v relationship field on the player__v object establishes its relationship with the team__v object.
Relationship naming conventions always use the plural form of the child object for inbound relationships and the singular form of the parent object for outbound relationships, Relationship names end in __vr here and in Vault for standard __v objects. For example, The inbound relationship name is players__vr for the team__v and player__v objects and the outbound relationship name is team__vr. The relationship names allow us to traverse from parent to child or child to parent. The examples below illustrate their usage in relationship queries (Joins). These are completely analogous to Vault Object queries.
NOTE: The team and player objects share some of the same field names: id and name__v. This is merely a naming convention. The field values are different on each object. The id or name retrieved (team or player) depends on the object being queried.
Problem
Retrieve the id, name, and city from all teams and the id, name, and position of the players assigned to each team.
Query
SELECT id, name__v, city__v, (SELECT id, name__v, position__v FROM players__vr) FROM team__v
Result
Team ID id | Team Name name__v | Team City city__v | Player ID id | Player Name name__v | Player Position position__v |
|---|---|---|---|---|---|
| 101 | Giants | San Francisco | 44 | Doe | Pitcher |
| 101 | Giants | San Francisco | 55 | Post | Catcher |
| 102 | Royals | Kansas City | 66 | Daniels | First Base |
| 102 | Royals | Kansas City | 77 | Perez | Catcher |
| 103 | Yankees | New York | 88 | Beltran | Right Field |
| 103 | Yankees | New York | 99 | Ryan | Short Stop |
| 104 | Cubs | Chicago | null | null | null |
| 104 | Cubs | Chicago | null | null | null |
Discussion
The object of the query is team__v, from which we’re directly retrieving the team id, name, and city. Since the team__v object is the parent of the player__v object, we used the inbound relationship name players__vr in a nested SELECT-FROM statement to retrieve the player id, name, and position from the player__v object.
Nested SELECT-FROM statements within the SELECT clause are very useful for obtaining related records by traversing from the “1” side of a 1:M relationship (from parent to child). We know from SQL that the result of a left outer join for objects A and B always contains all records of the “left” object (A), even if the join-condition does not find any matching record in the “right” object (B). Notice in this query that the “Cubs” team returned null results from the player object.
Problem
Retrieve the id, name, and city from all teams. Restrict the results to teams with assigned players.
Query
SELECT id, name__v, city__v FROM team__v WHERE id IN (SELECT name__v FROM players__vr)
Result
Team ID id | Team Name name__v | Team City city__v |
|---|---|---|
| 101 | Giants | San Francisco |
| 101 | Giants | San Francisco |
| 102 | Royals | Kansas City |
| 102 | Royals | Kansas City |
| 103 | Yankees | New York |
| 103 | Yankees | New York |
Discussion
This is nearly identical to the previous query but we’ve removed the nested SELECT-FROM statement from the SELECT clause and placed another in the WHERE clause. We’re also using the team id (which exists in both objects) and the logical operator IN. The object of the query is team__v. The nested SELECT-FROM statement is using the inbound relationship name players__vr to look at the player names in the player__v object and return results only when the team id exists in a player’s row. We’re not asking to return the player names (they are not included in the first SELECT clause) but only to filter on them.
Combining a nested SELECT-FROM statement with the IN operator in the WHERE clause allows us to test whether the team id exists and return results only when it does. We know from SQL that the result of an inner join for objects A and B requires that each record in the two joined objects have matching records. This query compared each row of object A with each row of object B and found all pairs of rows which satisfied the join-predicate (non-null values).
Problem
Retrieve the id and name of all players in the Catcher position and the id, name, and city of the team to which each player is assigned.
Query
SELECT id, name__v, team__vr.id, team__vr.name__v, team__vr.city__v FROM player__v WHERE position__v = 'Catcher'
Result
Player ID id | Player Name name__v | Team ID team__vr.id | Team Name team__vr.name__v | Team City team__vr.city__v |
|---|---|---|---|---|
| 55 | Post | 101 | Giants | San Francisco |
| 77 | Perez | 102 | Royals | Kansas City |
Discussion
Unlike the previous two queries in which the object of the query was team__v (parent), we’re now querying the object player__v (child). Child to parent queries are many-to-one (M:1) relationships and a nested SELECT-FROM statement can’t be used. To retrieve the fields from the parent object, we must combine its field names with the outbound relationship name team__vr using dot-notation.
Look at the basic part of this query: SELECT the player id and name from the player object WHERE the position is “Catcher”. Using dot-notation, add team__vr.id, team__vr.name__v, and team__vr.city__v to the SELECT clause. The outbound relationship name team__vr allows us to traverse “outbound” from the child player__v object to the parent team__v object to retrieve the team id, name, and city. The response contains the selected fields from each child object and the related parent object fields.
The last type of query we’ll discuss is a “lookup”.
Object A: Teams team__v
Team ID id | Team Name name__v | Mascot mascot__v |
|---|---|---|
| 101 | Giants | 5 |
| 102 | Royals | 6 |
| 103 | Yankees | 7 |
| 104 | Cubs | 8 |
Object C: Mascots mascot__v
Mascot ID id | Mascot Name name__v | Animal animal__v |
|---|---|---|
| 5 | Lou | Seal |
| 6 | Slugger | Lion |
| 7 | Dandy | Bird |
| 8 | Clark | Bear |
For this example, we’ll modify the Teams object team__v and create a new Mascots object mascot__v. Each team has a 1:1 relationship with its mascot (each team has only one mascot and each mascot has only one team). There is no true parent to child or child to parent relationship between these objects. There is what’s referred to as a “reference relationship”, which is why a “lookup” query must be used. To traverse the relationship, we’ll use the reference relationship name mascots__vr.
NOTE: The team and mascot objects share some of the same field names: id and name__v. This is merely a naming convention. The field values are different on each object. The id or name retrieved (team or mascot) depends on the object being queried.
Problem
Retrieve the team id and name which has a “Bear” as a mascot.
Query
SELECT id, name__v FROM team__v WHERE mascots__vr.animal__v = 'Bear'
Result
Team ID id | Team Name name__v |
|---|---|
| 104 | Cubs |
Discussion
The object of the query is team__v, which includes the mascot__v object field for the mascot but not its name or animal. This type of query is called a “lookup” because we’re using the animal__v field record from the mascot__v object and using dot-notation to combine it with the mascots__vr relationship in the WHERE clause, thereby looking up the team id and name from the team__v object.
In the examples below, we’ll use a simplified subset of Vault documents and products.
Object A: Documents documents
Document ID id | Document Name name__v | Document Type type__v |
|---|---|---|
| 1 | CholeCap Study | Study |
| 2 | Nyaxa Brochure | Promotional Piece |
| 3 | VeevaProm Information | Reference Document |
Object B: Products product__v
Product ID id | Product Name name__v | Generic Name generic__v |
|---|---|---|
| 01010 | CholeCap | cholepridol phosphate |
| 02020 | Nyaxa | nitroprinaline oxalate |
| 03030 | VeevaProm | veniladrine |
| 04040 | VeevaProm XR | veniladrine extended |
In Vault, documents documents and products product__v have many-to-many (M:M) relationships (a document may be associated with many products and a product may be associated with many documents). The same applies to document relationships with other Vault objects such as country__v, study__v, site__v, etc. These document-object relationships are referred to as reference relationships. Their reference relationship names (to use in relationship queries) are exposed in the document metadata relationship fields. We’ll discuss finding relationships in the sections below.
To traverse the document-product relationship, we’ll use the reference relationship name document_product__vr. The two queries shown below illustrate the bidirectional nature of document-product relationships. The direction of the query and structure of results depends on which object is being queried, i.e., the object in the FROM clause.
NOTE: The document and product objects share some of the same field names: id and name__v. This is merely a naming convention. The field values are different on each. The id or name retrieved (document or product) depends on the which is queried.
Problem
Query the Documents object documents to retrieve document fields. Use the document_product__vr relationship to retrieve product fields associated with each document.
Query
SELECT id, name__v, type__v, (SELECT id, name__v, generic__v FROM document_product__vr) FROM documents
Result
Document ID id | Document Name name__v | Document Type type__v | Product ID id | Product Name name__v | Generic Name generic__v |
|---|---|---|---|---|---|
| 1 | CholeCap Study | Study | 01010 | CholeCap | cholepridol phosphate |
| 2 | Nyaxa Brochure | Promotional Piece | 02020 | Nyaxa | nitroprinaline oxalate |
| 3 | VeevaProm Information | Reference Document | 03030 | VeevaProm | veniladrine |
| 3 | VeevaProm Information | Reference Document | 04040 | VeevaProm XR | veniladrine extended |
Problem
Query the Products object product__v to retrieve the product fields. Use the document_product__vr relationship to retrieve document fields associated with each product.
Query
SELECT id, name__v, generic__v, (SELECT id, name__v, type__v FROM document_product__vr) FROM product__v
Result
Product ID id | Product Name name__v | Generic Name generic__v | Document ID id | Document Name name__v | Document Type type__v |
|---|---|---|---|---|---|
| 01010 | CholeCap | cholepridol phosphate | 1 | CholeCap Study | Study |
| 02020 | Nyaxa | nitroprinaline oxalate | 2 | Nyaxa Brochure | Promotional Piece |
| 03030 | VeevaProm | veniladrine | 3 | VeevaProm Information | Reference Document |
| 04040 | VeevaProm XR | veniladrine extended | 3 | VeevaProm Information | Reference Document |
Results in List Format (Document to Product / Product to Document)
Discussion
As shown in the two examples above, using the document_product__vr relationship in a nested SELECT-FROM statement allows you to retrieve fields from both objects in a single query. The bidirectional nature of this M:M relationship allows you to place either object in the FROM clause, thereby obtaining nested results in two different ways.
Relationships (and the relationship names to use in queries) are exposed in the Document and Object Metadata APIs for fields of the type ObjectReference where the referenced object objectType is a Vault Object.
Here are the endpoints:
/api/{version}/metadata/objects/documents/properties/api/{version}/metadata/vobjects/{object name}To find relationships on a document or object, search the metadata response for fields with the following attributes (an example is provided in the JSON response below):
type : ObjectReference
objectType : vault_object__v
object__v for standard objects or object__c for custom objects, e.g., product__v, study__v, custom_object__c, etc. relationshipType : reference, reference_inbound, reference_outbound, parent, or child.
relationshipName : relationship_name__vr for standard objects or relationship_name__cr for custom objects.
study_number__vr, sites__vr, document_product__vr, etc.All document-object relationships are defined on the documents object and always take the form document_{field_name}__vr for standard objects or document_{field_name}__cr for custom objects.
For example:
product__v relationship field has the relationship name: document_product__vrcountry__v relationship field has the relationship name: document_country__vrcustom_object__c relationship has the relationship name: document_custom_object__crYou can retrieve the metadata of all document properties by sending a GET request to the /api/{version}/metadata/objects/documents/properties endpoint.
For example:
$ curl -X GET -H "Authorization: {SESSION_ID}" \
https://{CUSTOMER}.veevavault.com/api/{version}/metadata/objects/documents/properties
JSON Response (abridged)
The response contains the list of all document fields (id, name__v, type__v, etc.) configured across entire document type hierarchy. The response shown below lists the details of the product__v relationship field on the documents object. Note that product__v is both a Vault Object and a relationship field on other objects.
{
"name": "product__v",
"scope": "DocumentVersion",
"type": "ObjectReference",
"required": false,
"repeating": true,
"systemAttribute": true,
"editable": true,
"setOnCreateOnly": false,
"disabled": false,
"objectType": "product__v",
"label": "Product",
"section": "productInformation",
"sectionPosition": 1,
"hidden": false,
"queryable": true,
"shared": false,
"definedInType": "type",
"definedIn": "base_document__v",
"relationshipType": "reference",
"relationshipName": "document_product__vr"
}
Discussion
The product__v field is just one of many document fields. By searching the complete list of metadata returned for the Document Metadata API, you can find all relationships configured on the documents object which link documents to other objects in Vault. You can also create custom objects with relationships to documents or other objects. Once you have the relationship names, structure queries by using the relationship names in nested SELECT-FROM statements in either the SELECT clause or WHERE clause. You can also use multiple nested statements in a single query.
With Vault Objects, you can configure various relationships, including parent-child (hierarchical) and reference (non-hierarchical) relationships. Reference relationships can point to another object or back to the same object (self-referencing). Parent-child relationships are typically one-to-one (1:1) or one-to-many (1:M), but you can create a many-to-many (M:M) relationship by using a third (join) object. Learn more about Object Relationships in Vault Help.
This article describes how to set up many-to-many object and document relationships and then using the relationships to construct VQL queries such that a set of records from one parent can be retrieved based on another parent directly. These types of queries are possible by utilizing a third (join) object, which is a child object with two parents.
By using the inbound relationship to the join object from one of the parent objects, you can traverse (navigate) “down” from one of the parent objects to the join object. From there, you can use the outbound relationship from the join object to traverse back “up” to the second parent object. This is all done in a single query using subselect statements.
approved_country__c). This must be done in the Admin UI. Learn how.product_field__c) on the new object. Set the “Field Type” to “Parent Object” referencing the standard object product__v.country_field__c) on the new object. Set the “Field Type” to “Parent Object” referencing the standard object country__v.The new approved_country__c object is referred to as a “Join Object” (a child object of both the product__v and country__v objects).
Note: For the purposes of this article, we’ll refer to product__v as “Parent 1” and country__v as “Parent 2”. These are arbitrary names as both are simply “parent” objects of approved_country__c.
To retrieve the relationship metadata on the approved_country__c (Child) object, send a GET request to the /api/{VERSION}/metadata/vobjects/approved_country__c endpoint.
{
"responseStatus": "SUCCESS",
"object": {
"available_lifecycles": [],
"label_plural": "Approved Countries",
"help_content": null,
"prefix": "A09",
"in_menu": true,
"description": "Child join object with parents product and country.",
"label": "Approved Country",
"source": "custom",
"modified_date": "2015-10-22T16:39:55.000Z",
"created_by": 46916,
"allow_attachments": false,
"dynamic_security": false,
"relationships": [
{
"field": "country_field__c",
"relationship_name": "country_field__cr",
"relationship_label": "Country Field",
"relationship_type": "parent",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/country__v",
"label": "Country",
"name": "country__v",
"label_plural": "Countries",
"prefix": "00C"
}
},
{
"field": "product_field__c",
"relationship_name": "product_field__cr",
"relationship_label": "Product Field",
"relationship_type": "parent",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/product__v",
"label": "Product",
"name": "product__v",
"label_plural": "Products",
"prefix": "00P"
}
}
],
"urls": {
"field": "/api/v13.0/metadata/vobjects/approved_country__c/fields/{name}",
"record": "/api/v13.0/vobjects/approved_country__c/{id}",
"list": "/api/v13.0/vobjects/approved_country__c",
"metadata": "/api/v13.0/metadata/vobjects/approved_country__c"
},
"fields": [
{
"help_content": "Field on the Approved Country object which references the Product parent object.",
"editable": true,
"relationship_deletion": "block",
"label": "Product Field",
"source": "custom",
"type": "Object",
"relationship_outbound_name": "product_field__cr",
"modified_date": "2015-10-22T16:44:01.000Z",
"created_by": 46916,
"required": true,
"relationship_inbound_label": "Approved Countries",
"relationship_type": "parent",
"name": "product_field__c",
"list_column": true,
"modified_by": 46916,
"relationship_inbound_name": "approved_countries__cr",
"created_date": "2015-10-22T16:44:01.000Z",
"status": [
"active__v"
],
"object": {
"url": "/api/v13.0/metadata/vobjects/product__v",
"label": "Product",
"name": "product__v",
"label_plural": "Products",
"prefix": "00P"
},
"order": 10
},
{
"help_content": "Field on the Approved Country object which references the Country parent object.",
"editable": true,
"relationship_deletion": "block",
"label": "Country Field",
"source": "custom",
"type": "Object",
"relationship_outbound_name": "country_field__cr",
"modified_date": "2015-10-22T16:44:25.000Z",
"created_by": 46916,
"required": true,
"relationship_inbound_label": "Approved Countries",
"relationship_type": "parent",
"name": "country_field__c",
"list_column": true,
"modified_by": 46916,
"relationship_inbound_name": "approved_countries__cr",
"created_date": "2015-10-22T16:44:25.000Z",
"status": [
"active__v"
],
"object": {
"url": "/api/v13.0/metadata/vobjects/country__v",
"label": "Country",
"name": "country__v",
"label_plural": "Countries",
"prefix": "00C"
},
"order": 11
},
To retrieve the relationship metadata on the product__v (Parent 1) object, send a GET request to the /api/{VERSION}/metadata/vobjects/product__v endpoint.
"relationships": [
{
"field": "product_field__c",
"relationship_name": "approved_countries__cr",
"relationship_label": "Approved Countries",
"relationship_type": "child",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/approved_country__c",
"label": "Approved Country",
"name": "approved_country__c",
"label_plural": "Approved Countries",
"prefix": "A09"
}
},
To retrieve the relationship metadata on the country__v (Parent 2) object, send a GET request to the /api/{VERSION}/metadata/vobjects/country__v endpoint.
"relationships": [
{
"field": "country_field__c",
"relationship_name": "approved_countries__cr",
"relationship_label": "Approved Countries",
"relationship_type": "child",
"relationship_deletion": "block",
"object": {
"url": "/api/v13.0/metadata/vobjects/approved_country__c",
"label": "Approved Country",
"name": "approved_country__c",
"label_plural": "Approved Countries",
"prefix": "A09"
}
},
Once you’ve set up relationships between the join object and two parent objects (described in Part 1 above), you can start building queries.
In this query, we’re starting from the product__v (Parent 1) object, traversing “down” through the approved_country__c (Child) object, and then back “up” to the country__v (Parent 2) object.
This allows us to retrieve fields and values from all three objects in a single query.
SELECT id, name__v, (SELECT name__v, local_name__c, approval_status__c, country_field__cr.name__v, country_field__cr.external_id__v FROM approved_countries__cr) FROM product__v
This query can be broken down into the following steps:
id and name__v from all products. The product__v (Parent 1) object is the “driver object” of this query.approved_countries__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.approved_countries__cr relationship in a subselect to retrieve the name__v and external_id__v (using dot-notation on the country_field__cr relationship) from the country__v (Parent 2) object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 12,
"total": 12
},
"data": [
{
"id": "00P000000000101",
"name__v": "Nyaxa",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"country_field__cr.name__v": "Spain",
"country_field__cr.external_id__v": "SPA-014"
},
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"country_field__cr.name__v": "Italy",
"country_field__cr.external_id__v": "ITA-015"
}
]
}
},
{
"id": "00P000000000102",
"name__v": "Gludacta",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"country_field__cr.name__v": "United States",
"country_field__cr.external_id__v": "USA-012"
},
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"country_field__cr.name__v": "Italy",
"country_field__cr.external_id__v": "ITA-015"
}
]
}
},
In this query, we’re starting from the country__v (Parent 2) object, traversing “down” through the approved_country__c (Child) object, and then back “up” to the product__v (Parent 1) object.
SELECT id, name__v, (SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, product_field__cr.external_id__v FROM approved_countries__cr) FROM country__v
This query can be broken down into the following steps:
id and name__v from all countries. The country__v (Parent 2) object is the “driver object” of this query.approved_countries__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.approved_countries__cr relationship in a subselect to retrieve the name__v and external_id__v (using dot-notation on the product_field__cr relationship) from the product__v (Parent 1) object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 17,
"total": 17
},
"data": [
{
"id": "1357662840400",
"name__v": "United States",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"product_field__cr.external_id__v": "GLU-00577"
}
]
}
},
{
"id": "1357662840582",
"name__v": "Italy",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"product_field__cr.external_id__v": "NYA-00278"
},
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"product_field__cr.external_id__v": "GLU-00577"
}
]
}
},
{
"id": "1357662840631",
"name__v": "Spain",
"approved_countries__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"product_field__cr.name__v": "Nyaxa",
"product_field__cr.external_id__v": "NYA-00278"
}
]
}
},
To create a many-to-many relationship between documents and a join object:
approved_country__c). This must be done in the Admin UI. Learn how.approved_country__c, which is a child of both the product__v and country__v fields.To see the relationship metadata on the documents object, send a GET request to the /api/{VERSION}/metadata/objects/documents/properties endpoint.
{
"responseStatus": "SUCCESS",
"properties": [
{
"name": "approved_country__c",
"scope": "DocumentVersion",
"type": "ObjectReference",
"required": false,
"repeating": false,
"systemAttribute": false,
"editable": true,
"setOnCreateOnly": false,
"disabled": false,
"objectType": "approved_country__c",
"label": "Approved Country",
"section": "generalProperties",
"sectionPosition": 1000,
"hidden": false,
"queryable": true,
"shared": false,
"helpContent": "Document field which references the child join object Approved Country.",
"definedInType": "type",
"definedIn": "base_document__v",
"relationshipType": "reference",
"relationshipName": "document_approved_country__cr",
"controllingField": "product__v"
},
Once you’ve set up relationships between documents and the join object (described in Part 3 above), you can start building queries.
In this query, we’re starting from the documents object, traversing “across” through the approved_country__c (Child) object, and then back “up” to the product__v (Parent 1) and country__v (Parent 2) objects.
SELECT id, name__v, type__v, (SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, country_field__cr.name__v FROM document_approved_country__cr) FROM documents
This query can be broken down into the following steps:
id, name__v, and type__v from all documents. The documents object is the “driver object” of this query.document_approved_country__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.document_approved_country__cr relationship in a subselect to retrieve the name__v (using dot-notation on the product_field__cr relationship) from the product__v (Parent 1) object.document_approved_country__cr relationship in a subselect to retrieve the name__v (using dot-notation on the country_field__cr relationship) from the country__v (Parent 2) object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 201,
"total": 201
},
"data": [
{
"id": 381,
"name__v": "Nyaxa and Your Health",
"type__v": "Promotional Piece",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Italy"
}
]
}
},
{
"id": 382,
"name__v": "Nyaxa Information Packet",
"type__v": "Claim",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Spain"
}
]
}
},
{
"id": 383,
"name__v": "Nyaxa Prescribing Information",
"type__v": "Reference Document",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Italy"
}
]
}
},
{
"id": 384,
"name__v": "Gludacta Brochure",
"type__v": "Promotional Piece",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "United States"
}
]
}
},
{
"id": 385,
"name__v": "Gludacta Information",
"type__v": "Reference Document",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "Italy"
}
]
}
},
{
"id": 386,
"name__v": "Gludacta Prescribing Information",
"type__v": "Reference Document",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "United States"
}
]
}
},
In this query, we’re starting from the approved_country__c (Child) object, traversing “across” to the documents object, and also “up” to the product__v (Parent 1) and country__v (Parent 2) objects.
SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, country_field__cr.name__v, (SELECT id, name__v, type__v FROM document_approved_country__cr) FROM approved_country__c
This query can be broken down into the following steps:
name__v, local_name__c, approval_status__c from the approved_country__c object. The approved_country__c object is the “driver object” of this query.product_field__cr relationship to retrieve the product name__v field value from the approved_country__c object. This field maps to the product__v object.country_field__cr relationship to retrieve the country name__v field value from the approved_country__c object. This field maps to the country__v object.document_approved_country__cr relationship in a subselect to retrieve the id, name__v, and type__v from the documents object.{
"responseStatus": "SUCCESS",
"responseDetails": {
"limit": 1000,
"offset": 0,
"size": 4,
"total": 4
},
"data": [
{
"name__v": "Italy",
"local_name__c": "Gludasom",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "Italy",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 385,
"name__v": "Gludacta Information",
"type__v": "Reference Document"
}
]
}
},
{
"name__v": "Italy",
"local_name__c": "Nyza",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Italy",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"id": 381,
"name__v": "Nyaxa and Your Health",
"type__v": "Promotional Piece"
},
{
"id": 383,
"name__v": "Nyaxa Prescribing Information",
"type__v": "Reference Document"
}
]
}
},
{
"name__v": "United States",
"local_name__c": "Gludacta",
"approval_status__c": "Approved",
"product_field__cr.name__v": "Gludacta",
"country_field__cr.name__v": "United States",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 2,
"total": 2
},
"data": [
{
"id": 384,
"name__v": "Gludacta Brochure",
"type__v": "Promotional Piece"
},
{
"id": 386,
"name__v": "Gludacta Prescribing Information",
"type__v": "Reference Document"
}
]
}
},
{
"name__v": "Spain",
"local_name__c": "Nyasená",
"approval_status__c": "Pending",
"product_field__cr.name__v": "Nyaxa",
"country_field__cr.name__v": "Spain",
"document_approved_country__cr": {
"responseDetails": {
"limit": 250,
"offset": 0,
"size": 1,
"total": 1
},
"data": [
{
"id": 382,
"name__v": "Nyaxa Information Packet",
"type__v": "Claim"
}
]
}
}
]
}
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:
The image below shows the Dynamic Security Rule Criteria in the Vault UI.
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.
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.
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.
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 behavior varies slightly between Documents, Objects, and 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”.
If you supply a value that is not a valid label, VQL will treat the result as undefined. This means inequalities will return nothing. For example, workflow_type_v {=,>,<} 'Invalid Label' would all return nothing because 'Invalid Label' is not a valid value of the picklist.
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”.
If you supply an invalid value for label, VQL will treat the label as a string. This means inequalities will still operate and return results for invalid picklist values. For example, if a picklist named p contains values {'k', 'g', 'a'}, then p < 'h' would evaluate to true for values 'a' and 'g'.
To query object picklists, do not enter picklist value labels as shown in the object record details. Instead, 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.
If you supply an invalid value for label, VQL will treat the label as a string. This means inequalities will still operate and return results for invalid picklist values. For example, if a Picklist named p contains values {'k', 'g', 'a'}, then p < 'h' would evaluate 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' |
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.
vault_object__v), the lookup name is vault_object__vr.name__v.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.
All Dates and DateTimes are entered and returned in UTC (Coordinated Universal Time) and not the user’s time zone.
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 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 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 |
The following applies to dynamic constraints. For static reference constraints, see above.
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}}. See the table below for examples.
| Description | Controlling Field Location | Field to Constrain (Controlled Field) | Criteria VQL |
|---|---|---|---|
| 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}}) |
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'
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.