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.
- We have added special
TableEnd merge fields within the table, as you can see in the above screen shot. These two markers indicate the region
of the document that must be replicated, once for each closed opportunity. The
TableStart field must be the first
element in the leftmost cell of the row which must be replicated, and the
TableEnd field must be the last element in the last cell of the
row. (These are standard Word merge fields, inserted using the usual process for merge field creation, the only difference being that they are specially named.)
TableStart field must include a record group name following a colon. In this example, we have used
TableStart:Oppties, so we have
Oppties as our record group name. (Notice that there is no space after the colon.) This name is just a mnemonic for us, indicating the kind of list being produced; i.e., we can make up
any name we want, as long as it does not conflict with any other merge field name in the document.
When creating the merge action, Apsona will detect the record group and enable us to provide a data source for it. In this case, we might create a report that
gives us all the closed opportunities from partner accounts, and use that report as the data source. Step 3 of Apsona's merge tool lets us select the report
and associate it as the data source for the record group - see below.
After we select the report, the merge tool lets us map the merge fields needed by the sublist with those output by that report:
A key factor here is that, when producing the output documents from the template, the data source report for the Oppties record group must produce, for each
account, just the opportunities for that account, and no other. So when the merge tool runs, it must look through each Account that produces the output
documents; and for each such account, the merge tool must make sure that the report produces only the
opportunities for that specific account. For this reason, the merge action popup shows all the possible linkages between the parent object (in this case, the
Account object) and the object producing the report for the sublists (in this case, the Opportunity object), and it lets us select the one to use.
In this example, we must use the linkage that matches the Account ID in the parent object with Opportunity.Account, which is the field in the child object
(Opportunity) whose report will produce the Opportunity records. This linkage is what aligns each Account record with the corresponding Opportunity records.
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.
Note that there is a one-to-many relationship between the top-level fields and the sublist fields; that is,
for a given occurrence of a record that fills the top-level fields, there can be more than one record whose
fields fill the sub-list elements.
There can be more than one sublist in a given template. For example, if your donors donate cash as well as
in-kind, you might need to create two different sub-lists, one for the cash donations and one for the in-kind
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.
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
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
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.
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.
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
. 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
field indicates the data field of
the sublist on which to group the list. This field is set in the format
, i.e., the word
, 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
. When you create such a merge field, the
becomes available for mapping in step 2 of the merge action builder. Thus, corresponding to the above example that uses
, the field
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
. In the above screen shot, we have two examples of this:
. 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
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:
- You can use templates created using OpenOffice, LibreOffice or Google Docs, by using the angle-bracket syntax for merge fields
- It is important to ensure that the
GroupHeader field appears within the very first row of the data table, and not outside the table. In the
screen shot above, even though it
seems as if the
GroupHeader field is outside the table, opening up the template will show you that the Word table has a top row with no borders, and
that row contains the
GroupHeader field. When you set up the table in this manner, the merge tool replicates the entire table layout for each group
that it produces.
Note also that the spacing between groups in the output is actually produced by an empty last row in the table. Since the grouping process simply replicates the
entire table, there will be no space produced between the generated tables. Therefore, the template includes an empty row so that the resulting output is easier to
read. You can download the template and examine it to see how these
Notes about aggregation fields
The aggregation fields (for generating totals or subtotals) must appear within the table, in their own row outside the row containing the TableStart and
When using an aggregation field with the syntax
RecordGroupName:FieldName:function, you must ensure that
RecordGroupName is the same as the one used in the TableStart tag
FieldName is one of the fields used between the TableStart and TableEnd tags, and is therefore mapped to an appropriate field in your data source
Function is either
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
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
, so we can use the aggregation field
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.