Contact us Blog

Generating documents with sublists

Sublists in Word and PDF documents

A common use case for document generation involves producing a document containing one or more lists of items. For example, you might create a proposal to a prospect, containing a list of deliverables and a list of dependencies. Or you might create a thank-you letter to your donor, containing a list of their cash donations and another list of in-kind donations.

For example, suppose we need a series of Word documents, one for each of our partner accounts, in which each document contains a list of that partner's recently-closed opportunities. Suppose also that the opportunity list must include calculated fields, e.g., the number of product line items associated with that opportunity. Below is a rough outline of what the output document might look like.

To produce this output, we might create a Word template that looks like the one below, where we have inserted Word merge fields at the right places, for the account name, status and website. We have also added merge fields within the table of closed opportunities, marking the spots at which the opportunity's name, amount, close date and line item count must appear.
There are a few additional aspects here that merit attention. One has to do with creating the template, and the other with providing data for the template.

Designing the merge action

Generating documents with such sub-lists requires an understanding of how the document merge tool relates data elements with fields. To that end, here is a broad outline of the steps you need to carry out. The description below assumes that you are familiar with the user interface and functionality of the document merge tool.

Preparing the template

Use Microsoft Word to create a document template. This is just a plain old Word document with merge fields in the appropriate places. You might find our merge field creation macro useful in this process.

Identifying top-level and sub-list fields

In this document template, identify which fields of the template are “top-level” fields, and which are “sub-list” fields. Essentially, the top-level fields are those for which there will be just one instance in any given output document, while the sub-list fields will have more than one occurrence. For example, in a thank-you letter, your top-level fields might be the contact’s full name, phone number and address, and the sub-list fields might be the name, close date and amount of each donation that that contact made. Sub-list fields will appear between TableStart and TableEnd tags.

Data sources

Identify the data source from which the top-level fields will be obtained, and the data source(s) from which the sublist fields might be obtained.

Top-level field data:

The top-level fields will always be populated from the specific object, report or multi-step report from which you are doing the merge. This means that, for each record that appears in your report (or in the filtered list if you are merging from an object), there will be exactly one merged document produced. So the number of output documents will be the same as the number of such records. Unless, of course, you have selected "single document with page breaks," in which case the number of pages in the document will correspond to the number of records.

Sublist data:

The sub-list fields will almost always be obtained from some report or object other than the one that populates the top-level fields, i.e., other than the one from which you are merging. For instance, in the case of a thank-you letter, you would create a report that produces all the donations you wish to acknowledge, regardless of who gave them; and when you set up the linkage (see below), Apsona will correlate each top-level record with the specific sub-list records that are linked to it. It is very rare for the sub-list fields to be obtained from the same data source as the top-level fields.

Sublist content:

Ensure that every field you need to populate in the sub-list is produced by the data source you choose for the sub-list. This factor often determines what kind of data source you will use for a sub-list. For example, if you are creating a thank-you letter in which your sub-list includes only contact role fields, you can get away with using the Opportunity Contact Role object as the data source. But if, for example, you wish to include donation amounts and total payment amounts, you will need to create a report that includes these fields, and use that report as the data source.

Reusing data sources:

When running the merge, you will specify the data source for each sublist. In some cases, it is possible to use the same report as data source for more than one sublist, because you can use a different filter for each data source within the merge tool.

Linkages

For each sub-list, ensure that the linking field needed to correlate the sublist’s fields with the top-level fields is included in the report you will use to populate the sub-list. For example, in a thank-you letter containing a sub-list of donations, you must ensure that the contact ID from the Opportunity Contact Role object is included in the report that will serve as the data source for the sublist. You will need to set up these linkages in step 3 of the merge tool.

Running the merge

Execute the document merge tool, using the fields and linkages identified as above.

Grouped lists

When creating a sublist within a merged document, you can display the sublist in groups based on a field in your data. For example, below is a screen shot depicting an Account's information, along with all its closed deals in one list, and its open opportunities grouped by stage. Notice that each list includes total opportunity amounts, which are also generated by the merge tool. This layout is produced by the template below. If you like, you can download this template. Notice that this template has two record groups (as indicated by the TableStart tags) named ClosedDeals and OpenDeals. The fields in these record groups are mapped to data fields in the usual way (see the documentation about the merge tool). To produce this grouping behavior, the merge tool relies on a special merge field. The GroupHeader field indicates the data field of the sublist on which to group the list. This field is set in the format GroupHeader:RecordGroupName:GroupFieldName, i.e., the word GroupHeader, followed by the record group name and the grouping field name separated by colons. No spaces are allowed anywhere in that field name. The example in the above screen shot uses GroupHeader:OpenDeals:DealStage. When you create such a merge field, the GroupFieldName becomes available for mapping in step 2 of the merge action builder. Thus, corresponding to the above example that uses GroupHeader:OpenDeals:DealStage, the field DealStage is available for merging, as in the screen shot below. You can therefore map it to any field of the data source associated with your record group, and it will be replaced with the grouped values from that data source field. The merge field can be styled as needed, with colors and fonts, using the usual styling tools available in Word.

Generating totals and subtotals

To generate a total or subtotal, we use an aggregation field, which is another specially-formatted merge field in the format RecordGroupName:FieldName:function. In the above screen shot, we have two examples of this: ClosedDeals:Amount:Sum and OpenDeals:Amount:Sum. These fields are placed within the same table, on their own row outside the row containing the TableStart and TableEnd tags. You can, of course, style these fields as you see fit. For instance, in the screen shots above, we have merged two cells to produce the Total cell, and we have its content right-justified within the cell.

Notice also that, if you use an aggregation field within a grouped table (one that has the GroupHeader field applied), the merge automatically generates subtotals by group.

The SUM(ABOVE) function

Microsoft Word includes a SUM(ABOVE) function for calculating the sum of the values in a column in a table. And in some situations, e.g., when you are combining multiple sublists into the same table (to make it look like a single table), you might want to use this function to display a column total. Therefore, Apsona's Document Generator automatically updates the values of any cells that contain this function. So the function works fine with the PDF format as well.

Note, however, that currently only the SUM(ABOVE) function is supported. The other related functions SUM(LEFT), SUM(BELOW) and SUM(RIGHT) are not supported.

There is also a known issue: If the table that contains the SUM(ABOVE) function contains merged cells, the function will not work correctly.

Notes about grouping

Some key things to remember:

Notes about aggregation fields

Suppressing empty sublists

If the sublist for a particular document produces no records, you might want to either suppress the sublist entirely, or produce alternate content indicating that there were no data records for the sublist. You can use the Count function indicated above to achieve this, in conjunction with IF conditional directives. (The IF and related conditional directives are described in detail here.)

Below is a screen shot showing how to combine the two. Here we have used a record group named Donations, so we can use the aggregation field Donations:Count in a conditional directive. So we produce just the text "None" if there are no donations, otherwise we produce the full list of donations in a table.

Sublists without tables

In some situations, you might want to generate a sublist whose contents cannot be in a table. Perhaps you need the data items to be in a few repeated paragraphs, or perhaps a numbered or bulleted list. Below is a screen shot that shows such an example in which the top-level field is the name of an Account, and the sublist contains the contacts of that Account. To achieve this effect, we must coax Word to produce all the merge fields needed for a block without paragraph breaks between them. To do this, instead of typing the Enter key to separate the lines, we use Shift-Enter (i.e., hold down the Shift key when pressing the Enter key). The result is a template shown in the screen shot below. Notice how, for example, the Name, Phone and Email lines in the bullet list all appear in the same bullet. If you like, you can download this template and peek at it.