When using Merge Fields, you can include a WHERE
clause or an ORDER BY
clause in a token for a multi-select object reference field to allow Vault to filter on multiple data values. WHERE
and ORDER BY
clauses support VQL-type expressions. Learn more about creating VQL expressions in the Developer Portal.
Important: Filtering is not supported when using Merge Fields in Microsoft Excel.
Token Syntax
To use record filtering or sorting, use a multi-select object reference field token and append the WHERE
, ORDER BY
, or both VQL-type expressions to the end, separated by a semicolon. Tokens follow this pattern:
WHERE Token
${vault:relationshipname__vr.relationshipname__vr.fieldname__v;WHERE VQL_Expression}
For example,
${vault:document_quality_event__cr.findings__cr.name__v;WHERE findings__cr.criticality__c = 'Major'}
would return a filtered list of Findings related to the Quality Event, with a Criticality value of Major
.
With the WHERE
clause, Vault can filter across one or more related objects from the root object specified in a document field, which is the first object listed in your token. You can create tokens with up to five (5) relationships. All portions of the WHERE
clause should refer to a single object. In the example above, Quality Event is the root object and Findings is the related object.
ORDER BY Token
Optionally, you can use an ORDER BY
clause to sort the values returned by the token using the VQL operators ASC or DESC. Follow the clause with ASC
for ascending order, or DESC
for descending order. If you do not follow the clause with an ASC
or DESC
operator, the default sorting order is ascending. Note that you can only sort by the data returned in the token, or a data field returned within a table.
You can also include an ORDER BY
clause in a token for a multi-select object reference field to allow Vault to order the displayed fields.
For example,
${vault:document_quality_event__cr.lead_auditors__cr.name__v;ORDER BY lead_auditors.name__v ASC}
would return a list of Lead Auditors related to the Quality Event, sorted by name in ascending alphabetical order.
Limitations
The following limitations affect WHERE
clauses with Merge Fields:
- You cannot filter on the root object.
- You can only filter on objects with outbound relationships.
Note that ORDER BY
clauses, when used in combination with a WHERE
clause, are subject to the same limitations.
Record Filtering
With the WHERE
clause, Vault can filter on multiple data values. WHERE
clauses can support multiple conditions, for example, filtering for Signatures where the Status is Current and the Signature Type is Final Approval.
You can use the following standard VQL operators in WHERE
clauses:
=
!=
<
>
<=
>=
AND
OR
CONTAINS
BETWEEN {} AND {}
LIKE {}
Note that Vault does not support the IN
operator for merge field clauses.
Note: When a query in a WHERE
clause token uses YES/NO
values instead of true/false
, Vault returns the opposite result.
Filtering & Sorting with Rich Text
When merging Rich Text fields in Microsoft Word files, you’ll need to properly use Rich Text token syntax to ensure tokens resolve and display using rich text formatting.
To use record filtering, tokens should follow this pattern:
${vault:relationshipname__vr.relatedobject__vr.rich_text_fieldname__v;WHERE relatedobject__vr.fieldname__v = 'Value';richtext}
To use record sorting, tokens should follow this pattern:
${vault:relationshipname__vr.relatedobject__vr.rich_text_fieldname__v;ORDER BY relatedobject__vr.fieldname__v;richtext}
Vault resolves any Rich Text token syntax in non-Rich Text fields as plain text.
Filtering & Sorting with Tables
Vault supports tokens with WHERE
and ORDER BY
clauses within tables:
Finding | Criticality | Category | Sub Category |
${vault:document_quality_event__cr.findings__crname__v;WHERE findings__cr.criticality__c = 'Major' ORDER BY findings__cr.criticality__c DESC} |
${vault:document_quality_event__cr.findings__cr.criticality__c} |
${vault:document_quality_event__cr.findings__cr.finding_category__c} |
${vault:document_quality_event__cr.findings__cr.finding_subcategory__c} |
If the table has a row of tokens that includes two unrelated multi-value object reference fields or different WHERE
clauses, Vault populates the first field as rows but subsequent fields as lists.
Merge Fields also supports WHERE
and ORDER BY
tokens within nested tables up to three levels:
Name | Status | Country | |
${vault:document_product__vr.name__v; ORDER BY product_vr.status__v DESC} |
${vault:document_product__vr.status__v} |
${vault:document_country__vr.abbreviation__vs; ORDER BY country__vr.abbreviation__vs} |
${vault:document_country__vr.name__v} |
Name | Status | Country | |
WonderDrug | Active | US | United States |
Nyaxa | Active | CA | Canada |
US | United States | ||
Cholecap | Active | AU | Australia |
CA | Canada | ||
US | United States |
Multiple Column Table Sort
Vault also supports sorting on multiple columns in a table up to a maximum of three (3) columns. To sort using multiple columns, add a token using the ORDER BY and/or WHERE clause to the first column of the table. The clauses support VQL statements sorting by up to three (3) fields. Vault sorts fields based on their specified order in the token.
To sort on multiple columns in a table, tokens should look like this:
${vault:document_relationshipname__vr.objectname__vr.fieldname__v;ORDER BY objectname__vr.fieldname__v ASC|DESC, objectname__vr.fieldname2__v ASC|DESC, objectname__vr.fieldname3__v ASC|DESC}
For example,
Country | Province/State | City |
${vault:document_city__cr.province__cr.country__cr.name__v;WHERE country__cr.name__v='Canada' OR country__cr.name__v='United States' ORDER BY country__cr.name__v ASC, province__cr.name__v DESC, city__cr.name__v}
|
${vault:document_city__cr.province__cr.name__v}
|
${vault:document_city__cr.name__v}
|
Country | Province/State | City |
Canada | Québec | Montreal |
Canada | Ontario | Toronto |
Canada | Nova Scotia | Halifax |
United States | New York | Buffalo |
United States | New York | New York |
United States | California | Los Angeles |
United States | California | Pleasanton |
United States | California | San Diego |
Filtering with Barcodes
When you apply a barcode to a token with a WHERE
clause, Vault uses the filtered result to create the barcode. To add a barcode to your token, append the barcode portion after the WHERE
clause, separated by a semicolon.
For example,
${vault:document_quality_event__cr.findings__cr.name__v;WHERE findings__cr.criticality__c = 'Major';barcode}
Sample Configurations
Co-Auditors
When creating a report, your source document contains tokens identifying a Lead Auditor and Co-Auditors:
Lead Auditor: ${vault:document_quality_event__cr.lead_auditor__c}
Co-Auditor: ${vault:document_quality_event__cr.audit_team__cr.team_member__c}
However, the Team Member object contains users of all types, and the generated rendition lists both Co-Auditors and Observers, and does not list Co-Auditors in any logical order:
Lead Auditor: Teresa Ibanez
Co-Auditors:
- Observer: Gladys Dunford
- Co-Auditor: Thomas Chung
- Co-Auditor: Cody Brandon
You can use a WHERE
clause in your token to filter by Team Member Type so the rendition does not include Observers in the list of Co-Auditors, and an ORDER BY
clause so that they are listed in alphabetical order:
Co-Auditor: ${vault:document_quality_event__cr.audit_team__cr.team_member__c;WHERE audit_team__cr.team_member_type__c = 'Co-Auditor' ORDER BY audit_team__cr.team_member__c ASC}
The updated generated rendition would then list:
Lead Auditor: Teresa Ibanez
Co-Auditors:
- Co-Auditor: Cody Brandon
- Co-Auditor: Thomas Chung