# Filtering & Sorting with Merge Fields 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.
## 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 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][1]. 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.
## 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 {#filtering-and-sorting-with-tables} Vault supports tokens with `WHERE` and `ORDER BY` clauses within tables. When using tokens within tables, the first token in a table row drives the row count and data context for all subsequent tokens in the row. In addition, `WHERE` or `ORDER BY` clauses on the first token apply to the entire row.
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 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 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 [1]: #filtering-and-sorting-with-tables