¶ Query Builder
After you create your Document Solution, it's time to decide which data you want to show in the document. That's where Query Builder comes in. Query Builder is a powerful tool that lets you build your queries without a single line of code. It's simple to use and build queries just like you would in standard SOQL, except it's much easier.
¶ Getting Started with Query Builder
You can access Query Builder by opening your Document Solution record and selecting the Query Builder tab.
Click on the Add Query button to create a new query. Let's have a quick rundown of all the Query Builder's features:
- Query Variables. In Query Builder, you can use variables instead of specific values in the GraphQL Filter. Variables take on and represent a single value of a standard type. They should be used when a value has to be inserted into the query in multiple spots as you only have to define them once. Along with variables of the many types that you can add yourself, you also get three generic variables that you can use:
- recordId. The ID of the record from which you will be generating your document.
- currentUser. The ID of the current user in Salesforce.
- requestId. The ID of the Document Request is used to generate the document.
- Global Variables. Even though Global Variables are placed just under Query Variables, they are serving different purposes:
- Global Variables store a value that would be referenced in the template document and are not being used as a filter in the query.
- Global Variables can be used in the Expression Fields as well.
- Global Variables and Query Variables are referenced differently.
More information is in the Global Variables section.
- Main Information. This is where you select from which object you want to query records, and optionally add an alias as well.
- First. Using a numeric value or variable, you can choose how many records you want to have in your result. For the selected value of n, the query will return the first n records from the result.
- Offset. You can also add an offset value or variable to your result to skip the first n number of records.
- Sort. You can use it for sorting query results by some field.
- Fields. Select the fields you want the query to return in the result. You can also select fields from a related object (i.e. Account.FirstName while querying Contacts).
You can switch the toggle and instead of adding a Salesforce Field, you can build and add an Expression Field. More information on Expression Fields is in the separate section. - Filter. Filter the records based on the set of criteria you select. Click Add Condition to add criteria on certain fields by comparing them to a variable or a specific value. Then, if you have multiple conditions, click Add Group and customize the logic using standard logical operators.
- Preview. You also get a preview of your query in the GraphQL language, but you won't focus on that here.
- Actions:
- Save. Save your current query.
- Add Sub-Query. Add a nested query from one of the related lists on the selected object (i.e. while querying Accounts, you can add a sub-query for all of that Account's Contacts).
- Delete. Delete the current query.
That's all you need to know before using Query Builder. Using all of the Query Builder components mentioned above, you can get all the data you'll need to generate your document.
¶ Build your queries
Now that you know how to use Query Builder, you can get started on building queries for the quote document. Let's build a dynamic query that will help create a Sales Quote for your customers in seconds. You will start with pulling customer Opportunities data:
-
Click Add Query to create a new query.
-
In the Main Information section, select the Opportunity object.
-
In the Fields section, add the following fields:
- Id
- Name
- CreatedDate
- CloseDate
- Amount
-
You will be generating the document from the Opportunity record page (more on that in the Generate documents from record pages section). That's why you use the Opportunity record from which you are generating the document. That's where the recordId variable comes in handy. In the Filter section, add the following condition:
- Field: Id
- Operator: Equals
- Type: Variable
- Variable: recordId
We recommend using Filter so you don't hit Salesforce limits.
Click Save. After the query is completed it should look like this:
That's the first query! You now have the Opportunity record from which you are generating the document. However, you are also going to have all the Products related to that Opportunity. To get that, you'll build a sub-query on the OpportunityLineItems related list:
-
Click Add Sub-Query.
-
In the Main Information section, select the OpportunityLineItems relationship.
-
In the Fields section, add the following fields:
- Id
- Name
- Quantity
- UnitPrice
- Total Price
-
Click Save and after that, your sub-query should look like this:
The next piece of information you are going to show in the Sales Quote document will be all Opportunity Contacts Roles that are relevant for closing a deal. To get that, similar to the OpportunityLineItems sub-query, you will build a new subquery:
- Click back to the Opportunity query.
- Click Add Sub-Query.
- In the Main Information section, select the OpportunityContactRoles relationship.
- In the Fields section, add the following fields:
- Id
- Contact.Name
- Contact.Phone
- Contact.Email
- Role
- Click Save and after that, your sub-query should look like this:
The template that you will be using also requires the Account object. There are two ways to get the required record. You can get it directly over the Opportunity record by adding its fields to the query, or you can get it as a standalone record using the Opportunity record's AccountId field as a filter.
- Create a new variable named accountId.
- Under Type select Id.
- In the Value field, put {!Record.AccountId}.
- Click on the Save button.
Now you have a variable that holds the Account's ID related to the Opportunity record for which the document is going to be generated.
The next step is creating the query that gets the Account record.
Note: You won’t be able to select such compound fields as Address or Geolocation in Query Builder. But this does not mean that the fields that are included in the compound field are forbidden. For example, BillingAddress will not be provided as an option on the Account object, but BillingStreet, BillingCity, and the others are.
- Click on the Add Query button.
- Under Object enter Account.
- In the Fields section, add the following fields:
- Id
- Name
- BillingStreet
- BillingCity
- BillingPostalCode
- BillingCountry
- In the Filter section, add the following condition:
- Field: Id
- Operator: Equals
- Type: Variable
- Variable: accountId
Save the query, after which it should look exactly like this:
The next step is creating the query that gets the Organization record.
- Click on the Add Query button.
- Under Object enter Organization.
- In the Fields section, add the following fields:
- Name
- Street
- City
- PostalCode
- Country
Save the query, it now looks like this:
Now, you are going to have information about the User who owns Opportunity (let's call him Sales Agent). For that, you first have to get the Owner's ID from the Opportunity record and save it into a Variable:
- Create a new variable named ownerId.
- Under Type select Id.
- In the Value field, put {!Record.OwnerId}.
- Click on the Save button.
Then you have to add one more query on the User object.
Note: Unsupported characters and spaces will be automatically removed while you write down Aliases for objects or fields.
Supported characters are the Latin letters in upper and lower cases and digits from 0 to 9 are included.
-
Click Add Query.
-
In the Main Information section select the User object and type the alias SalesAgent.
-
In the Fields section, add the following fields:
- Id
- Name
- Phone
-
In the Filter section, add the following condition:
- Field: Id
- Operator: Equals
- Type: Variable
- Variable: ownerId
Save your SalesAgent query (it should look like this):
That's it! Using Query Builder you've collected all the data you need for the quote document. Now, you can build a Document Template in Google Docs or Google Sheets connect it to Document Solution in Maven Documents, and fill it with data you have just acquired with Query Builder. You can also build a Document Template using Microsoft Word or Excel, and transfer and use queries defined in Query Builder.
¶ Query Variables
Variables are used to store values of standard types which cover all types needed for document generation. The value stored comes either straight from the user or is a value of a field of a record inside the Salesforce app. A complete list of types:
- Id - IDs of Salesforce records
- String - Any standard String
- Boolean - Either the True or False value
- Date - Any Date
- DateTime - Both the Date and the Time
- Time - Only the Time of day
- Integer - Any whole number
- Decimal - Any number
- Formula - Any Salesforce formula
To create a variable press the Add Variable button located under the query elements on the left side of the application. After that, a popup appears which just needs to be filled with desired values. Then, clicking on the save button saves the variable inside the query and makes it available for further use inside documents linked to that query.
A dissection of the popup is explained below:
- Name. Text is used to identify the variable inside a document.
- Type. One of the 9 available types of variable which can hold value.
- Value. The value stored inside the variable, that shows up in the document.
They can be used for a range of things and they allow users to have one source of value for anything which needs that value inside the query.
¶ Global Variables
As was mentioned earlier, you can create Global Variables in Query Builder on Salesforce, and reference them inside Document Template. This, together with Expression Fields can make your Templates look neat.
But first, let's see how you can create one. After you reach the Query Builder tab, click the Add Global Variable button in the Global Variable section. The following window opens.
- In this field, you will name the Global Variable. The name can be descriptive and based on the value of the variable.
- Here, you can define the value of the Global Variable will contain. You can use Expressions, References, or just regular text.
- If you need help building an Expression, you can use Expression Builder by clicking on the Open Expression Builder button. Here is the instruction on using Expression Builder.
After you finish with all the fields, click Save and the created variable will appear in the corresponding section on Query Builder work pane. After running Template Builder on your document, you will find created variables alongside Salesforce fields. You will also be able to use these in Expression Fields.
¶ Expression Fields
With the Expression Field, you can create and add Expressions while building a query. These expressions will become a part of the query and appear on Template Builder alongside the fields of the corresponding Object.
You can think of Expression Fields as formulas on Salesforce, but rather than creating a new Formula Field in the Object modal, you can create an Expression Field, which will evaluate like our Expressions are evaluating regularly, and you will then be able to use it in Template Builder - just like you would any other regular Salesforce Field. Expression Fields only have access to the record they are located on (and everything below them). However, you can define part of the data in Global Variables (which have visibility to everything) and then reference such Global Variables in Expression Fields. For all of the developers out there, think of Global Variables as getters of Query data for Expression Fields.
Here is a quick instruction on how to add an Expression Field.
-
After you select an object in Query Builder, switch the toggle next to the Add Salesforce Field signature in the Fields section.
-
In the Expression Name field, write down the name of the expression.
Expression Value, can either be an Expression written manually or with Expression Builder by clicking on the corresponding icon.
Note that the Select Object or Field section of Expression Builder already has the starting sub-query selected. You can only continue down from that. (Remember that Expression Fields only see the record they are currently on and everything below that.
After you build the expression, the Expression Field will appear in the Fields section with other added fields from the Object.
When you've saved the query, its preview will appear in the corresponding section. The built expression will be displayed there as well.
Before placing the expression in the document, you can evaluate it in Playground:
- Go to the Playground tab on Document Solution.
- In the Variables section, replace the {!record. Id} variable with the ID of the needed opportunity
- Refresh Context.
- In the Expression section, open Expression Builder, and in the Select Query or Field section, select the corresponding Expression Field.
After all the steps from above, the Expression Field will appear in the Expression section in the {!$Object.Expressionfield} notation.
Expression Fields are referenced as references!
So make sure to use notation such as {!Object.ExpressionFieldName}, and not {!$Object.ExpressionFieldName}. The first will resolve the Expression Field, and the latter will output just the content of it.
Now, you can go to Document Template, run Template Builder, and insert Expression Field inside the document by clicking on it.