Invoice Template Formula Reference Guide

1. Introduction

The Invoice Template builder lets you define how data appears in each section of a generated invoice — Header, Columns, and Footer. By default, each field displays a single data value. Enabling the Formula toggle unlocks a formula expression editor where you can combine fields, apply functions, and build calculated values.

Where Formulas Are Available

Formula fields can be used in:

• Header fields
• Column fields (table rows)
• Footer fields

How to Enable a Formula Field

1. Open or add a field in the Header, Column, or Footer section.
2. Toggle the Formula switch at the top of the dialog to ON.
3. The editor switches to formula mode, showing a toolbar with Category, Function, and Data Field dropdowns.

Using the Formula Builder Toolbar

• Category — filters the available data fields by type (for example, Billing Item, Daily, Expense).
• Data Field — inserts a field reference into the formula at the cursor position.
• Function — inserts a function template (for example, CONCAT(field1, field2)) at the cursor position.

Formula Editor Color Coding

• Function names appear in brown
• Field references {{FIELD_NAME}} appear in blue
• Parentheses and brackets appear in gray

2. Field Reference Syntax

To reference a data field inside a formula, wrap its name in double curly braces:

{{FIELD_NAME}}

Examples:

{{AMOUNT}}
{{CURRENCY_CODE}}
{{FIRST_NAME}}
{{INVOICE_NUMBER}}

Fields can be used directly as values, passed into functions, or combined with operators:

{{AMOUNT}} + {{TAX}}
CONCAT({{FIRST_NAME}}, " ", {{LAST_NAME}})
CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, 0)

3. Operators

3.1 Arithmetic Operators

Use arithmetic operators to perform calculations between field values and numeric literals.

Operator	Description	Example
+	Addition	{{REGULAR_HOURS}} + {{OVERTIME_HOURS}}
-	Subtraction	{{GROSS_AMOUNT}} - {{DISCOUNT}}
*	Multiplication	{{HOURS}} * {{RATE}}
/	Division	{{TOTAL_AMOUNT}} / 2

Operators can be combined and grouped with parentheses:

({{REGULAR_HOURS}} + {{OVERTIME_HOURS}}) * {{HOURLY_RATE}}

3.2 Comparison Operators

Comparison operators evaluate a condition and return true or false. They are primarily used inside CASE conditions.

Operator	Description	Example
==	Equal to	{{CURRENCY_CODE}} == "INR"
!=	Not equal to	{{STATUS}} != "Cancelled"
<	Less than	{{HOURS}} < 40
<=	Less than or equal to	{{HOURS}} <= 40
>	Greater than	{{AMOUNT}} > 1000
>=	Greater than or equal to	{{AMOUNT}} >= 500

3.3 Logical Operators

Logical operators combine multiple conditions into a single expression.

Operator	Description	Example
&&	AND — both conditions must be true	{{ACTIVE}} == "Y" && {{ELIGIBLE}} == "Y"
||	OR — at least one condition must be true	{{TYPE}} == "FT" || {{TYPE}} == "PT"

Example combining AND with CASE:

CASE({{COUNTRY}} == "US" && {{HOURS}} > 40, {{OVERTIME_RATE}}, {{STANDARD_RATE}})

→ Returns OVERTIME_RATE only when country is "US" AND hours exceed 40; otherwise STANDARD_RATE.

4. Functions

4.1 String Functions

CONCAT

Joins two or more values together. Null values are automatically skipped.

Syntax: CONCAT(value1, value2, ...)

CONCAT({{FIRST_NAME}}, " ", {{LAST_NAME}})
CONCAT({{BILL_CLIENT_INVOICE_NUMBER}}, {{BILL_DOWN_FEE_NUMBER}})
CONCAT("INV-", {{INVOICE_NUMBER}}, "-", {{YEAR}})

FIRST

Returns the first N characters of a value.

Syntax: FIRST(n, value)

FIRST(3, {{INVOICE_NUMBER}})

→ If INVOICE_NUMBER is "INV-2024-001", result is "INV".

LAST

Returns the last N characters of a value.

Syntax: LAST(n, value)

LAST(4, {{ACCOUNT_CODE}})

→ If ACCOUNT_CODE is "GL-4500", result is "4500".

SUBSTR

Extracts a portion of a string. The start index is 1-based (the first character is position 1).

Syntax: SUBSTR(value, startIndex, length)

SUBSTR({{INVOICE_REF}}, 3, 5)

→ Starting at character 3, extracts 5 characters.

SPLIT

Splits a value by a delimiter and returns the part at the given index. The index is 1-based.

Syntax: SPLIT(value, delimiter, index)

SPLIT({{FULL_ADDRESS}}, ",", 1)
SPLIT({{PROJECT_CODE}}, "-", 2)

→ Returns the text before the first comma. → Returns the second segment of a hyphen-delimited code.

REPLACE

Replaces all occurrences of a target substring with a replacement.

Syntax: REPLACE(value, target, replacement)

REPLACE({{DESCRIPTION}}, "N/A", "Not Available")
REPLACE({{NOTES}}, " ", "_")

LENGTH

Returns the number of characters in a value. Returns 0 if the value is null.

Syntax: LENGTH(value)

LENGTH({{NOTES}})
LENGTH({{DESCRIPTION}})

4.2 Null Handling Functions

NULLTOZERO

Returns 0 if the field value is null. Use this before performing arithmetic on optional numeric fields.

Syntax: NULLTOZERO(value)

NULLTOZERO({{OVERTIME_HOURS}})
NULLTOZERO({{AMOUNT}}) + NULLTOZERO({{TAX}})

NULLTOEMPTY

Returns an empty string if the field value is null.

Syntax: NULLTOEMPTY(value)

NULLTOEMPTY({{MIDDLE_NAME}})
CONCAT({{FIRST_NAME}}, " ", NULLTOEMPTY({{MIDDLE_NAME}}), " ", {{LAST_NAME}})

4.3 Conditional Function: CASE

Evaluates a series of conditions in order and returns the value associated with the first true condition. An optional final argument is returned when no condition matches (the "else" value).

Syntax:

CASE(condition1, result1, condition2, result2, ..., elseValue)

Using == (equal to):

CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, 0)
CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, {{CURRENCY_CODE}} == "USD", {{USA_TAX_AMOUNT}}, 0)

→ First formula: returns TAX_AMOUNT for INR; else 0. Second formula: TAX_AMOUNT for INR, USA_TAX_AMOUNT for USD, else 0.

Using != (not equal to):

CASE({{STATUS}} != "Cancelled", {{AMOUNT}}, 0)

→ Returns AMOUNT for every status except "Cancelled"; returns 0 for cancelled records.

Using < / <= (less than):

CASE({{HOURS}} <= 40, "Regular", "Overtime")
CASE({{AMOUNT}} < 500, "Low", {{AMOUNT}} < 1000, "Medium", "High")

→ First: "Regular" for 40 h or fewer; else "Overtime". Second: classifies amount into Low/Medium/High.

Using > / >= (greater than):

CASE({{AMOUNT}} >= 10000, {{AMOUNT}} * 0.05, {{AMOUNT}} >= 5000, {{AMOUNT}} * 0.03, 0)

→ 5% discount for amounts ≥ 10,000; 3% for amounts ≥ 5,000; no discount otherwise.

Using && (AND) inside a CASE condition:

CASE({{COUNTRY}} == "US" && {{HOURS}} > 40, {{OVERTIME_RATE}}, {{STANDARD_RATE}})

→ Overtime rate only when country is "US" AND hours exceed 40.

Using || (OR) inside a CASE condition:

CASE({{STATUS}} == "Pending" || {{STATUS}} == "Under Review", "In Progress", {{STATUS}})

→ Displays "In Progress" for Pending or Under Review; otherwise the status as-is.

4.4 Membership Functions

CONTAINS

Returns true if a string contains a given substring.

Syntax: CONTAINS(source, value)

CONTAINS({{DESCRIPTION}}, "Premium")
CASE(CONTAINS({{NOTES}}, "urgent"), "Priority", "Standard")

IN

Returns true if a value matches any of the listed options.

Syntax: IN(value, option1, option2, ...)

IN({{COUNTRY}}, "USA", "Canada", "Mexico")
CASE(IN({{CURRENCY_CODE}}, "INR", "USD", "EUR"), {{AMOUNT}}, 0)

NOTIN

Returns true if a value does NOT match any of the listed options. Inverse of IN.

Syntax: NOTIN(value, option1, option2, ...)

NOTIN({{STATUS}}, "Cancelled", "Rejected")
CASE(NOTIN({{COUNTRY}}, "US", "CA"), {{FOREIGN_TAX}}, 0)

4.5 Numeric Functions

ROUND

Rounds a numeric value to a specified number of decimal places (half-up rounding).

Syntax: ROUND(value, scale)

ROUND({{TOTAL_AMOUNT}}, 2)
ROUND({{HOURS}} * {{RATE}}, 2)
ROUND(NULLTOZERO({{TAX_AMOUNT}}) / 2, 2)

4.6 Date Functions

ADDDAYS

Adds a number of days to a date field.

Syntax: ADDDAYS(dateField, days)

ADDDAYS({{START_DATE}}, 7)
ADDDAYS({{INVOICE_DATE}}, 30)

SUBSTRACTDAYS

Subtracts a number of days from a date field.

Syntax: SUBSTRACTDAYS(dateField, days)

SUBSTRACTDAYS({{END_DATE}}, 30)
SUBSTRACTDAYS({{DUE_DATE}}, 7)

5. Nesting Functions

Functions can be nested — the result of an inner function is passed as an argument to the outer function. When reading a nested formula, work from the inside out: the innermost expression is evaluated first.

Null-safe arithmetic

Wrap optional numeric fields in NULLTOZERO before calculating to avoid errors:

NULLTOZERO({{AMOUNT}}) + NULLTOZERO({{TAX}})

Round a calculated result

ROUND(NULLTOZERO({{TAX_AMOUNT}}) / 2, 2)

Step by step:

1. NULLTOZERO({{TAX_AMOUNT}}) → converts null to 0
2. ... / 2 → divides by 2
3. ROUND(..., 2) → rounds to 2 decimal places

Conditional + null-safe + arithmetic (full pattern)

ROUND(NULLTOZERO(CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, 0)) / 2, 2)

Step by step:

1. CASE(...) → picks TAX_AMOUNT if currency is INR, else 0
2. NULLTOZERO(...) → turns null result into 0
3. ... / 2 → halves the value
4. ROUND(..., 2) → rounds to 2 decimal places

String nesting — build a formatted code

CONCAT(FIRST(3, {{INVOICE_NUMBER}}), "-", SUBSTR({{ACCOUNT_CODE}}, 1, 4))

→ First 3 chars of invoice number + hyphen + first 4 chars of account code.

CASE with a function as the result

CASE({{STATUS}} == "Active", CONCAT({{FIRST_NAME}}, " ", {{LAST_NAME}}), "Inactive User")

→ Returns full name for active records; "Inactive User" otherwise.

Membership check driving a CASE

CASE(IN({{COUNTRY}}, "US", "CA", "MX"), "North America", "Other")

→ "North America" for US, Canada, or Mexico; "Other" for all other countries.

6. Real-World Examples

The following examples are based on real invoice template use cases.

Example 1 — Single-Currency Tax (Half Amount)

Goal: Show half the INR tax amount; show 0 for all other currencies.

NULLTOZERO(CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, 0)) / 2

• CASE(...) returns TAX_AMOUNT when currency is INR, else 0
• NULLTOZERO(...) handles the case where TAX_AMOUNT itself is null
• / 2 divides the result by 2

Example 2 — Multi-Currency Tax (Half Amount)

Goal: Show half the tax for INR or USD currencies; 0 for all others.

NULLTOZERO(CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, {{CURRENCY_CODE}} == "USD", {{USA_TAX_AMOUNT}}, 0)) / 2

• Two condition/value pairs inside CASE: one for INR, one for USD
• Final 0 is the else value for all other currencies
• Wrapped in NULLTOZERO to guard against null fields

Example 3 — Multi-Currency Tax (Full Amount, No Null Guard)

Goal: Return the appropriate tax field per currency without dividing.

CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, {{CURRENCY_CODE}} == "USD", {{USA_TAX_AMOUNT}}, 0)

Use this form when null values are not a concern and the full amount is needed.

Example 4 — Single-Currency Tax (Full Amount, with Null Guard)

Goal: Return the INR tax amount or 0, null-safe.

CASE({{CURRENCY_CODE}} == "INR", {{TAX_AMOUNT}}, 0)

Example 5 — Concatenated Invoice Header Field

Goal: Combine two billing fields into a single header value.

CONCAT({{BILL_CLIENT_INVOICE_NUMBER}}, {{BILL_DOWN_FEE_NUMBER}})

→ Produces a merged invoice reference in the header column.

Example 6 — Tiered Billing Rate

Goal: Apply a higher rate for workers exceeding 40 hours, only in the US.

CASE({{COUNTRY}} == "US" && {{HOURS}} > 40, {{OVERTIME_RATE}}, {{STANDARD_RATE}})

Example 7 — Safe Full Name

Goal: Build a full name, treating null middle name as empty.

CONCAT({{FIRST_NAME}}, " ", NULLTOEMPTY({{MIDDLE_NAME}}), " ", {{LAST_NAME}})

7. Tips & Limitations

Topic	Guidance
Field names	Case-sensitive. Always insert using the Data Field dropdown to avoid typos.
String literals	Must use double quotes: "INR", not 'INR'.
Null fields	Wrap optional numeric fields in NULLTOZERO() before arithmetic. Wrap optional text fields in NULLTOEMPTY() before string operations.
SUBSTRACTDAYS spelling	The function is named SUBSTRACTDAYS (one 'T'). Use the Function dropdown to insert it.
Division by zero	Avoid dividing by a field that could be 0. Guard with CASE: CASE({{DIVISOR}} != 0, {{VALUE}} / {{DIVISOR}}, 0)
Formula validation	The editor validates your formula on save. A red error means invalid syntax — check that all {{ }} references are complete.
Operator precedence	Standard math rules apply: * and / before + and -. Use parentheses to control evaluation order.
Header/Footer vs Columns	Header and Footer formula editors do not include the Custom Field Object toolbar button. Column formulas have access to the full toolbar.