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 (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.

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