Menu

Export Data (Pro)

Export Sage 50 Data (Pro Version)

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.  

 

Demonstration Video

 
 
   PostTrans XML API for Sage 50 Export Data
  0:00 Export Settings
  0:40 Selecting the Company/Companies
  1:00 Export Time
  2:80 Explanation of Transaction Data
  4:00 Selecting Tables to Export
  5:20 Selecting Columns/Fields to Export
  6:20 More about Transaction Export
  7:00 How Transaction Lines related to Header
  7:40 Transaction Export Time Frame
  8:10 Output to Folder/SFTP server
  8:40 Performing a manual export
 
 

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

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:

Sage 50 Exported Tables

Title

Description

Currency

Export Currency

TAX

Export TAX codes

Country

Export Counties

Nominal

Export Nominal Accounts

Department

Export Departments

Customers

Export Customers with criteria Pro Filter
Filter by Active, CuAnal1,CuAnal2,CuAnal3 or CuNomCode but field name can be typed in (CASE SENSITIVE)

Supplier

Export Supplier with criteria Pro Filter
Filter by Active, SuAnal1,SuAnal2,SuAnal3 or SuNomCode but field name can be typed in (CASE SENSITIVE)

Price Lists

Export Customers/Supplier Price List relating to exported Customers/Supplier Pro

Cust/Supp Prices

Export Customers/Supplier Prices relating to exported Customers/Supplier Pro

Product Level *

Export Product Level Every x mins Pro Filter
Filter by Active, PrCust1,PrCust2,PrCust3,PrSalesNom,PrDept or PrCategory but field name can be typed in (CASE SENSITIVE)

Product

Export Product with criteria Pro Filter
Filter by Active, PrCust1,PrCust2,PrCust3,PrSalesNom,PrDept or PrCategory but field name can be typed in (CASE SENSITIVE)

Project

Export Project with criteria Pro
Filter by Active, PjAnalysis1,PjAnalysis2 or PjAnalysis3

Audit Trial History

Audit Trial History Pro In last X days
The Audit trail contains all the posted financial transactions, but has no Product codes and thus no Product info
Only Transactions relating to exported Customer/Suppliers are exported, posted since the last x days
HeaderUniqueID is the unique key (First Split no in Sage 50, shown in Transaction list)
Columns Exported

Audit Trial History Splits (lines)

Audit Trial History Lines Pro
Lines associated with the Audit Trial Header records
HeaderUniqueID forms the relationship between the two tables
Unique ID can be used a key for each line record which does not changed (Split no in Sage 50, shown in Transaction list)

Sales Invoice Module

Sales Invoice Module Pro In last X days

Columns Exported

Sales Invoice Module Lines

Sales Invoice Module Lines Pro

Sales Orders Module

Sales Orders Module Pro In last X days

Columns Exported

Sales Orders Module Lines

Sales Orders Module Lines Pro

Purchase Orders Module

Purchase Orders Module Pro In last X days

Columns Exported

Purchase Orders Module Lines

Purchase Orders Module Lines Pro