Skip to content

Exporting Custom Data from Sage 50 CA

This task will export information from Sage via an SQL query, allowing you to obtain any data you require from Sage.

Settings

Sage 50 CA Connection

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

Date Modified

Required
Used in conjunction with the @dtASDate and @tmASTime parameters to enable the export of new/modified records only. The value will be split into separate date and time components, and made available via these query parameters.

Date Time Alias Pairs

Optional
If you are making use of the @dtASDate and @tmASTime parameters in your query, and you have applied aliases to these columns, you will need to enter the corresponding pairs of aliases here. This will ensure Zynk can update the Date Modified setting correctly each time the task runs.

  • Date Alias - This is where you specify an alias that was applied to the dtASDate column.
  • Time Alias - This is where you specify the alias that was applied to the tmASTime column, within the same table as the above.

Export As Elements

Required
Set to true to if you would like the value of each column to be output as an XML element rather than an XML attribute.

Query

Required
The SQL select query to run against the Sage database. If you want to ensure that only new/modified records since the task last ran are returned, you can use the parameters @dtASDate and @tmASTime in the where clause. An example of how to do this is shown below.

SELECT *
FROM tCustomr
WHERE (tCustomr.dtASDate = @dtASDate AND tCustomr.tmASTime > @tmASTime) OR tCustomr.dtASDate > @dtASDate

Please note that your must select the dtASDate and tmASTime columns as part of your query if you want to export new/modified records. This is to ensure that Zynk can update the value of the 'Date Modified' setting correctly.

If you are joining to other tables and want to consider modifications to that data too, you will need to select the dtASDate and tmASTime columns from the joined tables too. An example is shown below.

SELECT tInvent.sPartCode,
  tInvent.sName,
  tInvent.dtASDate,
  tInvent.tmASTime,
  tInvByLn.dInStock,
  tInvByLn.dtASDate AS qtyDtASDate,
  tInvByLn.tmASTime AS qtyTmASTime
FROM tInvent
  LEFT JOIN tInvByLn ON tInvent.lId = tInvByLn.lInventId
WHERE (tInvent.dtASDate = @dtASDate AND tInvent.tmASTime > @tmASTime) OR tInvent.dtASDate > @dtASDate OR
  (tInvByLn.dtASDate = @dtASDate AND tInvByLn.tmASTime > @tmASTime) OR tInvByLn.dtASDate > @dtASDate

Please note that the extra dtASDate and tmASTime columns will need to be given aliases to ensure they have distinct names, and these alias names will need to be specified via the 'Date Time Alias Pairs' setting. In the case of the example above, you would need to create a single entry in the 'Date Time Alias Pairs' list, with qtyDtASDate as the date alias, and qtyTmASTime as the time alias.

Root

Required
The name to use for the root element in the XML output file. Defaults to 'Records'.

Row

Required
The name to use for each row element in the XML output file. Defaults to 'Record'.

Output File

Required
The name of the file to output the exported records to. The data must will be in XML format, a sample of which is shown below.

Zynk Settings

See Common Task Settings

Examples

Sample output file, produced by the query SELECT * FROM tCustomr:

<?xml version="1.0" encoding="utf-8"?>
<Records>
  <Record>
    <lId>33</lId>
    <sName>Ashburton Reinforcing</sName>
    <dtASDate>2016-08-10T00:00:00</dtASDate>
    <tmASTime>1899-12-30T08:43:12</tmASTime>
    <sASUserId>sysadmin</sASUserId>
    <sASOrgId>winsim</sASOrgId>
    <sCntcName>Steve Ashburton</sCntcName>
    <sStreet1>12 Cultus Street</sStreet1>
    <sStreet2 />
    <sCity>Richmond</sCity>
    <sProvState>British Columbia</sProvState>
    <sCountry />
    <sPostalZip>V3M7Q3</sPostalZip>
    <sPhone1>(604) 555-8471</sPhone1>
    <sPhone2 />
    <sFax>(604) 555-9132</sFax>
    <dCrLimit>40000</dCrLimit>
    <dAmtYtd>38085.6</dAmtYtd>
    <dLastYrAmt>15675</dLastYrAmt>
    <fDiscPay>2</fDiscPay>
    <nDiscDay>30</nDiscDay>
    <nNetDay>60</nNetDay>
    <bStatement>1</bStatement>
    <bContOnChq>0</bContOnChq>
    <bEmailForm>1</bEmailForm>
    <bEmailCnfm>0</bEmailCnfm>
    <bUseSimply>0</bUseSimply>
    <bUseMyItem>0</bUseMyItem>
    <dAmtYtdHm>0</dAmtYtdHm>
    <dAmtLYHm>0</dAmtLYHm>
    <dCrLimitHm>-1</dCrLimitHm>
    <bMemInToDo>0</bMemInToDo>
    <bUsed>1</bUsed>
    <lCurrncyId>1</lCurrncyId>
    <sEmail>[email protected]</sEmail>
    <sWebSite />
    <bUseMailAd>0</bUseMailAd>
    <bInactive>0</bInactive>
    <lTaxCode>11</lTaxCode>
    <bIntCust>0</bIntCust>
    <lDfltDptId>3</lDfltDptId>
    <lAcDefRev>40200000</lAcDefRev>
    <lDpDefRev>0</lDpDefRev>
    <lCompId>1</lCompId>
    <nLangPref>0</nLangPref>
    <lPrcListId>1</lPrcListId>
    <dtSince />
    <dtLastSal>2025-03-31T00:00:00</dtLastSal>
    <lInvLocId>0</lInvLocId>
    <dStdDisc>0</dStdDisc>
    <lSalManID>1</lSalManID>
    <bDirectPay>0</bDirectPay>
    <bCanSaveCC>0</bCanSaveCC>
    <bEFTMyRef>0</bEFTMyRef>
    <bEFTMyCode>0</bEFTMyCode>
    <sEFTRef />
    <sEFTCode />
    <nDefInvPay>0</nDefInvPay>
    <lDefInvCC>0</lDefInvCC>
    <nDefPay>0</nDefPay>
    <lDefCC>0</lDefCC>
    <bUseDefInv>1</bUseDefInv>
    <bUseDefPay>1</bUseDefPay>
  </Record>
</Records>