Exporting Custom Data from Sage 50 UK
This task will export data from Sage 50 UK as specified in the Query, it will export to XML format. This is useful when there is no task which exports the data required.
The table and column names that can be used in the Query are determined by the Sage 50 UK ODBC Driver. We would advise that you connect to Sage 50 UK via Excel (or similar utility) to list the table and column names available on your version, or for reference view article Sage 50 UK Database Documentation.
There is a section within the Sage Accounts Help file which details how to connect up to the Sage 50 UK ODBC via Excel, simply open the Sage help, click 'Index' and search for 'ODBC'. You can view an extract of the document in Sage 50 UK Troubleshooting
Settings
Sage 50 Connection
Required
The connection to Sage 50 UK to use. See the Connecting to Sage 50 UK article if you require more information on how to create/manage connections.
Date Modified
Dependant
The rolling date to use when exporting modified records. Will update automatically when the task runs.
Date Modified Tables
Optional
The list of tables to check for modified records in. Use when the query is joining to multiple tables, and you need to check the modified date in one or more of the tables you are joining to.
Export As Elements
Required
Set to true to export the data as XML elements, or false to export as XML attributes. See the samples below to see the difference between the two formats.
Export Modified, New or All Records
Required
Specify whether to export new, modified or all records from Sage. Note that the 'Modified' setting also includes new records.
This setting will only take effect when the query is specified using the 'Select', 'From', 'Where', 'Group By' and 'Order By' settings. When using the 'Custom Query' setting, the task will always return all data.
Output File
Required
The name of the file to export to.
Root
Required
The name of the XML root element, defaults to Rows
Row
Required
The name of XML rows, defaults to Row
Custom Query
Dependant
The SQL query to run against Sage 50. This setting takes precedence over the individual 'Select', 'From', 'Where', 'Group By' and 'Order By' settings. E.g. SELECT * FROM STOCK
From
Dependant
The from statement of your SQL query. The FROM keyword should not be included. Not required when specifying the query using the Custom Query setting. E.g. SALES_LEDGER
Group By
Dependant
The group by statement of your SQL query. The GROUP BY keyword should not be included. Not required when specifying the query using the Custom Query setting. E.g. SALES_LEDGER.ACCOUNT_REF
Order By
Dependant
The order by statement of your SQL query. The ORDER BY keyword should not be included. Not required when specifying the query using the Custom Query setting. E.g. SALES_LEDGER.ACCOUNT_REF
Select
Dependant
The select statement of your SQL query. The SELECT keyword should not be included. Not required when specifying the query using the Custom Query setting. E.g. SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.NAME, SALES_LEDGER.BALANCE
Where
Dependant
The where statement of your SQL query. The WHERE keyword should not be included. Not required when specifying the query using the Custom Query setting. E.g. SALES_LEDGER.ACCOUNT_REF = 'ABS001'
Zynk Settings
Examples
Example query, which selects the name and balance fields from the sales ledger, for the account ABS001:
Example output file, when the 'Export As Elements' setting is false:
<?xml version="1.0" standalone="yes"?>
<Rows>
<Row ACCOUNT_REF="ABS001" NAME="ABS Garages Ltd" BALANCE="2533.31" />
</Rows>
Example output file, when the 'Export As Elements' setting is true: