Customising Statements - Using HTML Templates

A statement is generated using a template defined using HTML. The flow is basically as follows, where a statement (aka billing run) selects a template to use, and that template can then be generated to different outputs, email, pdf, printed

Account Billing Run	»	HTML Template A	»	Email
		HTML Template B		PDF

Creating and Editting Statement Formats

To maintain statement formats, go into "settings" and then "Account Handling". On this page is a link to access the statement format editor. There is also a link to access remittance formats if you want to use remittances.

The editor appears are shown below. It consists of three sections:

1. Template Definition. This is the actual HTML and Fieldpine tags that define the statement.
2. Technical Preview. This shows the HTML as rendered, but without undergoing symbol replacement.
3. Data Preview. This section shows the final output after symbol and conditional replacement. ie This is what customers will actually see. For the preview to work, you need to enter a billing run number and click "Render" each time. The process of rendering the output can be computationally expensive so isn't constantly performed.

   Tip. If you have not run any statments yet at all, create a test account, sell something on account and run a statement for just that account. Then you will have an actual billing run statement to preview

Template Definition

The template definition is simply an HTML document. You can use all HTML options as the statement is rendered by something called a headless browser. This is an invisible web browser that draws the HTML and then outputs the desired format, such as a PDF file.

For most sites, we recommend starting with a statement format that is broadly what you want and changing that as required. This is easier than starting from scratch with a new blank format.

You can use javascript if required, but are given a limited time budget, so long running scripts may be terminated before completion.

Symbols

To insert statement specific data, called symbol replacement, you insert strings like "%billrun.account.name%" These always start and end with percent signs (%). The symbol %billrun.account.name% can be translated as (1) for this billing run/statement, (2) go to the assocated account, (3) extract the account name. Symbols can include a formatting control, which is a special value after the symbol name followed by a slash (/). For example in "%billrun.createdate/dt[dd-mm-y]%" the "/dt[dd-mm-y]" part instructs the parser to format the value %billrun.createdate% as a date with the specific format.

In general, symbols can be considered something you lookup and cut/paste as required.

Conditionals

If you need to optionally include content in the HTML depending on the value of a symbol, you can use the <fieldpine-if> tag. This has several forms and allows you to test symbols. There are several examples further down this page showing fieldpine-if conditions being used.

Repeat Blocks

As a billing run can have from 0 to N items to display, the tag <fieldpine-repeat> is used to repeat a block of HTML for each item. So you define how one item appears and then wrap that chunk of HTML in a repeat block to draw all the items.

// Lets draw a table of date/debit/credit

// First we create the table header
<table><thead><tr><td>Date</td><td>Debit</td><td>Credit</td></tr></thead><tbody>

// Then we start a repeat block.  The keyword "billrun" means we are repeating for each item in the billing run.  ie each sale invoice or adjustment
<fieldpine-repeat billrun>

// Insert the HTML to render a single item
<tr style="border-bottom:0;border-top:0">
<td nowrap valign='top'>
%billrun.item[].completeddt/dt[dd-mm-yy]%
</td>
<td align="right" valign='top'>

// fieldpine-if conditions can be used, these are evaluated for each line
<fieldpine-if test-notzero="%billrun.item[].debit%">
    %billrun.item[].debit/$[2s3]%
</fieldpine-if>
</td>
<td align="right" valign='top'>
<fieldpine-if test-notzero="%billrun.item[].credit%">
    %billrun.item[].credit/$[2s3]%
</fieldpine-if>
</td>
</tr>

// Finish the repeat block
</fieldpine-repeat>

// And close the whole table
</tbody></table>

The above, might produce output like

Date	Debit	Credit
13-Feb-2019	$55.10
15-Feb-2019	$120.60
26-Feb-2019		$100.00

Displaying Transactions - Accounting Style

Worked example. To display transactions in an accountant style layout we define the various parts in the template

Date	Activity	Reference	Invoice Amount	Payments	Balance NZD
	Opening Balance				$279.90
17-03-2022	Invoice # 16782003		$579.30		$579.30
18-03-2022	Payment # 50000597			$279.90	$279.90
31-03-2022	Invoice # 16782076		$199.00		$199.00

First we define static HTML to create the table and define the headers

<table border="1" style="border-collapse:collapse;width:100%;" cellpadding="2">
<thead>
<tr style="background-color:silver;font-weight:700">
<td>Date</td>
<td nowrap>Activity</td>
<td>Reference</td>
<td nowrap>Invoice Amount</td>
<td>Payments</td>
<td>Balance NZD</td>
</tr>
</thead>
<tbody>

Draw the first row of the table which contains the opening balance.

<tr>
<td></td>
<td>Opening Balance</td>
<td></td>
<td></td>
<td></td>
<td align="right">%billrun.openingbalance/$[2s3]%</td>
</tr>

Use Javascript to create a variable to hold the opening balance. We will be using this to display the "Balance" column.

<script>
    var curbal = Number("%billrun.openingbalance%")
</script>

Start a repeat loop for each item in the statement billing run. Everything from this line until the matching </fieldpine-if>

<fieldpine-repeat billrun>

Draw each statement line. The symbol %billrun.item[]. is used to select the current item as we repeat this loop

<tr style="border-bottom:0;border-top:0">
<td nowrap valign='top'>
%billrun.item[].completeddt/dt[dd-mm-yy]%
</td>

Display the words "Invoice" or "Payment" if the sale/invoice has payment amounts or not

<td valign='top'>
<fieldpine-if test-iszero="%billrun.item[].credit%">
Invoice #
</fieldpine-if>
<fieldpine-if test-nozern="%billrun.item[].credit%">
Payment #
</fieldpine-if>
%billrun.item[].ref%
</td>

Continue to display the other columns in the table. Note how a <fieldpine-if> is used to blank zeros.

<td valign='top'>   %billrun.item[].orderno%   </td>

<td align="right" valign='top'>
<fieldpine-if test-notzero="%billrun.item[].debit%">
%billrun.item[].debit/$[2s3]%
</fieldpine-if>
</td>

<td align="right" valign='top'>
<fieldpine-if test-notzero="%billrun.item[].credit%">
%billrun.item[].credit/$[2s3]%
</fieldpine-if>
</td>

<td align="right" valign='top'>

Use javascript to calculate and display the balance column on each line.

<script>
    curbal += Number("%billrun.item[].debit%0") - Number("%billrun.item[].credit%0");
    document.write((Math.round(curbal * 100) / 100).toFixed(2));
</script>

If the sale was paid instore and not actually charged to the account, add the words "Paid in Store" with the amount.

<fieldpine-if condition="0%data.setting.DebugBillingOutStandingMethod% eq 1">
<fieldpine-if condition="%billrun.item[].nonaccountnet% gt 0">
<br><small>(Paid in Store %billrun.item[].nonaccountnet/$[2s3]%)</small>
</fieldpine-if>
</fieldpine-if>

Close the table/row

</td>
</tr>
</fieldpine-repeat>
</tbody>
</table>

Hyperlinking to Source Invoices

If your server has been configured to allow public access to invoices (not covered here) then the statement can include a link on the statement that will open the users browser to view the original source invoices.

<a href="%billrun.item[].documenturl%">Invoice %billrun.item[].ref%</a>

The symbol %billrun.item[].documenturl% automatically converts to a HTTP/S url to the document.

For security reasons, the URL does not include the sale number directly. Internally all sales are allocated a longer random "id" (eg "YW3L547IXN5KPQCFRYU8YBHGWBE9JIDOVQ4JYABYDK") which is used on the URL.

Sales are also automatically allocated a short password. The password is not in the URL, and if you are enforcing passwords you will need your own method to provide the passwords to the user. One option is simply to put the password on the statement

<a href="%billrun.item[].documenturl%">Invoice %billrun.item[].ref%</a>  (Password: %billrun.item[].randompassword%)

Adding "Do Not Pay" to total due.

If an account is in credit, some customers will not always notice the negative sign and pay the credit amount anyway, further pushing them into credit.

BALANCE DUE $-76.29

The following HTML adds extra text if the closing balance of the statement is less than zero

<p style='font-size:1.2em'
<b>
BALANCE DUE %billrun.closingbalance/$[2s3]%
<fieldpine-if condition="%billrun.closingbalance% lt 0">
     IN CREDIT. DO NOT PAY
</fieldpine-if>
</b>
</p>

The result on the statement is now

BALANCE DUE $-76.29   IN CREDIT. DO NOT PAY

Displaying Percentage of Credit Limit Used

You may like to display a percentage of current balance vs credit limit. This isn't widely used, but is in some specific cases.
There is no symbol to give you this figure, but you can easily calculate it with a small piece of javascript on the page.

<p>
    Credit Limit: %billrun.account.credit_limit%
    <script>
        let due = Number("0%billrun.closingbalance%");
        let credit_limit = Number("0%billrun.account.credit_limit%");
        let pct = due * 100 / credit_limit;
        if ((credit_limit < 10000) && (due > 0)) {    // Do not display for larger credit limits
            document.write(" Credit Limit Used: " + pct.toFixed(0) + "%");
        }

    </script>
</p>

Results in output like

Credit Limit: 1500.00  Credit Limit Used 15%

Customising Statements - Word Document Templates

Word Templates are now deprecated and are maintenance only. No new development will be undertaken. Please convert to HTML templates outlined above.

End of month statements are generated using a template file that is managed using Microsoft Word. The POS reads the Word document and inserts content where required, and finally creates a PDF document which is sent to the customer.

• The template file is stored in the folder \fieldpine\pos\site_messaging\messageformat108.docx
• If you do not have this file, such as a new installation, copy the file \fieldpine\pos\system_messaging\messageformat108.docx to the folder \fieldpine\pos\site_messaging\messageformat108.docx
  Do not edit the file in the system_messaging folder as it will be overwritten with Pos updates.
• It is highly recommended to save a backup of this file before making any changes.
• You can preview your changes by saving the template file and then using "View PDF" on any previous statement generated for an account. The POS will re-render the statement using the current version of the template file.

When you look at the template file, you will see a number of items between percent signs, such as %billrun.account.name% These are called symbols and are where the POS inserts customer specific information. Other items such as text and images are copied verbatim to the output file.

• Not everything you can do in Word will be reproduced in the generated output file. Word has a large number of options and possibilities and not all of these are available in the Word to PDF translator.
• Images and pictures should use JPEG or PNG formats only. Other formats may not transfer to the generated file.

Statement Symbols

The symbol %billrun...% refers to the current billing run (aka statement run).

Symbol	Result
%billrun.openingbalance%	The amount owing at the beginnnig of this statement. This is the closing balance from the previous statement
%billrun.closingbalance%	The total amount owing at the end of this statement. Closing balance = opening balance + totaldebits - totalcredits
%billrun.outstandingbalance.NNN%
%billrun.totaldebits%	Total amount of debits included in this statement
%billrun.totalcredits%	Total amount of credits included in this statement
%billrun.item[].NNN%	Values relating to each statement line item, which is typically a sale. The value NNN can refer to any of the symbols available on a sale, which is numerous, commonly used ones are listed below
%billrun.account.NNN%	Values relating to the individual account this statement is for. The value NNN can refer to most of the account field and attributes, with commonly used ones listed below

Account Symbols

Symbol	Result
%billrun.account.accid%	The internal unique number assigned to this account
%billrun.account.name%
%billrun.account.address1%
%billrun.account.phone%
%billrun.account.email%
%billrun.account.accountsmemail%

Item Symbols. These are symbols that related to each individual sale or adjustment.

Symbol	Result
%billrun.item[].completeddt%	The date/time of the sale or adjustment
%billrun.item[].orderno%	Customers order number, if one was captured at sale time
%billrun.item[].saletotal%	The total of the sale. If the item is an adjustment it is the total of that adjustment

You can also gain access to each individual saleline if you wish to list individual items on the statement. The syntax appears slightly weird with %billrun.item[].item[].XXX%
Which means, for this billing run (billrun), select the current Sale on that statement (billrun.item[]) and then select each saleline within that sale (billrun.item[].item[]) and select field XXX from the saleline.

Symbol	Result
%billrun.item[].item[].totalprice%	The totalprice charged for the saleline. Tax inclusive
%billrun.item[].item[].product.description%	The current product description for the item purchased
%billrun.item[].item[].product.plucode%
%billrun.item[].item[].qty%

Tips for Statements

From our support team, here are some guidelines for statements

• Be VERY clear what the amount to pay is. Customers sometimes simply pay the last figure on the document without reading or understanding it. However, even if you put "total to be paid $nnn", some customer will still pay a different total. There is no single statement format that is totally foolproof and non confusing to customers.
• The overdue totals are popular with retailers, but can lead to confusion with your customers.