Functions used in merge fields

Syntax

FilterByValue(FieldName,Operator,Value)

You can decorate the FieldName argument. 

This function supports the following operators: 

• LT: less than

• LE: less than or equal to

• GT: greater than

• GE: greater than or equal to

• EQ: equal to

• NE: not equal to

• IS_NULL: for example,InvoiceItems|FilterByValue(Description,IS_NULL)

• NOT_NULL: for example,InvoiceItems|FilterByValue(Description,NOT_NULL)

Remarks

You can specify the FieldName argument to be dotted paths, but the first part must be an attribute of the input object. For example, in the InvoiceItems|FilterByValue(RatePlanCharge.ChargeType,EQ,OneTime) function, RatePlanCharge is a valid field of the InvoiceItems object. 

You can quote the Value argument, but the value cannot contain the dot character ".". If the value contains the following characters, you must escape (URL Encoding) them:

• Whitespace ' ': %20

• Comma ',':  %2C

• Left curly brackets '{': %7B

• Right curly brackets '}': %7D

Example

If you want to filter all invoice items by comparing the 0 - 7 substring of their service start date with 2021-01, use the following function:

InvoiceItems|FilterByValue(ServiceStartDate|Substr(0,7),EQ,2021-01)

Syntax

FilterByRef(FieldName,Operator,RefAttributeName)

You can decorate the FieldName argument. 

This function supports the following operators: 

• LT: less than

• LE: less than or equal to

• GT: greater than

• GE: greater than or equal to

• EQ: equal to

• NE: not equal to

Remarks

If the current object does not have any RefAttributeName property, its outer object in the stack context is used for searching until the property is found.

Example

InvoiceItem can be the regular charges and the discount charges. As a discount charge, it has an AppliedToInvoiceItemId pointing to a regular invoice item. This linkage enables you to show the inline discount by using FilterByRef function together with Cmd_Assign, see the following example:

{{#InvoiceItems|FilterByValue(ProcessingType,EQ,0)}}

{{Cmd_Assign(RegularItemId,Id)}}

{{ChargeName}} - {{ChargeAmount}}

{{#InvoiceItems|FilterByRef(AppliedToInvoiceItemId,EQ,RegularItemId)}}

 * {{ChargeName}} - {{ChargeAmount}}

{{/InvoiceItems|FilterByRef(AppliedToInvoiceItemId,EQ,RegularItemId)}}

{{/InvoiceItems|FilterByValue(ProcessingType,EQ,0)}}

This example has two list sections. The first one InvoiceItems|FilterByValue(ProcessingType,EQ,0) returns all the regular invoice items.

For each regular invoice item, use Cmd_Assign(RegularItemId,Id) to give Id an alias RegularItemId.

The second list section InvoiceItems|FilterByRef(AppliedToInvoiceItemId,EQ,RegularItemId) basically is to filter all the invoice items by the conditionAppliedToInvoiceItemId = RegularItemId. AppliedToInvoiceItemId is an attribute of its input data InvoiceItems, and RegularItemId is defined in the outer object, also known as the object in the first list section.

Syntax

GroupBy(FieldName,FieldName){0..2})

Remarks

This function supports at most three arguments.

The FieldName argument must be a valid attribute of the input list object type. You can specify the FieldName argument to be dotted paths. 

For example, the InvoiceItems|GroupBy(RatePlanCharge.RatePlan.Name,ChargeName) function groups the invoice items by rate plan name first, and then by charge name for the records with the same rate plan name.

Example

Assume that an input list is as follows:

[
{
  "ChargeName": "C-000001",
  "ChargeAmount": 10,
  "ServiceStartDate": "2021-01-01"
},
{
  "ChargeName": "C-000001",
  "ChargeAmount": 15,
  "ServiceStartDate": "2021-02-01"
},
{
  "ChargeName": "C-000002",
  "ChargeAmount": 12,
  "ServiceStartDate": "2021-01-01"
},
{
  "ChargeName": "C-000002",
  "ChargeAmount": 8,
  "ServiceStartDate": "2021-03-01"
}
]

If you apply the GroupBy(ChargeName) function to the input list, it returns the following output in the rendering result:

[
  {
    "ChargeName": "C-000001",
    "_Group": [
        {
            "ChargeName": "C-000001",
            "ChargeAmount": 10,
            "ServiceStartDate": "2021-01-01"
        },
        {
            "ChargeName": "C-000001",
            "ChargeAmount": 15,
            "ServiceStartDate": "2021-02-01"
        }
    ]
  },
  {
    "ChargeName": "C-000002",
    "_Group": [
        {
            "ChargeName": "C-000002",
            "ChargeAmount": 12,
            "ServiceStartDate": "2021-01-01"
        },
        {
            "ChargeName": "C-000002",
            "ChargeAmount": 8,
            "ServiceStartDate": "2021-03-01"
        }
    ]
  }
]

The preceding example shows how the input list is transformed. The object in the transformed list consists of two fields only:

• GroupBy key field, for example, ChargeName

• _Group that is a hard-coded key for GroupBy use cases

If you specify multiple GroupBy fields in the function, the records in _Group is transformed in the same way. For three levels of GroupBy fields, such asItems|GroupBy(A,B,C), the final output example is as follows:

[
{
    "A": "..",
    "_Group": [
        {
            "B": "..",
            "_Group": [
                {
                    "C": "..",
                    "_Group": [
                        {
                            "A": "..",
                            "B": "..",
                            "C": "..",
                            ...
                        }
                    ]
                }
            ]
        }
    ]
}
]

Syntax

SortBy(FieldName,Direction(,FieldName,Direction){0..2})

This function supports the following directions: 

• ASC: sorting data from the smallest to the largest

• DESC: sorting data from the largest to the smallest

Remarks

You can specify the FieldName argument to be dotted paths.

This function supports at most three pairs of field names and directions.

Example

Assume that you want to show all the charges in the order of service start date, use the following functions:

{{#InvoiceItems|SortBy(ServiceStartDate,ASC)}}

{{ChargeName}} - {{ChargeAmount}} - {{ServiceStartDate}}

{{/InvoiceItems|SortBy(ServiceStartDate,ASC)}}

Syntax

First(N)

Remarks

If less than N records exist, this function returns the original list.

The value of N must be greater than or equal to 1.

Example

The InvoiceItems|First(5) function returns the first five invoice items in the rendering result based on the default sorting order.

Syntax

Last(N)

Remarks

If less than N records exist, this function returns the original list.

The value of N must be greater than or equal to 1.

Example

The InvoiceItems|Last(5) function returns the last five invoice items in the rendering result based on the default sorting order.

Syntax

Map(FieldName|QuotedValue(,FieldName|QuotedValue)*)

• The argument can be either a field name or a quoted value, either single or double quoted.

• FieldName cannot be a dotted path.

• The FieldName data type cannot be a list.  For example, InvoiceItems|Map(TaxationItems) is invalid.

• If you specify only one argument, the Map function returns a list of fieldName values.

• If you specify multiple arguments, the Map function returns a list of lists.

Example

Assume that an InvoiceItems list is as follows:

[
{
  "ChargeName": "C-000001",
  "ChargeAmount": 10,
  "ServiceStartDate": "2021-01-01"
},
{
  "ChargeName": "C-000001",
  "ChargeAmount": 15,
  "ServiceStartDate": "2021-02-01"
},
{
  "ChargeName": "C-000002",
  "ChargeAmount": 12,
  "ServiceStartDate": "2021-01-01"
},
{
  "ChargeName": "C-000002",
  "ChargeAmount": 8,
  "ServiceStartDate": "2021-03-01"
}
]

The InvoiceItems|Map(ChargeName) function returns the following value in the rendering result:

["C-000001","C-000001","C-000002","C-000002"]

The InvoiceItems|Map(ChargeName,ChargeAmount) function returns the following value in the rendering result:

 [["C-000001", 10],["C-000001",15],["C-000002",12],["C-000002",8]]

You can also use quoted values as arguments. For example, the InvoiceItems|Map(ChargeName,'Charge') function returns the following value in the rendering result:

[["C-000001","Charge"],["C-000001","Charge"],["C-000002","Charge"],["C-000002","Charge"]]

Syntax

FlatMap(ListFieldName)

Example

Assume that an input of this function is as follows:

[
{
    "A": "...",
    "L": [
        {"B": "b1"},{"B": "b2"}
    ]
},
{
    "A": "...",
    "L": [
        {"B": "b3"}
    ]
}
]

The FlatMap(L) function returns the following output in the rendering result:

[
  {"B": "b1"},
  {"B": "b2"},
  {"B": "b3"}
]

Remarks

You can use the Uniq function to dedup the rendering result.

Example

An invoice can be generated for multiple subscriptions, which are linked on InvoiceItem according to the object model. Use the following function to get all the subscriptions of this invoice:

Invoice.InvoiceItems|Map(Subscription)

But the problem is that the result contains duplicate records, since there are multiple invoice items for the same subscription.

To get a unique list of the subscription, append a Uniq function to the preceding function:

Invoice.InvoiceItems|Map(Subscription)|Uniq

Syntax

DateAdd(Value,DatePart)

The DateAdd function syntax has the following arguments:

Argument	Description
Value	Required. The number of intervals that you want to add to the part of a date specified in the DatePart argument. The argument value is an integer, and can be positive (to get dates in the future) or negative (to get dates in the past).
DatePart	Required. The part of the date to which the DateAdd function adds the number of intervals. The argument value can be any of the following options: D: stands for days M: stands for months Y: stands for years

Examples

Assume that you want to get payments with the effective date within the past month. To achieve this goal, you can use the following function sample:

{{Cmd_Assign(VarTheInvoiceDate,Invoice.InvoiceDate,True)}}

{{Cmd_Assign(VarTheDate,Invoice.InvoiceDate|DateAdd(-1,M),True)}}

{{#Invoice.Account.Payments|FilterByValue(Status,EQ,Processed)|FilterByRef(EffectiveDate,GT,VarTheDate)|FilterByRef(EffectiveDate,LE,VarTheInvoiceDate)}}

{{PaymentNumber}}, {{EffectiveDate}}

{{/Invoice.Account.Payments|FilterByValue(Status,EQ,Processed)|FilterByRef(EffectiveDate,GT,VarTheDate)|FilterByRef(EffectiveDate,LE,VarTheInvoiceDate)}}

The preceding function sample returns the following output in the rendered result:

P-00000020, 2021-03-20 P-00000019, 2021-03-20 P-00000018, 2021-03-14

Syntax

Default(Value)

The Default(Value) function syntax is as follows:

• If the input data of the Default function is either null or undefined, this function returns the value of its argument.
• If the input data of the Default function is neither null nor undefined, this function returns its input data.

Examples

If you want to look up a custom object by Account.Locale__c to implement globalization, you can use the following function example:

{{Cmd_ListToDict(default__messages|FilterByRef(locale__c,EQ,Invoice.Account.Locale__c),key__c,value__c,Message)}}

The preceding function example uses FilterByRef(locale__c,EQ,Invoice.Account.Locale__c)  to dynamically filter custom object records by an account custom field. However, if no account custom field is defined, the preceding function example returns empty in the rendered result.

In this case, you can append a Default function that uses a default value en_US in case the account custom field is undefined and the Account.Locale__c field is empty. The function example can be as follows:

{{Cmd_ListToDict(default__messages|FilterByRef(locale__c,EQ,Invoice.Account.Locale__c|Default(en__US)),key__c,value__c,Message)}

If the Account.Locale__c field is undefined, the preceding function example returns en_US in the rendered result.

Syntax

Nth(number)

The Nth function syntax has the following arguments:

Argument

Description

number

Required. The value of this argument is an ordinal number that specifies the position of an item in the input list. The argument value is an integer, and can be positive or negative. A positive integer indexes from the beginning of the input list; a negative integer indexes from the end of the input list. For example: Nth(1) returns the first item of the input list. Nth(2) returns the second item of the input list. Nth(-1) returns the last item of the input list Nth(-2) returns the last but one item of the input list.

Remarks

If the input list is undefined, this function always returns null in the rendered result. If the index is out of the boundary, this function always returns null instead of throwing errors.

Examples

If you want to achieve the same goal as one filter option FromLastInvoice is used in the Previous Transaction table in Word templates to get the last but one invoice with the transaction date right before the date the current invoice is posted in the list of Account.Invoices, you can use the following function in HTML templates:

Invoice.Account.Invoices|Nth(-2)

This function returns the last but one item of the invoices associated with the corresponding account. 

Syntax

Sum(FieldName)

Remarks

FieldName must be a numeric field.

Example

Use the following function to get the total charge amount of all invoice items in the rendering result:

InvoiceItems|Sum(ChargeAmount)

Remarks

If the input is null, 0 is returned.

Example

InvoiceItems|Size

Syntax

Min(FieldName)

Remarks

FieldName must be fields of comparable data types, for example:

• Text

• Number

• Date/DateTime

• Boolean

Example

Use the following function to get the oldest invoice item of the InvoiceItems object in terms of ServiceStartDate in the rendering result:

InvoiceItems|Min(ServiceStartDate)

Syntax

Max(FieldName)

Example

Use the following function to get the latest invoice item of the InvoiceItem object in terms of ServiceStartDate in the rendering result:

InvoiceItems|Max(ServiceStartDate)

Example

To check whether an account has any payment, use the following example:

Account.Payments|IsEmpty

Syntax

Substr(Begin,End)

Arguments Begin and End are integers. The value of these arguments must follow the following formula:

0 <= Begin < End

Remarks

If the input text length is less than End, the End value is set to the length of the input list.

For example, if you define the function as Substr(0,10) but the input text has only six characters, all the six characters are considered as the input data.

Syntax

Localise(locale_NAME)

localise_NAME defines a language country code that is composed of two arguments separated by an underscore. For example, fr_CA indicates French Canadian.

• The first argument is a valid ISO Language Code. These codes are the lower-case two-letter codes as defined by ISO-639. 

• The second argument to both constructors is a valid ISO Country Code. These codes are the upper-case two-letter codes as defined by ISO-3166. 

See Supported languages in HTML templates for an example list of locale names.

Remarks

This function works only for date, datetime, and numeric fields.

The localise_NAME input parameter is optional. 

If no input parameter is specified, the default locale is determined based on the following order:

• Locale of the Communication Profile

• Locale in the tenant setting

Example

You can specify the locale name to override the default locale. The default locale is determined based on the following order:

• Use the locale specified in the communication profile

• Use the tenant locale if the communication profile specifies the locale as DEFAULT LOCALE.

For example, if the default locale is de_DE, the merge field examples and their outputs are as follows:

• {{Invoice.InvoiceDate|Localise}}: 16/12/2021
• {{Invoice.InvoiceDate|Localise(it_IT}}: 16.12.2021
• {{Invoice.InvoiceDate|Localise}}: 16.09.2021 02:26:07+08:00

You can specify the locale name to override the default locale. For example, if the default locale is de_DE, the merge field examples and their outputs are as follows: 

• {{Amount|Localise}}: 1,234,567.89
• {{Amount|Localise(fr_FR)}}: 1.234.567,89

Syntax

EqualToVal(Value)

Remarks

The input of this function must be a scalar value.

Use EqualToVal(0) to compare numbers, and it has the same behavior as EqualToVal(0.00).

Use EqualToVal("0") to compare strings.

Example

{{#Account.Status|EqualToVal(Active)}}
Handle Active Account
{{/Account.Status|EqualToVal(Active)}}

Remarks

To check whether the Description property of the Account object is blank, use the following merge field:

Account.Description|IsBlank

Example

{{Account.Currency|Symbol}} {{Invoice.Balance|Localise}}

The output of the example can be displayed as $ 1,000.00.

Syntax

Round(Precision,RoundingMode)

• The first argument is an integer between 0 and 10.

• The second argument is optional. The default value of the second argument is HALF_UP. This argument has the following available options:

  • UP
  • DOWN
  • CEILING
  • FLOOR
  • HALF_UP
  • HALF_DOWN
  • HALF_EVEN
  • UNNECESSARY

Example

Assume that an invoice has the amount of 10.125.

{{Amount|Round(2)}}returns 10.13.

You can also use the Round function for padding purposes.

{{Amount|Round(4)}} returns 10.1250.