Examples: Using nested tables in invoices

Group usage details and subtotals in a nested table

To generate the invoice with the above layouts, you need to configure the nested table with the NestedTable:GroupColumn and NestedTable:ValueColumn merge fields. The first column in the table is the legend for identifying the rows.

• Row 3 represents the outer table: Usage. Insert the following merge fields in this row:
  • Start outer Table:{MERGEFIELD TableStart:Usage \* MERGEFORMAT}
  • Define merge field to group by: {MERGEFIELD "NestedTable:GroupColumn Usage.BillingCode__c" \* MERGEFORMAT}
  • Define merge field to calculate the subtotal: {MERGEFIELD "NestedTable:ValueColumn Usage.RatingAmount" \* MERGEFORMAT}
  • Define merge field to display the subtotal: {MERGEFIELD Usage.RatingAmount \* MERGEFORMAT}
  • End the outer table: {MERGEFIELD EndTable:Usage \* MERGEFORMAT}
• Row 3A represents the nested, inner table: Usage_01. Insert a table inside row 3.  
  • Replace the Usage object in merge fields in all columns with Usage_01.
  • First column: Start the inner table: {MERGEFIELD TableStart:Usage_01 \* MERGEFORMAT}​
  • Middle columns: No changes required except to update the object name.
  • Last column: Reference the Rating Amount line item and end the inner table: {MERGEFIELD Usage_01.RatingAmount \* MERGEFORMAT}{MERGEFIELD EndTable: Usage_01 \* MERGEFORMAT}​
• For testing purposes, import the required usage data plus the following fields:
  • Usage custom field: Usage.BillingCode__c or any Usage custom field that you define
  • Usage.RatingAmount

Sort grouped usage data

You can use the TableSort merge field to specify the sorted order for data in nested tables. See Changing the Sort Order on a Table for a detailed explanation of the TableSort merge field.

To sort on a column in a nested table, indicate the sort parameter by choosing the desired nested table column. In the following example, suppose you would like to display items sorted in ascending order by RatingAmount. After the TableStart declaration of nested table Usage_01, insert {MERGEFIELD "TableSort:Usage_01.RatingAmount ASC" \* MERGEFORMAT}. Make sure to use the inner table name (Usage_01 in this example) in the TableSort declaration. 

The following shows the inserted TableSort merge field in red.

You can use the TableSort directive in both the outer and inner tables to sort by different parameters.

For example, expanding on the previous example, in which we sorted the inner table on RatingAmount, we will show how to sort the outer table on the custom field BillingCode__c in ascending order. After the NestedTable:ValueColumn declaration, insert {MERGEFIELD "TableSort:Usage.BillingCode__c ASC" \* MERGEFORMAT}. Note the use of the name of the outer table (Usage) in the reference to Usage.BillingCode__c. Because this declaration is in the outer table scope, we must use the outer table name or the template validation will fail.

The following table shows the inserted merge field in red.

The resulting invoice will have a usage table grouped by Usage.BillingCode__c sorted in ascending alphabetical order. Each row will have a nested table sorted by Usage.RatingAmount in ascending numerical order.

Group charge details and subtotals in nested tables

To generate the invoice with the above layouts, you need to configure the nested table with the NestedTable:GroupColumn and NestedTable:ValueColumn merge fields.

The first column in the table is the legend for identifying the rows.

• Row 3A represents the outer table: InvoiceItem. Insert the following merge fields in this row:
  • Start the outer table: {MERGEFIELD TableStart:InvoiceItem \* MERGEFORMAT}​
  • Define a merge field to group by: {MERGEFIELD "NestedTable:GroupColumn InvoiceItem.IsNew" \* MERGEFORMAT}
  • Define a merge field to calculate the subtotal. In this example, it calculates amount without tax: {MERGEFIELD "NestedTable:ValueColumn InvoiceItem.AmountWithoutTax" \* MERGEFORMAT}
  • Define IF field to test the value of the InvoiceItem.IsNew merge field. If the test value is true, display "New Charges". If the test value is false, display "Old Charges": {IF «InvoiceItem.IsNew»="true" "New Charges" "Old Charges" \* MERGEFORMAT}
  • Define a merge field to display amount without tax: {MERGEFIELD InvoiceItem.AmountWithoutTax \* MERGEFORMAT}
  • End the outer table: {MERGEFIELD TableEnd:InvoiceItem \* MERGEFORMAT}​​ ​​​​​​
• Row 3B represents the nested, outer table: InvoiceItem_RatePlan. Insert a table inside row 3A.  
  • Start the outer table: {MERGEFIELD TableStart:InvoiceItem_RatePlan \* MERGEFORMAT}​
  • Define a merge field to group by: {MERGEFIELD "NestedTable:GroupColumn InvoiceItem_RatePlanName" \* MERGEFORMAT}
  • Define a merge field to calculate the subtotal. In this example, it calculates amount without tax: {MERGEFIELD "NestedTable:ValueColumn InvoiceItem.AmountWithoutTax" \* MERGEFORMAT}
  • Define a merge field to sort by rate plan rate: {MERGEFIELD "InvoiceItem_RatePlan.RatePlanName" \* MERGEFORMAT} 
  • Define a merge field to display rate plan name: {MERGEFIELD InvoiceItem.RatePlanName \* MERGEFORMAT}
  • Define a merge field to display amount without tax: {MERGEFIELD InvoiceItem.AmountWithoutTax \* MERGEFORMAT}
  • End the outer table: {MERGEFIELD TableEnd:InvoiceItem_RatePlan \* MERGEFORMAT}​
• ​Row 3C represents the nested, inner table: InvoiceItem_1. Insert a table inside row 3B.  
  • First column, start the inner table: {MERGEFIELD TableStart:InvoiceItem_1 \* MERGEFORMAT}​
  • Middle columns: No changes required except to update the object name.
  • Last column, reference the Amount Without Tax line item and end the inner table: {MERGEFIELD InvoiceItem_1.AmountWithoutTax \* MERGEFORMAT}{MERGEFIELD EndTable: InvoiceItem_1 \* MERGEFORMAT}​