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.
PostTrans XML API for Sage 50 Export Data
||Selecting the Company/Companies
||Explanation of Transaction Data
||Selecting Tables to Export
||Selecting Columns/Fields to Export
||More about Transaction Export
||How Transaction Lines related to Header
||Transaction Export Time Frame
||Output to Folder/SFTP server
||Performing a manual export
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
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.
When finished, a flag file is created in the export folder called ”finished.txt”
The Export menu gives access to the export settings. From within here you can:
- Select one or many Sage 50 Companies to export
- Select which tables to export
- Apply simple criteria to Customer/Supplier export
- Selecting column to export for the larger export tables
- Set the amount of transaction data to export. (X days)
- Setup SFTP upload
- Run times at certain hours
- Alter the export date format
- Set export folder
Pressing [Select Columns] button would give: