Generating documents with sublists

Sublists in generated 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.

Notice the special TableStart and TableEnd merge fields within the table 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.)The TableStart field must include a record group name following a colon. In this example, we have used TableStart:Oppties, so we have chosen 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.

Note that all the record groups in your document template must have distinct names. No two record groups can have the same name.

Setting up the merge action #

When creating the merge action, Apsona automatically detects each record group in the template, and lets us provide a data source for it. For the present example, we might create a report that gives us all the opportunities from partner accounts, and use that report as the data source. In step 3 of the merge action builder, each record group is associated with a tabbed panel containing four tabs: Mapping, Filter, Sort order, and Linkage:

(There will be as many such tabbed panels as there are record groups in your template.)

The Mapping tab #

In the Mapping tab, we select a report and associate it as the data source for the record group, as in the above screen shot. After we select the report, the merge builder fills the field dropdowns alongside each merge field it found, and lets us map the merge fields needed by the sublist with those output by that report. In the example screen shot below, we are mapping the Amount to the similarly-named Amount in the report. (Note that the two field names do not need to be the same – you can use any names you like in the template, and similarly in the report. The merge builder lets you correlate the two.)

The Linkage tab #

We must of course ensure that, in the output document for a particular account, there will appear only the opportunities for that account, and no other opportunities. So when the document generator runs, it must look through each Account that produces the output documents; and for each such account, the generator must make sure that the report produces only the opportunities for that specific account. The usual way to relate the records in the sublist with the main Account record is via lookup fields between the two objects – in this example, lookup fields from Opportunity to Account. So the merge action builder uses the Linkage tab to show all the available lookup fields 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.

Include the linkage fields. You must ensure, when creating a linkage, that both the fields involved in the linkage are available in the respective data sources. In the above example, the report that produces all the partner accounts includes the Account ID field. So Apsona knows that it should show in the data source dropdown all the objects and reports that contain an Account ID as a field. If you don’t include the linking Id field in the top-level report, you won’t see the necessary data sources in the dropdown.

The Sort / Group tab #

The Sort/Group tab provides the ability to either sort the sublist by a particular column, or to group it by one or more leftmost columns.

  • If you choose to sort by a particular column, the sort order you select will override the sort order in the data sourse. For example, if your data source is a report that is sorted by a Name field, and you specify a sort order by a Date field in the Sort/Group tab, then the data in the sublist will be sorted by Date.
  • You can instead choose to group by the leftmost n columns in the sublist. This feature is described later in this document.

The Filter tab #

You can use the Filter tab to set up filter terms additional to the ones already in the sublist’s data source. In the present example, we have a sublist data source that produces all partner opportunities, but the ClosedDeals sublist is required to show only Closed opportunities. We can enforce this condition by adding a filter condition in the Filter tab, as in the screen shot below. With this feature available, you can (for instance) reuse the same report in multiple sublists, with different filter conditions in each sublist.

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 donations.

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.

General guideline: As discussed above, always get your sublist data from a report or object other than the one used for the top-level fields. If you don’t, you will produce a separate document for each row in the sublist, which is likely not what you want.

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.

Need for linkages #

If you create a document with a sublist, you will almost always need a linkage to relate the sublist content to the top-level content. So the document generator tool produces an informational message in step 4 if it finds any missing linkages:

But in some cases, you might want to include the same list of records in all the generated content – e.g., if you want to produce a list of upcoming events next month, to be included in all the generated documents or email. In such cases, you can safely omit the linkage and ignore the informational message. Also note that the above informational message is produced only for document and email generation actions, not for Excel generation actions. This is because sublists without linkages are very common in Excel merges, so the informational panel is unnecessary.

Running the merge #

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

Grouped lists and sub-sublists #

In some situations, you might want your sublist to itself be composed of multiple sublists. For instance, your document might contain info for a single Account and its Opportunities, and for each Opportunity, the list of Products for that Opportunity. In such a situation, you can group by the Opportunity field(s) and show the Products as a sub-sublist.

Grouping is also useful within the same object type, e.g., when you want to show your list of Opportunities grouped by Opportunity Type.

There are two different display structures available for grouped lists. The first is used when grouping on a single field (that is separately displayed), and the second is used when grouping on the leftmost fields in the sublist.

Grouping on a single field #

When grouping on a single field, you can use a special GroupHeader field to designate the grouping field. Below is an example 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.

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 described elsewhere.
  • Make sure 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 bits work.

Cumulative sums and averages #

In some use cases, when grouping on a single field, there is a need to calculate cumulative sums and averages across previous groups. For example, the screenshot below shows an Account Summary with Opportunities grouped by the quarter of the Close Date, and the need is to show cumulative sums of the Opportunity amounts over previous quarters.

To support this functionality, the merge tool includes two functions, CumSum for cumulative sum and CumAvg for cumulative average. Below is a screenshot of the template that uses the CumSum function to produce the cumulative sum.

Some notes:

  • The cumulative sum for the Amt field of the Opps record group is produced with the field «Opps:Amt:CumSum», similar to the other grouping fields described above. Similary, the cumulative average is produced with «Opps:Amt:CumAvg».
  • The cumulative values are for the current and previous groups, where “previous” is defined by the ordering of values in the grouping field. In the above example, the ordering is on the Qtr field that represents the quarter of the Close Date of the Opportunity. The merge tool automatically orders the groups in ascending order by the values of the grouping field.
  • The row that produces the cumulative sum or average must be part of the table being replicated. In this example, we have an extra row at the bottom of the table containing the cumulative total.
  • You can download and examine the template used to produce the above screenshots. You can also download the merge action that produces this merge, and import it into your org to try it out.

Grouping on leftmost fields #

Another way to show grouped sublists is by the leftmost columns. You would set this grouping via the Sort / Group tab in the merge action builder popup. In the example screen shot below, we have set the grouping to use the first (leftmost) two columns.

Suppose we use this grouping with a template such as the one below. Notice here that the sublist contains five columns.

Below is an example of the result we obtain. The sublist is grouped by the leftmost two columns, Commitment and Stage, which appear only once in each group. The remaining three payment fields appear so that the payments are a sublist of the Commitment (Opportunity) record.

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.

Generating totals, subtotals and aggregations #

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.

Some limitations exist:

  • 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.
  • If you are creating your template with Microsoft Word, it is best to use a Word Merge field (via Insert – Quick Parts – Field in Microsoft Word) rather than a Word Formula field (the kind you get via Insert – Quick Parts – Formula) for SUM(ABOVE) values. If you use a Word Formula field, your output document cannot be properly rendered in PDF format.

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.

Powered by BetterDocs