Word Invoice Template Mail Merge Tips

LogiSense Billing utilizes Microsoft Word and mail merge to generate invoice templates and credit note templates. There are some general configuration settings that can be configured in the LogiSense Billing admin portal which will apply to the template as a whole. The rest of the settings will be controlled directly within the formatting in the word document.

Template Settings

Setting	Options	Purpose

Setting	Options	Purpose
Locale	Locales configured within the system (i.e. English - Canada, French - Canada)	This is used to determine the translation that will be used for the tokens provided in the template.
Date	Various Date Formatting Options: Open	Sets the overall date token format for the document. If this is not set, the template formats will be used. (if you need to use different date formats within the template, this configuration should be left empty).
Time	Various Time Formatting Options: Open	Sets the overall time token format for the document. If this is not set, the template formats will be used. (if you need to use different time formats within the template, this configuration should be left empty)
Currency Precision	Numeric	This is the value after the decimal place, for currency values.

Mail Merge Tips & Tricks

Word mail merge is generally pretty easy to use. Google can quickly surface some easy tips on how to effectively manipulate and transform the data supplied by LogiSense tokens in your template. Below are some tips that we found useful.

• Make sure you use the actual mail merge fields. Typing the brackets yourself seems easy but doesn’t actually work. To quickly insert a field, use CTRL+F9

• Toggle field codes to view formulas and formatting using ALT+F9. (if you want to see all field codes, do a CTRL+A first to highlight everything in your document)

• If you don’t have “toggle field codes” on, make sure to edit the MERGEFIELD name using Word’s built in function as opposed to directly editing an inserted MERGEFIELD name. (Right click, Edit Field) If you edit it directly, the name doesn’t actually get updated

• Table rows can not be dynamically included or excluded based on conditional statements, within the table itself. An entire table can be displayed (or not), conditionally

• Word will truncate the displayed name of the merge field at a certain number of characters

• Formatting Dates - Toggle Field Codes

  • Dates can be formatted using the “\@” switch after the merge field.

  • You will need to encapsulate the format options in double quotes (i.e. ““dd-MMM-yyyy””)

  • Day formats are: “d” (no leading zero) or “dd” (with a leading zero)

  • Month formats are: “M” (no leading zero), “MM” (leading zero), “MMM” (abbreviated month name) and “MMMM” (display the full month name)

  • Year formats are: “yy” (two digit year) and “yyyy” (four digit year)

  • Examples based on a date of February 18, 2020:

    • “dd-MMM-yyyy” = 18-FEB-2020

    • “MMMM dd, yyyy” = February 18, 2020

    • “MM/dd/yy” = 02/18/20

  • If my field token is “invoice.date” and I want the second date format from a above, my expanded field would look like this: {MERGEFIELD invoice.date \@ “MMMM dd, yyyy”}

• Basic Mathematics - Toggle Field Codes

  • Basic mathematical operations can be performed within a field, based on token data.

  • If I wanted to sum the values of two merge fields (i.e. invoice.total and invoice.totalTax), my field code would look like this:

    • { {MERGEFIELD invoice.total} + {MERGEFIELD invoice.totalTax} }

• Tables:

  • Tables can be used for displaying the detailed items from invoice data

  • Table start and table end tags must exist within a cell that contains data (they cannot exist in blank rows before/after your data)

  • Table data can also be sorted, within the TableStart and TableEnd tags, using the TableSort feature

  • An example is below:

• Conditions

  • IF conditions can be used to determine the value (if any) of what shows up on your template

  • The IF condition must be inside field tags and can evaluate the value of a token/variable

  • The following would display “Amount Owing” if the amount owing is greater than zero, or “Credit” if there is a negative balance, prior to displaying the balance on the invoice:

    • {IF {MERGEFIELD invoice.balance} >= 0 “Amount Owing” “Credit”}{MERGEFIELD invoice.balance}