¶ 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.
¶ 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 Trust this add-in button
-
You'll see the message that Template Builder is successfully loaded and the logo of the add-in appeared in Excel taskbar
That's it! Now you have Maven Documents Template Builder installed!
¶ Online version
Follow the next steps to install Maven Documents Template Builder on the online version of Microsoft Excel.
-
Go to Office.com and log into your Microsoft account
-
Create a new Excel book document or open an existing one
-
Select Insert tab in Excel taskbar and click on Add-ins
-
Click on Office Store button and search for Maven Documents Template Builder
-
Select 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 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.
¶ Run the Template Builder Add-in
The first thing that you should do before building the template is to run Template Builder add-in on your XLSX file and log into the Salesforce account.
Follow the steps to run Template Builder add-in on the offline XLSX file:
-
Run Microsoft Excel and create a new or open an already existing book.
-
In Home tab click on Maven Documents button on the right side of the Toolbar
-
You'll see Welcome message with some useful links. Click Let's go
-
After the Maven Documents task pane appears click on Login to Salesforce button that looks like a gear
-
A new dialogue window should open, offering two login options. 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
Note: If this is your first time logging into the Salesforce account with Template Builder, the verification of a user 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 could upload the file later with Template Builder.
-
Click Done
Note: Notice, that 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 find the query, built-in Query Builder, on Template Builder's work pane.
As the taskbars in online and offline versions of Microsoft Excel are almost the same, the process of running the add-in on the online version will be similar.
¶ Meet the 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 Template Builder work pane. Let's divide it into two parts: Work part and the Administration part.
¶ Work part includes:
-
{} Fields section. Here you will find the objects and the fields you added to the query in Document Solution. All objects and fields can be inserted into the document with one click.
-
</> Elements section. 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 XLSX Elements Reference page.
-
The fx or Expression Builder section. 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 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 Select function menu. The first dropdown menu filters functions by categories (date, number, string, etc.), and the second dropdown menu contains (filtered) functions you can use.
You can also insert your Salesforce fields into expressions by using the dropdown in 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.
Note: Before inserting the function or a field, verify that you've selected the text you want to replace in the text field below
¶ The administration part includes:
- Refresh Nodes button. This button serves two purposes. The first purpose is to get the query connected to the selected solution straight from Salesforce, reflecting any changes made to that query straight to the list of queried objects and fields. The second purpose is to refresh the context inside the document, depending on the cursor's location. If the cursor is placed inside a repeater or table that iterates through a sub-queried object, its name and fields will also appear in the list on the task pane, making it easier to insert them into that Table/Repeater.
- 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.
- Upload File to Template button. Click on this button to upload the XLSX file you are working on to Document Template, that you selected in Login to Salesforce step.
Note: After changes are made in the XLSX file you need to upload it to Document Template. Because once the file is uploaded to Salesforce it doesn't refresh automatically. And 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 the Template Builder add-in 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 Maven Documents 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.
It is important to remember that one cell can hold only one merge field.
Remember, that 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 Name field from Opportunity object to show from which opportunity you will take the data. Select {}Fields tab and click on Opportunity object. In the dropdown list of fields, click on {} 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 Account object to indicate who will use the quote. Also, insert CreatedDate field directly from Opportunity to show when the quote was prepared.
All the fields we've put using {!QueryName.FieldName} notation will be dynamic, meaning they will 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. And by magic, we mean Expressions.
-
Select fx tab on 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 Select function section select Format function type and format date function. Click Insert. That function makes the date look in the "dd/mm/yyyy" format
-
Highlight DateField in the text field below and in Select function section select Date function type and addDays function. Click Insert
-
Now you need to replace DateTime in the expression with the current date, which will also be a function. From Select function menu, choose Date function type and now() function
-
Highlight DateTime in the text field below and click Insert. This action will replace DateTime with now() function
-
Replace NumberOfDays with 30
-
The last thing to do is to change Format to a 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 will only be shown after the template is generated and 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 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, a repeater. And this is where Elements part kicks in.
Note: Elements are the 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 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 a Table you will use Repeater element to show the information about the products.
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 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 Elements tab.
- In Element Type field select Repeater element
- Fill the attribute fields with the following values:
Name: line-items
Value: Opportunity.OpportunityLineItems
Variable: oli
Direction: Vertical
Last Cell: F - Leave Hidden attribute unselected and click Insert Element
Have a look at the screenshot.
Now, when you have the header of the table and the repeater inserted, it is time to fill it with data.
- Place the cursor in the cell inside the Repeater and click Refresh button on Template Builder to get the fields from "oli" variable shown in Fields tab in Template Builder
- Insert the 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 the 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, 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 Elements tab
- In Element Type fields select Repeater element
- Fill the attribute fields with the following values:
Name: contacts
Value: Opportunity.OpportunityContactRoles
Variable: con
Direction: Vertical
Last Cell: F - Leave Hidden attribute unselected and click Insert Element
When the 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 the 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 Row or Column element in Template Builder
-
If you want to remove the row or the column leave Collapse Only checkbox empty
-
If you want to hide but not remove the row or the column, then mark 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.
¶ Upload the XLSX File to the Document Template
Now, when you have the document ready, you can easily upload it to the Document Template that you chose in Login process.
All you need to do is click on Upload File to Template button, on Template Builder work pane. Once you've done it, you'll see the message that the file has been successfully uploaded.
And now, if you go to Salesforce and select Document Template tab, and click on the respective Document template in Maven Documents app, in Files section you will find the document.
Once the template document gets Template ID, you can generate the file.
Note: Be aware that Template Document ID will usually appear a couple of seconds after the file has been 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 the Document Templates, you can use Add Files button or 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.
Note: If you built your template in the Online version of Microsoft Excel or Autosave mode is available on the Offline version, the name of the generated document will include a URL with the path to the template.
¶ Debug Document with Playground
If you want to see what the actual data will be available on your Document Template, feel free to use 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?