How to update Sage 50 customer account statements to show Breww's invoice numbers

Help docs & tutorials Integrations
,
1

If you are Using the Sage 50 integration with Breww, you may notice that the invoice numbers in Sage 50 don’t match those in Breww. Sadly due to limitations in how Sage 50 works, it’s not possible for Breww to apply its own invoice number in Sage 50, so Sage 50 will generate its own numbers (note this limitation doesn’t apply to most other account integrations, including Sage Accounting - their modern cloud-based product).

But, fear not! Breww is putting its own invoice numbers into a field within the invoice in Sage, called CUST_ORDER_NUMBER and so you can modify your customer account statement template to show this field instead of Sage’s own invoice number.

This guide shows you how to do this, and the same concept should apply to other reports/documents in Sage.

What you need to do

We have a video guide below which shows you the steps involved, but the premise is as follows:

  • You need to take your existing statement report template and modify which data field is shown in the invoice reference column.
  • The field you need to show is named CUST_ORDER_NUMBER and exists in the INVOICE dataset.
  • The INVOICE dataset can be attached (known as “joined”) to the existing datasets available in the account statement report.
  • By going to Report → Joins, you can add the INVOICE dataset and create a relation from the AUDIT_HEADER dataset to the INVOICE dataset, by linking INV_REF_NUMERIC from AUDIT_HEADER to INVOICE_NUMBER in the INVOICE dataset. This tells Sage how to find the right invoice.
  • All fields in the INVOICE dataset can now be used in your report. The one you need is called CUST_ORDER_NUMBER.
  • This can then be saved as a custom report layout. When printing statements, make sure to use your custom layout, not the original.

Dataset relation diagram

erDiagram AUDIT_HEADER ||--o{ INVOICE : "Join INV_REF_NUMERIC to INVOICE_NUMBER" AUDIT_HEADER { int INV_REF_NUMERIC } INVOICE { int INVOICE_NUMBER string CUST_ORDER_NUMBER }

Video tutorial

This explains how to show the Breww invoice number on a Sage 50 customer statement, but we appreciate this is a complex topic. We’re happy to try to help if you have any trouble completing this and would like to reply to this thread below.

If you get completely stuck, we’d recommend speaking to Sage directly as they will be able to help you set this up in your own instance of Sage 50. You can link them to this guide if you need to.

Here are a couple of official help guides from Sage that are related and may be useful:

What about the invoice list (and other lists) in Sage 50

In most places in Sage 50, you can right-click in the header bar that shows a list of invoices and choose which columns are shown. If you add “Customer Order No” this will show the Breww invoice number.