¶ 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 and log into your Microsoft account.
-
Create a new Excel book document 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 the Template Builder Add-in
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.
Follow the steps to run the Template Builder add-in on the online XLSX file:
-
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. 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 the 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 offline 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 the Template Builder work pane. Let's divide it into two parts: the Work part and Administration part.
¶ The work part includes:
-
The {} Fields tab. 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.
-
The </> Elements tab. Here you have a picklist with 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 (filtered) 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.
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:
- The 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.
- 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 XLSX file you are working on to Document Template, that you selected in the 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. 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 the 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 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 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. 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 DateField in the text field below and in the Select function section select the Date function type and the addDays function. Click Insert.
-
Now you need to replace DateTime in the expression with the current date, which will also be a function. From the Select function menu, choose the Date function type and the now() function.
-
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 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 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.
Note: 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.
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 attribute fields with the following values:
Name: line-items
Value: Opportunity.OpportunityLineItems
Variable: oli
Direction: Vertical
Last Cell: F - Leave the Hidden attribute unselected 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 to get 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 attribute fields with the following values:
Name: contacts
Value: Opportunity.OpportunityContactRoles
Variable: con
Direction: Vertical
Last Cell: F - Leave the Hidden attribute unselected 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.
¶ Upload the XLSX File to the 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 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.
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 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.
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 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?