The advanced tab isn’t so hard really.

As with all things, when we break it down it’s all very obvious. The options here do all the heavy lifting and in most cases we provide assistance. This tab controls the most powerful functions that allow for complex export for example of new records added since the last export.

odbc-advanced-exporting-options

 

Generic options

The output date format is used whenever a field with the data type is exported. It will write the date to your CSV or text file in this format.

Post Process Options

If you want to call a program after the export for example our unattended FTP file transfer tool then this is where you define the EXE name and any switches required.

Use the [DestFileName] keyword to pass the exported data file name.

Header Style Options

Sometimes you want to export a header line followed by one or more detail lines. Optionally you want the program to chop each new record into a separate file. This can be accommodated but is worthy of it’s own posting.

Export Variables

The ODBC export tool support the use of variables, these take a bit of explaining but it’s worth the read. In some scenarios it is much tidier to replace static values with variables. A great example would be where you want to reuse a SQL statement many times substituting one or more values in each case. You might be exporting 5 different stock categories on a regular basis. You might have a SQL statement such as

SELECT * from Stock WHERE Category = ‘WHEELS’

Then you would copy and save the statement replacing the category “WHEELS” with “HUBS” and so on. It is MUCH tidier and easier to maintain if we have a statement

SELECT * from Stock WHERE Category = [WhichCategory]

Now in this example I will admit that it is such a simple statement that it isn’t worth the effort but in a very complex statement it is nice to reuse the statements without risking breaking the statement when you add a new category. When the ODBC Export runs it will load the SQL Statement and then replace the keyword [WhichCategory] with a variable from the ODBC definition if one exists. Don’t worry about where you want to use the SQL Syntax of using square brackets around field names that are not valid like if they contain a space or are a SQL Keyword. The ODBC Export tool only replaces Variables defined in the variables table so just ensure that any variables you add are unique.

Updating variables

This is a very clever feature of the ODBC Export tool and was one of the primary reasons it was originally developed. Imagine the following scenario,

  • You want to export purchases to another company.
  • You only want to send newly added Purchases orders

The only way to achieve this is to know the last Purchase order already exported. So in the ODBC Export tool you can store the value in a column back into a variable, just check the “SaveVariableValue” check box and specify the column that you want to store. Also we need to decide when variables will be updated. This is a column that is specified in the field above the variables grid labeled Update vars on change of column. Now in your SQL Statement you can get the variable value to use in your where clause for example

SELECT * from Purchases WHERE PONumber > [LastPONumber]

Then in the variables settings we would have

  • Variable Name = LastPONumber
  • Variable Type = Value
  • VariableValue = 0 (to start it is zero but each time it processes a record it will update this number)
  • VariableColumnName=PONumber
  • SaveVariableValue=True

Note: = Variables are case sensitive

Scenario Example

  • Monday export where Last PO is greater than 0 it exports 14-10
  • Tuesday export where Last PO is greater than 10 it exports 11-15
  • Wednesday export where Last PO is greater than 15 it exports 15-18

Want to try it out first?

 

Just send us an email for a trial version.

Like what you see?

Contact us to buy a copy for just $995

This price includes all of the b2b suite modules, CSV Conversion, ODBC Exporting, Scheduling, Scripting and Unattended FTP. Talk to us today and let us show you how you could use your web presence more effectively and improve the value of this vital business resource.

Prices are in NZ dollars and exclude GST.