Knowledge Center

Knowledge Center > Billing and Payments > Invoices > Creating a Custom Invoice Template > Grouping and Subtotal Functions in Nested Tables > Examples: Using Nested Tables in Invoices

Examples: Using Nested Tables in Invoices

This article gives you two examples on how to configure nested tables in your invoice templates:

To download the invoice templates that are used in the examples, see Sample Nested Table Templates.

Example of Creating a Usage Nested Table

This example shows how to create a usage nested table with the following layouts on your invoice: 

  • Grouped by a custom field, Billing Code
  • Subtotaled by a supported merge field, Rating Amount
  • Sorted in ascending order by Charge Date

The following figure shows the generated invoice:

Sample generated invoice for usage

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 1]

Usage Details

[ROW 2]

Date

Subscription Number

Charge Description

Quantity

Amount

[ROW 3:
Outer Table]

«TableStart:Usage» «NestedTable:GroupColumn Usage.BillingCode__c» «NestedTable:ValueColumn Usage.RatingAmount»

Billing Code: «Usage.BillingCode__c»

[ROW 3A:
Inner Table]

«TableStart:Usage_01» «Usage_01.Date»

«Usage_01.SubscriptionNumber»

«Usage_01.ProductChargeDescription»

«Usage_01.quantity»>

«Usage_01.RatingAmount»

«TableEnd:Usage_01»

Subtotal: «Usage.RatingAmount»«TableEnd:Usage»

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

[ROW 1]

Usage Details

[ROW 2]

Date

Subscription Number

Charge Description

Quantity

Amount

[ROW 3:
Outer Table]

«TableStart:Usage» «NestedTable:GroupColumn Usage.BillingCode__c» «NestedTable:ValueColumn Usage.RatingAmount»

Billing Code: «Usage.BillingCode__c»

[ROW 3A:
Inner Table]

«TableStart:Usage_01»

«TableSort:Usage_01.RatingAmount ACS» 

«Usage_01.Date»

«Usage_01.SubscriptionNumber»

«Usage_01.ProductChargeDescription»

«Usage_01.quantity»>

«Usage_01.RatingAmount»

«TableEnd:Usage_01»

Subtotal: «Usage.RatingAmount»«TableEnd:Usage»

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.

[ROW 1]

Usage Details

[ROW 2]

Date

Subscription Number

Charge Description

Quantity

Amount

[ROW 3:
Outer Table]

«TableStart:Usage» «NestedTable:GroupColumn Usage.BillingCode__c» «NestedTable:ValueColumn Usage.RatingAmount» «TableSort:Usage.BillingCode__c ACS» 

Billing Code: «Usage.BillingCode__c»

[ROW 3A:
Inner Table]

«TableStart:Usage_01»

«TableSort:Usage_01.RatingAmount ACS» 

«Usage_01.Date»

«Usage_01.SubscriptionNumber»

«Usage_01.ProductChargeDescription»

«Usage_01.quantity»>

«Usage_01.RatingAmount»

«TableEnd:Usage_01»

Subtotal: «Usage.RatingAmount»«TableEnd:Usage»

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.

Example of Creating Invoice Item Nested Tables

This example shows how to create three level nested tables with the following layouts on your invoice: 

  1. Group the invoice items by charges.
    • Group 1: New Charges. The invoice item charges that are not displayed in the most recently posted invoice. 
    • Group 2: Old Charges. The invoice item charges that are displayed in the most recently posted invoice.
  2. For the invoice items in the same charge group, group them by rate plans.
  3. For the invoice items in the same rate plan group, list the following information for each invoice item:
    • Quantity
    • Unit Price
    • Subscription Number
    • Tax Amount
    • Amount Without Tax

The following figure shows the generated invoice:

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 1]

CHARGE DETAILS

[Row 2]

Quantity

Unit Price

Subscription Number

Tax Amount

Amount Without Tax

[Row 3A: Outer Table]

«TableStart:InvoiceItem» «NestedTable:GroupColumn InvoiceItem.IsNew» «NestedTable:ValueColumn InvoiceItem.AmountWithoutTax»

{IF «InvoiceItem.IsNew»="true" "New Charges" "Old Charges"} Subtotal_Amount: «InvoiceItem.AmountWithoutTax»

[Row 3B
Outer Table]

«TableStart:InvoiceItem_RatePlan» «NestedTable:GroupColumn InvoiceItem_RatePlanName» «NestedTable:ValueColumn InvoiceItem_RatePlan.AmountWithoutTax» «TableSort:InvoiceItem_RatePlan.RatePlanName»

Rate Plan Name: «InvoiceItem_RatePlan.RatePlanName» Subtotal_Amount: «InvoiceItem_RatePlan.AmountWithoutTax»

[Row 3C
Inner Table]

«TableStart:InvoiceItem_1» «InvoiceItem_1.Quantity»

«InvoiceItem_1.UnitPrice»

«InvoiceItem_1.SubscriptionNumber»

«InvoiceItem_1.TaxAmount»

«InvoiceItem_1.AmountWithoutTax»

«TableEnd:InvoiceItem_1»

«TableEnd:InvoiceItem_RatePlan»

«TableEnd:InvoiceItem»

  • 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}​
Last modified
01:29, 20 Jul 2017

Tags

This page has no custom tags.

Classifications

(not set)