Exporting Custom Data from Sage 50 US

This task will export data from Sage 50 US in XML format, as specified by an SQL query. 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 US database. You can find a list of the table names here. For the column names, we recommend using our built in query designer, which can be accessed via the Query setting on this task.

Settings

Sage Connection

Required
The connection to Sage 50 US to use. See the Connecting to Sage 50 US article if you require more information on how to create/manage connections.

Date Filter

Required

Used when exporting new/modified records. The value can be accessed in your query using ODBC parameter syntax - ?.

The value will update automatically each time the task runs, to the greatest date among the columns listed in the 'Date Filter Columns' setting.

Date Filter Columns

Optional

The columns to use when determining the new value for the 'Date Filter' setting. The greatest date among these columns will be taken as the new value for the 'Date Filter' setting.

Export As Elements

Required

This setting determines how columns are written to the output XML file. Set to True to export them as XML elements, e.g. <Column1>Value 1</Column1>. Set to False to export them as XML attributes, e.g. Column1="Value 1".

Query

Required

The custom query to run against Sage. You can find a list of the table names that can be queried here.

You can use ODBC parameter syntax to pass the value of the 'Date Filter' setting into the where clause of your query, which is useful when you only want to export new or modified records from Sage. An example of how to do this is shown below.

SELECT LineItem.ItemID,
  LineItem.ItemDescription,
  LineItem.LastSavedAt
FROM LineItem
WHERE LineItem.LastSavedAt > ?

Timeout

Optional

The maximum length of time (in seconds) to allow the query to run.

XML Root Name

Required

The name to use for the root element in the output file. Defaults to Rows.

XML Row Name

Required

The name to use for the row elements in the output file. Defaults to Row.

Output File

Required
The name of the file to export to. Data is exported in XML format. Defaults to sage_50_us_export_queery.xml.

Zynk Settings

See Common Task Settings.

Example

A sample output file is shown below.

<?xml version="1.0" encoding="utf-8"?>
<Rows>
  <Row ItemID="HD-TX-4KZ-101" LastSavedAt="2024-03-07T15:49:52" ItemDescription="DM Lite Transmitter for HDMI S" />
  <Row ItemID="DL-HD12-H2" LastSavedAt="2024-05-23T11:31:37" ItemDescription="DIGITALINX HDMI DA 1:2 4K 18G" />
  <Row ItemID="70-1045-08" LastSavedAt="2024-05-23T11:31:36" ItemDescription="Cable Cubby 500, Black, No AC" />
  <Row ItemID="CG29686" LastSavedAt="2024-03-07T15:49:53" ItemDescription="50FT HDMI CABLE WITH GRIPPING " />
</Rows>