Sage 200 Custom Export

This article will show you how to configure your own custom export from Sage 200. In this example, we demonstrate the process of filtering the results of an export and exporting additional fields which are not included in the standard export. We will use the Export Sales Orders task as an example in this tutorial, but the same principles apply across all the Sage 200 export tasks.

Filtering the Export

As an example, we will filter the results of the Export Sales Orders task to only include orders where the total amount is greater than £100. Firstly, we need to check the field name in the Sage database. You can do this using SQL Server Management Studio.

  • Navigate to your Sage 200 database in the Object Explorer, and expand the 'Tables' folder.
  • Select the table where the data is stored, in the case of sales orders it is 'SOPOrderReturn', and expand the 'Columns' folder.
  • A list of the field names will be displayed. We can see that the total is stored in the 'TotalGrossValue' column.

SQL Object Explorer

  • In the Export Sales Orders task settings in Zynk, expand the 'Query Settings', and click the 'Where Clauses' setting.

Export Sales Order Settings

  • Set the Comparison to 'GreaterThan', the Field Name to 'SOPOrderReturn.TotalGrossValue' and the Value to '100'.

Where Collection

  • Now when you run the workflow, the task will only export sales orders where the total amount is greater than 100.

Including Additional Fields in the Export

As an example, we will include the Total Net, Total Tax and Total Gross fields in the orders returned by the Export Sales Orders task. Firstly, we need to check the field names in the Sage database. You can do this using SQL Server Management Studio.

  • Navigate to your Sage 200 database in the Object Explorer, and expand the 'Tables' folder.
  • Select the table where the data is stored, in the case of sales orders it is 'SOPOrderReturn', and expand the 'Columns' folder.
  • A list of the field names will be displayed. We can see that the data is stored in the 'TotalNetValue', 'TotalTaxValue', and 'TotalGrossValue' column.

SQL Object Explorer

  • In the Export Sales Orders task settings in Zynk, expand the 'Query Settings', and click the 'Columns' setting.

Export Sales Order Settings

  • Enter the column names into the list.

String Collection

  • Now when you run the workflow, the task will include these additional fields in the CustomFields collection in the output file.

Custom Fields