Transaction Export Overview

The XML API exports various Sage 50 tables to give all the information an external system would need.   VAT,GL Records, etc and then Customer/Supplier records.  It builds a list of exported which match any criteria given (CuNominal=4000, etc).   When exporting the transactions, towards the end of the export, it only exports those relating to the Customer/Supplier records export earlier, which reduces the exported data size.  

 

Download Example Data Extract

Demonstration Video

Audit Trail

At the heart of Sage 50 is the Audit Trail known as “Transaction” in the user interface.   This information represents the true financial information posted to the accounts.  The table dates back to 1986 with extensions made over the years.  These transactions are exported, but its important to understand how, where and when these transactions are created.

The audit Trail list all outstanding transactions, i.e not paid

Audit Trail Types

These are the types:

• SI,Sales Invoice
• SC,Sales Credit
• SR,Sales Reciept
• SA,Payment on Account
• PI,Purchase Inv
• PR,Purchase Receipt
• PC,Purchase Credit

List of Columns exported from Audit Trail

Sales/Purchase Orders/Invoices

If Sales (SO) or PO Purchase Orders (PO) are raised in the system, then they reside in their respective modules within Sage 50, until received or delivered.   Once Sales Order is delivered, then the order can be processed into a Sales Invoice, held in the invoice module.   When finally Invoiced, printed and sent out to the customer then the invoice can be processed to update the Audit Trail, but with no stock information, just description of each line on the original invoice.  Type “SI” in the audit trail.

In the case of the Purchase Orders, they are processed straight to the Audit Trail.  Type “PI” in the audit trail.

Using PostTrans XML API we can export the Audit trial and its lines, the Sales Invoice from the Sale invoice module, and the Sales/Purchase Orders from their respective modules.

The Audit Trail is only updated when accounts run the update process.  Therefore, items in the invoice module do not appear in the Audit Trail until processed.

List of Columns Exported from Invoice and Purchase Ledgers

The following diagram show the exported data files from the different Sage 50 Ledgers/Data files and when/how the transactions move through the system.

Export last X days

Initially, you may want to export the last three years of transaction data to an external system, and then the last 15-30 days of data, every or most days.

The export will also include anything outstanding if selected in settings.  This works differently in various areas:

• Audit Trail – Invoice Not Paid
• Sales Invoice Module – Not Posted to Audit Trail 
• Sales Orders Module – Not posted to Invoice Module and not Cancelled
• Purchase Orders Module – Not posted to Audit Trail and not Cancelled

Each table has a field called “transOutstanding” = 1 if outstanding as defined above.

Field Names non-transactional Data

The field names used are those used by PostTrans for import and used by PostTrans for Excel

Field Names Transactional Data

The Sage 50 field names are used ALL IN UPPER CASE, with a few extra fields added in camel case.  These extras simplify development.

For instance, the Audit Trial Export has, in the header TransHist.txt, HeaderUniqueID which is a unique key (actually the first split, line number, number in the transaction shown in Sage 50).   This ID then relates to all the HeaderUniqueID in the TransHistLines.txt table.  The TransHistLines.txt table then has UniqueID and HeaderUniqueID available for export.

Product Stock levels

Two export schedules can be set.  One to export all data, and Transactions for that last xdays + anything outstanding, and another to export Product Stock level file.   This an hourly stock level can be exported.

Finished

When finished, a flag file is created in the export folder called ”finished.txt”

Export Settings

• Select one or many Sage 50 Companies to export

Pressing [Select Columns] button would give: