¶ XLSX Templates
Maven Documents Template Builder add-in for XLSX files is a powerful tool that helps you build an Excel sheet with data from Salesforce.
¶ Installation Process
You can use the add-in in both the online and offline Microsoft Excel versions. But here is the difference in installation.
¶ Online version
Follow the next steps to install Maven Documents Template Builder on the online version of Microsoft Excel.
-
Go to Office.com or Microsoft OneDrive and log into your Microsoft account.
-
Create a new Excel book or open an existing one.
-
On the "Home" tab in the Excel taskbar click on Add-ins.
-
In the dropdown menu click the "+More Add-ins" button.
-
It opens the Office store. Search for Maven Documents Template Builder there.
-
Select the "Template Builder" add-in and you'll be redirected to the add-in's page. There you can find some useful links and information. Then click the "Add" button.
-
Read and if you agree with the terms and conditions, check the respective box. Click Continue.
-
You'll see the "Welcome" message with some useful links. Click Let's go.
Voilà! The "Maven Documents Template Builder" add-in now could be used on the online XLSX file.
¶ Offline version
Follow the next steps to install Maven Documents Template Builder on the offline version of Microsoft Excel.
-
Go to appsource.microsoft.com/en-us/product/office/WA200004491 and click Get it now.
-
After the installation is complete, click Open in Excel.
-
When Microsoft Excel opens, you'll see the standard Microsoft page with quick instructions on launching the new add-in. Click on the "Trust this add-in" button.
-
You'll see the message that Template Builder is successfully loaded and the logo of the add-in appears in the Excel taskbar.
That's it! Now you have Maven Documents Template Builder installed!
¶ Sandbox Version
For testing Maven Documents features in Sandbox Orgs, we've made a separate "Template Builder" add-in.
You can install the add-in by following the steps we provided above for the Online and Offline versions. The only difference is to select the "Maven Documents Template Builder (sandbox)" add-in.
¶ Run Template Builder
The first thing that you should do before building the template is to run the "Template Builder" add-in on your XLSX file and log into the Salesforce account.
-
Run Microsoft Excel and create a new or open an already existing book.
-
In the "Home" tab click on the "Add-ins" icon and select Maven Documents from My
-
You'll see the "Welcome" message with some useful links. Click Let's go.
-
After the Maven Documents task pane appears click on the "Login to Salesforce" button that looks like a gear.
-
A new dialogue window should open, offering two login options:
- Log in to Production
- Log in to Sandbox
Select where you want to log in.
We recommend you log in to Sandbox first to see if everything is working fine.
-
In the following dialogue window put in your credentials and click Log in.
If this is your first time logging into the Salesforce account with Template Builder, user verification is required from the Salesforce side. You'll receive an email with the verification code which you should enter in the required field.
-
Click Allow.
-
In the last dialogue window select the Document Solution, you would like to use.
-
After you select the solution you can select the Document Template to which you will upload the document.
-
If you like to upload the file you are working on to the selected template, check the question box. Otherwise, leave it blank.
You can upload the file later with Template Builder. -
Click Done.
Under Solution/Template Select you'll see the information to which org you've connected Template Builder.
Once you finish the log-in process, you'll see that template Builder reflects the query from the connected Document Solution.
As the taskbars in online and offline versions of Microsoft Excel are almost the same, the process of running the add-in on the offline version will be similar.
¶ Meet Template Builder
Once you are done with the login process, you can finally use Template Builder.
Before you start, let's have a glance at what we call the "Template Builder" work pane. Let's divide it into two parts: the "work" part and the "administration" part.
¶ The "work" part includes:
-
The "{} Fields" tab.
Here you will find the objects and the fields you added to the query on Document Solution. All objects and fields can be inserted into the document with one click.
-
The </> Elements tab.
Here you have a picklist with the elements that could be inserted and used in the document. For detailed information on every element, you can check the "XLSX Elements Reference" page.
-
The fx or Expression Builder tab.
Expression Builder lets you create expressions (similar to formula fields in Salesforce) without needing a formula field directly on your document. You can read more about expressions and their syntax on the "Expressions" page.
The icon in the bottom left of the expressions input field opens Expression Builder.
First of all, you can see all available functions in the "Select function" menu. The first dropdown menu filters functions by categories (date, number, string, etc.), and the second dropdown menu contains functions you can use.
You can also insert your Salesforce fields into expressions by using the dropdown in the "Insert field" menu (an arrow (>) next to a field suggests that it's a relationship field - e.g. Account > Contact > ...).
After the function and fields are inserted, they appear in the text field below. So you can edit the expression manually or use the expression builder again.
Before inserting the function or a field, verify that you've highlighted the text you want to replace in the text field below
¶ The "administration" part includes:
-
The "Refresh Nodes" button.
This button serves two purposes:- To reflect changes made on the Salesforce side. For example, you've changed the name of an object or edited the query in Query Builder. After clicking "refresh", you will see the changes in Template Builder.
- The second purpose is to refresh the context inside the document to see fields from sub-queries. For example, if the cursor is placed inside a repeater or table, which iterates through a record, its name and included fields will also appear in the list fields on Template Builder after refreshing.
-
The "Login to Salesforce" button.
You are already familiar with this one. It helps to connect the Salesforce org with Template Builder and select what Document Solution and Document Template to use. -
The "Upload File to Template" button.
Click on this button to upload the DOCX file you are working on to Document Template, that you selected in the Login to Salesforce step.After changes are made in the DOCX file you need to upload it to Document Template. Because once the file is uploaded to Salesforce it doesn't refresh automatically. If you want to have the up-to-date version, you need to upload it after changes are made.
¶ Build Templates using Template Builder for XLSX files
Now, that you know how to install and run Template Builder on the XLSX file, it is time to create and modify the template document using all the Template Builder abilities.
Let’s see how we create the "Sales Quote" document. You can use the created Document Solution from Template Gallery as a reference.
¶ Adding fields and expressions
In our example, we are using the cell in the second row and the "B" column, and further examples will consider that location. But you can start with whatever cell you like.
Write down the header of the document and place the cursor in the cell below, so you can insert the fields there.
One cell can hold only one merge field.
First of all, you need to log in to Salesforce to have the up-to-date query in Template Builder.
-
In the first page's header, you can place some information about the organization, for example, the name and address. All these fields you can select from Template Builder.
-
Then, we use the "Name" field from the "Opportunity" Object to show from which opportunity you will take the data. Select the "{}Fields" tab and click on the "Opportunity" Object. In the dropdown list of fields, click on the "{}Name" field. So, in the cell under the header of the document, you will have the following merge field {!Opportunity. Name}.
-
Then use "Name" and "Billing Address" fields from the "Account" Object to indicate who will use the quote. Also, insert the "CreatedDate" field directly from Opportunity to show when the quote was prepared.
All the fields we've put using the "{!QueryName.FieldName}" notation are dynamic. It means they depend on the results that our queries returned.
-
After you have the prepared date in the document, you need to have the expiration date in your quote. For example, it expires 30 days after the quote (document) has been generated. So let’s use some magic to do that. By magic, we mean Expressions.
-
Select the "fx" tab on the "Template Builder" work pane. As you remember, this one stands for Expression Builder.
-
Open Expression Builder by clicking on the icon in the bottom left of the expressions input field.
-
In the "Select function" section select the "Format" function type and the "format date" function. Click Insert. That function makes the date look in the "dd/mm/yyyy" format.
-
Highlight the "DateField" in the text field below, and select the "Date" function type and the "addDays" function in the "Select function" section. Click Insert.
-
Now you need to replace DateTime in the expression with the current date. From the Select function menu, choose the "Date" function type and the "now()" function. This function reflects the current date and time.
-
Highlight DateTime in the text field below and click Insert. This action will replace DateTime with the "now()" function.
-
Replace NumberOfDays with 30.
-
The last thing to do is to change Format to short.
The result should look something like this.
-
Click Insert Expression and then Insert Expression one more time.
-
If needed, you can use Excel formulas with merge fields from Template Builder as building blocks. The result of such formulas is shown only after the template is generated and the "Edit" mode of the generated file is activated.
¶ Inserting elements
The next step you need to do is to show the information about our Opportunity's products.
If you remember, in the "Query Builder" article, you added OpportunityLineItems as a sub-query. For the sub-query to be available in Template Builder, you need to be in a specific context. Sub-queries can only be accessed from inside of an element that belongs to its parent query, for example, Repeater. This is where the "Elements" part kicks in.
Elements are building blocks of every document and sheet. They allow Maven Documents users to turn a simple Excel sheet into a rich, content-filled document that can satisfy the needs of a whole range of users. Elements are written in the HTML-like code directly on the document template. More information on what are Elements and how to write them can be found here.
As you can notice, there is no Table element in the "Element Type" list. Because an Excel sheet is a table itself, instead of Table you will use the "Repeater" element to show the information about the products.
In XLSX files, you can use two types of Repeater:
- Vertical repeater
- Horizontal repeater
The type of Repeater must be specified under the "Direction" attribute.
Repeater is an iteration component that allows you to output the contents of a collection according to a structure that you specify because the whole body of the "Repeater" element will be shown for every record in the collection.
- Write down the headers of every column of the table starting with column B.
- Place your cursor in the cell where you want your products to be shown. Then, in Template Builder open the "Elements" tab.
- In the "Element Type" field select the "Repeater" element.
- Fill the fields with the following values:
Name: line-items
Value: Opportunity.OpportunityLineItems
Variable: oli
Direction: Vertical
Last Cell: F - Leave the "Hidden" parameter untouched and click Insert Element.
Have a look at the screenshot.
Now, when you have the header of the table and Repeater inserted, it is time to fill it with data.
- Place the cursor in the cell inside Repeater and click the "Refresh" button on Template Builder. It gets the fields from the "oli" variable shown in the "Fields" tab in Template Builder.
- Insert fields into their corresponding cells in the row inside Repeater.
- In the bottom row put "Opportunity Amount", by adding the field from Template Builder.
- Make some cosmetic changes, like changing the color of the rows and making the font bolder.
The table should look like this.
All the Repeaters you add to the document should have different names, so the data in the generated document is shown properly.
¶ Continuing the modification with known tools
The next part of the template is content related to terms and conditions. With the power of Microsoft Excel, you can edit the content. Using Template Builder's features, you will add a few placeholders to dynamically populate the account name and delivery period, for example.
Following the terms and conditions, the "Payment Details" section appears in the template.
Again, the combination of Microsoft Excel edit features alongside Template Builder abilities helps you create the required template with all the data you need.
Another part of the document will contain the contact details of a sales agent and a customer.
Add the sales agent's contact details by using the respective fields from Template Builder. To insert the customer contacts you can use the already-known "Repeater" element:
- Place your cursor in the cell you want the information to be shown. Then, in Template Builder open the "Elements" tab.
- In "Element Type" fields select the "Repeater" element.
- Fill fields with the following values:
Name: contacts
Value: Opportunity.OpportunityContactRoles
Variable: con
Direction: Vertical
Last Cell: F - Leave the "Hidden" parameter as it is and click Insert Element.
When Repeater is inserted, fill the corresponding cells with the needed data.
- As in the previous example, the first row will be the header row. So, populate its cells with the names of the fields.
- Put the cursor in the second row and refresh the query. With that move, you'll have the fields from the sub-query appear in Template Builder.
- Insert needed fields inside the respective cells of the table.
In the screenshot below you can see what you could have as a result.
We also should mention the opportunity to remove or hide any rows or columns in terms of your needs. There are Row and Column elements that can do that for you.
-
Select the "Row" or "Column" element in Template Builder.
-
If you want to remove the row or the column leave the "Collapse Only" checkbox empty.
-
If you want to hide but not remove the row or the column, then mark the "Collapse Only" checkbox.
-
Insert the element in the corresponding row or column and generate the template.
So the provided element removes the selected column..
..and the element below only hides the defined row.
With all the steps from above you can create any Document Template in Microsoft Excel.
¶ Uploading the XLSX File to Document Template
Now, when you have the document ready, you can easily upload it to Document Template that you chose in the Login process.
All you need to do is click on the "Upload File to Template" button, on the "Template Builder" work pane. Once you've done it, you'll see the message that the file has been successfully uploaded.
Now, if you go to Salesforce, select the "Document Templates" tab, and click on the respective Document Template in the "Maven Documents" app, in the "Files" section you will find the document.
Once the template document gets Template ID, you can generate the file.
Template Document ID appears a couple of seconds after the file was uploaded. If the ID doesn’t appear in a maximum of two minutes, try uploading the same file one more time.
The waiting time solely depends on the load that Salesforce is dealing with upload time.
If you want to add a new file to Document Template, you can use the "Add Files" button or the "Upload New Version" option. Maven Documents will use the latest file version that is uploaded. To keep things clean, it is good practice to have one file per Document Template uploaded.
¶ Debug Document with Playground
If you want to see what the actual data will be available on your Document Template, feel free to use the "Playground" tab. It can also serve as a means to debug the Flow when things aren't going as they should be.
Try changing the Locale definition in your settings, for example, and see how data changes. Magic isn't it?