Exporting Custom Data from Sage 200 (V2)

This task will export data from Sage 200 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 200 database. They can be viewed using SQL Server Management Studio.

Note - The information below applies to version 2 of the task. Workflows created using Zynk 2.0.3 or below will contain version 1 of this task, please see Exporting Custom Data from Sage 200 instead.

Settings

Sage 200 Connection

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

Op Lock

Required
This setting stores the highest OpLock value from the records the task has exported. It will update automatically each time the task is ran, provided your query returns the OpLock column from the database.

The OpLock value can be used to export modified records only. The task will pass this value to SQL Server as a query parameter called 'oplock'. You can use this in your query to only export records where the OpLock is greater than the value of this parameter. For example, the query below will only return customer accounts that have been modified since the task last ran.

SELECT * FROM SLCustomerAccount WHERE SLCustomerAccount.OpLock > @oplock

Op Lock Aliases

Optional
If your query is joining to multiple tables, and you need to check for modified records across these tables, you can use this setting to specify the aliases used in the query for the OpLock columns from each table. These aliases will be taken into account when the Op Lock setting is automatically updated, and it will be set to the highest value across all the tables. For example when using the query below, you should add ContactOpLock to the list of aliases.

SELECT [SLCustomerAccount].[SLCustomerAccountID], [SLCustomerAccount].[OpLock], [SLCustomerContact].[ContactName], [SLCustomerContact].[OpLock] AS [ContactOpLock]
FROM [SLCustomerAccount]
INNER JOIN [SLCustomerContact] ON [SLCustomerContact].[SLCustomerAccountID] = [SLCustomerAccount].[SLCustomerAccountID]
WHERE [SLCustomerAccount].[OpLock] > @oplock OR [SLCustomerContact].[OpLock] > @oplock

Query

Required
Enter the SQL query to run against the Sage 200 database. E.g. `SELECT * FROM Using the Query Designer.

Query Timeout

Optional
Enter the length of time (in seconds) to allow the query to run for. If the query does not finish running before this time elapses, it will stop and an error will be reported back.

Truth Settings

Optional
These settings can be used to output the IDs of any records that were imported into Sage via Zynk, and an <Id> value was provided. The IDs are stored in the 'External ID' column of Zynk's truth table, and can be included in the export by specifying the following info:

  • Internal ID Column - The Sage database column name to read the internal ID value from. The column needs to be selected as part of the query. Some common examples of the column names to use for certain tables are listed below.

    • PLSupplierAccount - SupplierAccountNumber
    • POPOrderReturn - DocumentNo
    • SLCustomerAccount - CustomerAccountNumber
    • SOPOrderReturn - DocumentNo
    • StockItem - Code
    • In many other cases it is the table name followed by ID. For example, when exporting from the SOPDespatchReceipt table, it would be SOPDespatchReceiptID.

  • Record Type - The type of record to search for in the truth table. This can be seen on the Data tab. For example, for sales orders, the type is 'Zynk.Connect.Objects.SalesOrder'.

  • Collection - The collection to read the external ID value from. In most cases this can be left blank. For sales orders, this will need to be set to the type of order you are dealing with (either 'ProductInvoice', 'SopReturn', 'SopQuote' or 'SopProforma').

Output File

Required
The file to save the results of the query to. The data will be saved in XML format.

Export as Elements

Required
Set to true to export the data as XML elements, or false to export data as XML attributes.

Root

Required
The name to give the root element in the output XML file. Defaults to 'Rows'.

Row

Required
The name to give the XML element for each row returned by the SQL query. Defaults to 'Row'.

Zynk Settings

See Common Task Settings

Examples

Example query, which selects the customer account reference and contact name(s), for the customer account ABC001:

SELECT [SLCustomerAccount].[CustomerAccountNumber], [SLCustomerContact].[ContactName], CONVERT(bigint, [SLCustomerContact].[OpLock]) AS [OpLock]
FROM [SLCustomerAccount]
INNER JOIN [SLCustomerContact] ON [SLCustomerContact].[SLCustomerAccountID] = [SLCustomerAccount].[SLCustomerAccountID]
WHERE [CustomerAccountNumber] = 'ABC001'

Example output file when Export as Elements is set to false:

<?xml version="1.0" standalone="yes"?>
<Rows
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Row CustomerAccountNumber="ABC001" ContactName="John Smith" OpLock="186136"/>
  <Row CustomerAccountNumber="ABC001" ContactName="Joe Bloggs" OpLock="127188"/>
</Rows>

Example output file when Export as Elements is set to true:

<?xml version="1.0" standalone="yes"?>
<Rows>
  <Row>
    <CustomerAccountNumber>ABC001</CustomerAccountNumber>
    <ContactName>John Smith</ContactName>
    <OpLock>186136</OpLock>
  </Row>
  <Row>
    <CustomerAccountNumber>ABC001</CustomerAccountNumber>
    <ContactName>Joe Bloggs</ContactName>
    <OpLock>127188</OpLock>
  </Row>
</Rows>