Contact us Blog

Generating documents and email messages

Apsona's merge tool offers Excel merge, Word merge and Email merge in a single package. It enables you to create pretty-looking Word and Excel documents containing data extracted from your Salesforce database. For example: For more specific information is available:

Generating .docx documents
Generating .xlsx spreadsheets
Generating email messages

Prerequisites

If you are not an administrator, make sure that your Apsona configuration includes access to the Document object in Salesforce. To see how this is done, please look at the documentation about Apsona configurations.

Getting started

To use the merge tool, you will need to (a) create a template that specifies the layout of the generated output, and (b) identify the data source that will be used to generate the output.

What is a template?

If you are generating Word or PDF documents, the template will be just a plain old Word .docx file. If you want Excel output, the template will be an .xlsx file. In either of those cases, you would store the template in your Salesforce Document object. And if you are generating e-mail, you will use a Salesforce email template created via Setup - Communication Templates - Email Templates in Salesforce.

What is the data source?

The data source is where the merge data comes from. The data source can be a Salesforce object (such as Contact, Lead, Account, or a custom object), or from an Apsona report or multi-step report. You can merge directly from an object if If you want to hard-wire the filtering conditions into the merge action (e.g., when you want to run the action from a button or a sidebar link in Salesforce), or you want the documents generated in a specific order (e.g., by the zip code value), or you want to include either calculated fields or fields from multiple objects, you would use either a report or a multi-step report.

Merging from a report

To generate documents or send email from a report, you would: You can also run merges from multi-step reports, if you have that feature available.

Merging from an object's views

You can also run a merge from any of Apsona's console views (the view available via the object's "All records" menu item) or tabular views. To use this feature, click the Tools menu and then the "Merge/Mail" item. You will see the same popup that appears for merging, but in this case it will apply only to the filtered set of records in your current view.

Merge actions

To perform a merge, you will create what we call a merge action, which specifies three things:

With Apsona's merge tool, you can create and edit merge actions, and save them with specific names. You can then run an existing merge action simply by providing its name, so that you don't have to rebuild or re-edit the merge action. Thus you can have your more-knowledgeable users perform a one-time setup of all the merge actions you need, and other users can simply run the merge action of their choice, without having to delve into the complexities of the template and mapping mechanisms.

Document templates

Where are templates stored?

The Document Generator can use templates stored either in your Salesforce Document object, or as a Google Doc. If you want to use the Salesforce Document object, your first step would be to open the Document tab in Salesforce (or use the Apsona Document page) and upload your template there.

What about Lightning?

If you are using the Lightning experience, the Document object is not available in the Salesforce UI. To work around this situation, you can do the following:
  1. First, create a few folders in the Document object. This is a one-time-only step that requires a temporary switch to the Classic experience.
  2. From this point forward, use the Documents console view within Apsona to manage documents, as described in this article.

Template file formats

The Document Generator does not support the older .doc and .xls format used by Office 2003 or earlier; it requires the newer formats supported by Microsoft's Office 2007 and later versions as well as OpenOffice and LibreOffice applications. If you use the older Office products, you can get around this problem by installing Microsoft's Office Compatibility Pack.

As far as formatting or layout constraints, there are virtually none: you can use almost any .docx file or .xlsx file containing merge fields. Simply edit your file and set it up the way you want to look, and upload it into your Salesforce Documents object to make it available for Apsona's merge tool.

Naming your merge fields

In general, there are very few requirements as to how you should name your merge fields. They do not, for example, have to be the same as - or bear any relationship to - any existing field anywhere in your system. So when you create your template, you can simply make up any merge field names you want. This way, during the template creation, you can choose mnemonic names indicating what they mean to you, and your primary concern will be the way the template is laid out rather than what to name the fields. Later, when you actually create the merge action which uses the template, Apsona's merge tool will open up your template, extract the merge field names it finds there, and let you set up the name mapping.

Data sources

The data source for a merge action can be a Salesforce object (such as Account, Opportunity, or any custom object), an Apsona report, or an Apsona multi-step report.

Objects as data sources

When using a Salesforce object as a data source, you create the merge action navigating to the object's tab in Apsona and clicking Tools - Merge/Mail. In this case, when you set up the field mapping, Apsona will offer you all the fields in the object as candidates with which you can match each merge field. Also, when running a merge action whose data source is a Salesforce object, you can run a filter to select just some of the data records in the object for the merge action. You can also use the tabular view, and check one or more the checkboxes next to the records before running the merge action. If you do so, only the checked records will be included for the merge.

Single-step reports as data sources

When using an Apsona object report as data source, you run the report, and then click the Merge/Mail button in the tool bar at the top. In this case, Apsona will show you the fields output by the report as match candidates. Also, unlike when using an object as data source, running a merge action on a report will include all of the report's records in the merge results; you cannot, for example, use any checkboxes to select the records to include.

Multi-step reports as data sources

When using an Apsona multi-step report as data source, you run the report and then click the Merge/Mail button in the tool bar at the top of the grid result. In this case, Apsona will show you the fields output by the grid as match candidates. And as with the Apsona object report, running a merge action on a multi-step report's grid will include all of the grid's records in the merge results; you cannot, for example, use any checkboxes to select the records to include.

Selecting a subset of records

By default, when you invoke the merge popup, it will use all the records in the current data source for generating documents. But in some situations, you might want to select just some of the records. For example, when using the Contact object as data source, you might run a filter on the Contact object to obtain your most important contacts, and in looking through the list, you see a few contacts who have already been contacted and must be omitted from the generated list. A similar need for omitting some records arises on occasion when running the document merge from a report or multi-step report.

When running the merge from a list or tabular view of an object, you can simply use the check boxes at the left of the result list to select the records you wish to merge.

When generating documents from a report or multi-step report, however, check boxes are not available. But you can still select a subset of the generated list, as follows.

More info about generating .docx documents

More info about generating .xlsx spreadsheets

Generating email messages

A tour of the document generator

When you invoke the document generator by clicking the "Merge/mail" button, a popup window appears, within which you create and manage a single merge action. This popup window contains a four-step wizard. In the following description, we use the term "data source" to mean the object or report from which you have invoked the document generator popup.

Selecting the action

In the first step, you select what you want to do: either run an existing merge action or create a new one. If you choose to create a new merge action, you then specify the kind of merge action you want to create, as in the screen shot below. If you elect to run an existing action, a dropdown appears, listing all the merge actions applicable to the object or report from which you are running the merge action. In this case, you also have the option to edit the merge action before running it. If you do not choose to edit, clicking the "Next" button causes selected merge action to run immediately. If, however, you choose to edit the merge action, you will be stepped through the wizard just as if you were creating a new merge action, except that the settings of the selected merge action will be preset in every step of the wizard.

Setting up options

The choices in this step depend on the kind of action you selected in step 1.

Mapping the fields, filters, and linkages

In this step, the document generator popup displays all of the placeholder fields it finds in the template. Alongside each such field, the popup shows a dropdown that lists all the fields available in your data source. So you can specify, for each field in the template, the corresponding field in your data source that will provide its data. In the simple case where your document template does not contain any sub-lists (i.e., record groups), this popup will contain all the available template fields in one group. If, however, your template includes sub-lists, the template placeholder fields of each record group will be listed separately in its own region, underneath its record group name, e.g., the way the ClosedDeals record group appears in the above screen shot. For more details about record groups, see the detailed documentation.

Setting up logging

This is the last step of the merge action creator wizard, and here, you can set up logging options specifying additional actions to be taken when the merge action is run, as shown in this animation.

You can, for example:

Attaching generated documents to data records

When attaching the generated documents to corresponding Salesforce records, you can specify the object records to which you want them attached (via the selector marked 1 in the screen shot below), and whether to use Salesforce Classic's Attachment object or Lightning's File object (via the selector marked 2 in the screen shot below). Also note that the name of the attachment or file will be determined by your choice of the "Field to use as file name" in step 2 of the merge builder - see the screen shot in the section above on setting up options.

Required record ID fields

To perform any of these logging actions, Apsona needs to be able to access the corresponding data records uniquely. For this reason, you can only perform a logging action on a particular object if the record id field of that object is available in your data source. For example, if you have created a report from which you are generating documents, and you want the generated documents to be attached to corresponding Contact records, then your report must include a field containing the Contact Id. Similarly, if you wish to add a task to the Account object indicating that the document generation was completed, your data source must include an Account Id field.

You can also specify a the format for Date and Currency values when generating .docx or .pdf files.

Copying or replicating a merge action

On occasion, you might need to copy or replicate a merge action. As a specific example, suppose you have created a merge action PremiumCustomerInvoice that uses the template named PremiumCustomerInvoice.docx, and now you wish to create a similar merge action for the template StandardCustomerInvoice.docx, which is a variation of PremiumCustomerInvoice.docx. Perhaps most of the template merge fields in the two templates are similarly named, with perhaps some slight variations. Here are the steps for replicating the merge action. This assumes that the template StandardCustomerInvoice.docx has been uploaded into the Salesforce Document object.

  1. Click the Merge/Mail item to start the merge action builder, and select the PremiumCustomerInvoice merge action via "Run an existing merge action." Check the "Edit this action" box.
  2. In step 2, Apsona pre-selects the PremiumCustomerInvoice.docx template. Change this template to StandardCustomerInvoice.docx, and click Next.
  3. In step 3, Apsona pre-fills the mappings for the fields that have identical names between the two templates. Modify the mappings as necessary to suit the needs of the new StandardCustomerInvoice.docx template. Then click Next.
  4. In the last step, after setting up any logging options you need, ensure that the "Create a copy" check box is checked, and change the name of the merge action to StandardCustomerInvoice. Because of the "Create a copy" option, Apsona now creates a new merge action with the name you specify.
  5. Click Save and Run.
The key idea is that we reuse as much as possible of the work done in creating the first PremiumCustomerInvoice merge action by stepping through the merge action builder with it, but using the "Create a copy" checkbox functionality so as to produce a new merge action instead of overwriting the existing one.

Deleting merge actions

If you wish to remove unneeded merge actions (you will need to be an administrator to do this):
  1. Click Settings - Merge actions to see the list of merge actions in your org
  2. Use the filtering mechanisms to find the ones you wish to delete
  3. Check the boxes next to them, and click Tools - Delete checked
Note that the Merge actions item in the Settings menu is available only if you are an administrator.

Troubleshooting

Here are some common issues that arise, and some tips on how to resolve them.

Generating documents with tables/sublists: a separate document is generated for each sublist item

When generating a document containing sublists, e.g., a letter to a Contact that should contain a list of their gifts, Apsona might produce a separate document for each gift, instead of one document per Contact. This is typically because the merge isn't set up correctly: You must obtain the top-level single-occurrence fields (e.g., the Contact fields) from one data source (e.g., a report or the Contact object), and the sublist's fields from a different data source (e.g., an Opportunity report, or the Opportunity object). See this link for full details.

Can't find the object ObjectName

You might see a popup error saying that (the metadata for) a certain object is not available. The error message will include the API name of the object. This typically means that the merge action, or one of the objects or reports it relies on, requires access to the specified object. To remedy this situation, carry out the following steps:
  1. Ensure that your Salesforce user profile has access to the object indicated in the error message. Your administrator can make this happen, via the Salesforce Setup screens. Note that the BrandTemplate object is actually labeled Letterhead in Salesforce.
  2. Ensure that the Apsona Configuration for your user profile includes the object in question as a visible object. Follow the directions described in our documentation about object access.
  3. If the error message is about an Attachment object, it could refer to Foo_Attachment, where Foo is another native or custom object. In this case, make sure that both the objects Attachment and Foo_Attachment are included in the configuration.
  4. After the above steps are carried out, clear your Apsona cache by clicking Settings - Clear cache in Apsona, as described in our documentation.

When generating a .docx document with sub-lists, some of the generated sub-lists are empty or partially filled.

The most common reason for this happening is that the data source report that feeds the sub-lists has a limited record range (see more about record ranges). Keep in mind that the document generator runs just one query to extract the records for all of the sub-lists needed by all of the generated documents. So if you limit your report's record range to, say, 100, then any sub-list data that needs records past the 100 limit will not be populated.

In step 4 (logging), some dropdowns are empty or incomplete.

The usual reason is the absence of the necessary record ID fields in the source report. See the above description related to setting up logging for more details.

In step 4, the "Attach" button is grayed out or unavailable.

When generating .docx or .pdf files, if you have elected — in step 2 of the merge action popup — to produce a single file for all your output documents (with either paragraph or page breaks) instead of separate files for each document, the "Attach each output..." checkbox will be unavailable. This is because, once Apsona produces a single file for all your records, it is not able to break them out into individual files for attaching to individual records. Therefore, if you wish to attach the generated documents to the records, you will need the "Separate files" option in step 2.

When merging from a button, "merge failed" appears.

When generating a document from a button, you might see an error that says "Merge failed: No data records available for merging." This happens if the merge action being invoked involves a report that excludes the current record. Conside a specific example to illustrate. Suppose you that have created a merge action on an Opportunity report that produces an acknowledgement letter, and that you have created a button on the Opportunity detail page for generating the letter. Suppose also that you have a filter on the report that produces only Closed/Won opportunities. If you then click the button on a detail page for a non-closed opportunity, that Opportunity record will not qualify for the report's filter, so there will be no data available for merging. You will then see this error.

Word documents are generated with pages in incorrect order.

If you generate multiple Word documents (or address labels, or a single document with page breaks) you would expect that the documents (or pages) are generated in the order that they appear in the browser. However, there are technical limitations that make this impractical. If you generate documents from a report or multi-step report, the order of the documents will be consistent with the order in which they appear in the report. But if you generate them directly from an object, the specific field mappings will determine the sort order, so it is not always possible to generate the documents in the order of record occurrence in the browser.

Therefore, if you wish to generate documents in a specific order, you would want to generate them from a report created for that purpose.
script>