Document templates - Calculations using Merge Fields

Calculations are quiet advanced and can be tricky to get right.

If there is a value that divides by zero you will get a “REF#” error so ensure that any calculations done are tested before used in the live system.

Calculations

If you want to do any calculation for Merge Fields, follow the following steps:

Open Word document
Press Ctrl and F9 on your keyboard – this will create the expression field { }.
Click inside the expression field and insert your merge field
Right-click on the mail merge field and select ‘Toggle Field Codes’
1. This will display the merge field nested inside an expression field
From here you can do the calculations on the merge field.
1. For example, to add 1 to the age field place an equals symbol (=) before the between the two open brackets and plus 1 (+ 1) between the two close brackets.
2. For example : to display 50% of the total amount in Quote Document { = { Mergefield TotalIncGST} /2 }
Press Alt+F9 to switch to Normal view
Save document and upload

Other useful Merge field codes

\* Caps	Capitalizes the first letter of each word.
\* FirstCap	Capitalizes the first letter of the first word.
\* Upper	Capitalizes all letters.
\* Lower	All letters are in lower case.

Switch	Description

Switch	Description
0 (zero)	Specifies the minimum numeric places to display including leading zeroes. EXAMPLE `{MERGEFIELD current_interest_rate \# 00.00}` displays 05.00
#	Specifies the minimum numeric places to display. If a place has no digit, it is replaced with a space. EXAMPLE `{MERGEFIELD payment_amount \# $###}` displays $ 10
x	Does not display digits to its left and if used to the right of a decimal point, the result is rounded to that place. EXAMPLE `{MERGEFIELD cifno \# x##}` displays 111 if cifno is 123111 `{MERGEFIELD current_interest_rate \# 0.00x}` displays 0.599 if current_interest_rate is .59911
. (decimal point)	Places a decimal point in its position.
, (comma)	Places a digit grouping symbol (thousand separator) as specified in the regional settings in the Control Panel. EXAMPLE `{MERGEFIELD current_payoff_balance \# $,0.00}` displays $1,234.56
(minus)	Places a minus sign on a negative result or adds a space if the result is positive or zero. EXAMPLE `{MERGEFIELD current_payoff_balance \# "$#,##0.00;- $#,##0.00"}` displays $1,234.56 for a positive number and - $1,234.56 for a negative number
+ (plus)	Places a plus sign to a positive result, a minus sign to a negative result, or a space if the result is zero. EXAMPLE `{MERGEFIELD current_payoff_balance \# "+ $#,##0.00"}` displays + $1,234.56 for a positive number and - $1,234.56 for a negative number
() (parenthesis)	Places negative number within parenthesis and a hyphen in place of zero. EXAMPLE `{MERGEFIELD current_payoff_balance \# "$,0.00;($,0.00);'-'"}` displays $1,234.56 for a positive, (1,234,56) for a negative number, and - for zero.
h or H	Hours. Lower case h denotes 12-hour format and upper case H denotes 24-hour format. Use hh or HH to display a leading zero on single digit hours.
m	Minutes. Use mm to display a leading zero on single digit minutes.
s	Seconds. Use ss to display a leading zero on single digit seconds.
AM/PM	Displays AM or PM.

Switch	Description

Switch	Description
\b	Specifies text to be inserted before the merge field if the field is not blank
\f	Specifies text to be inserted following the merge field if the field is not blank

EXAMPLE

{MERGEFIELD firstname1 \f " "}{MERGEFIELD middlename1 \f " "}{MERGEFIELD lastname1}

If all fields are not blank, the result will be Mary Jane Smith

If middlename1 is blank, the result will be Mary Smith