Sublists in generated documentsA 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.
TableEndmerge 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
TableStartfield must be the first element in the leftmost cell of the row which must be replicated, and the
TableEndfield 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.
Setting up the merge actionWhen 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:
The Mapping tabIn 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
Amountto the similarly-named
Amountin 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 tabWe 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 tabThe 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 tabYou 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
ClosedDealssublist 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 actionGenerating 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 templateUse 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 fieldsIn 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 sourcesIdentify 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 dataThe 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 dataThe 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 contentEnsure 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 sourcesWhen 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.
LinkagesFor 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 linkagesIf 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 mergeExecute the document merge tool, using the fields and linkages identified as above.
Grouped lists and sub-sublistsIn 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 fieldWhen grouping on a single field, you can use a special
GroupHeaderfield 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
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
GroupHeaderfield 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
GroupFieldNamebecomes available for mapping in step 2 of the merge action builder. Thus, corresponding to the above example that uses
GroupHeader:OpenDeals:DealStage, the field
DealStageis 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
GroupHeaderfield 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
GroupHeaderfield 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
GroupHeaderfield. 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 averagesIn 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,
CumSumfor cumulative sum and
CumAvgfor cumulative average. Below is a screenshot of the template that uses the
CumSumfunction to produce the cumulative sum. Some notes:
- The cumulative sum for the
Amtfield of the
Oppsrecord group is produced with the field
«Opps:Amt:CumSum», similar to the other grouping fields described above. Similary, the cumulative average is produced with
- 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
Qtrfield 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 fieldsAnother 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 sublistsIf 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
Countfunction 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:Countin 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 aggregationsTo 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:
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) functionMicrosoft 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.