TMW Smartlink Data Exchange Agent (TMWDX)

TMW SmartLink Data Exchange Agent (TMWDX)

User Guide

Version 1.6

TMW Proprietary Information

TMW SmartLink Data Exchange Agent / Version: 1.6
User Guide / Date: 1/19/2019

Revision History

Date / Version / Description / Author
3/17/2005 / 1.0 / Initial Document / jday
3/28/2005 / 1.3 / Revised to reflect usability modifications in the TMWDX application. / jday
8/5/2005 / 1.6 / Revised for additions to the TMWDX command set. Add updated images. / jday

Table of Contents

TMW SmartLink Data Exchange Agent (TMWDX)

User Guide

Version 1.6

TMW Proprietary Information

Revision History

Introduction

Application Installation

Application Overview

Setup

Source Tab

Work Tabs Associated with Stored Procedures

Import Types

Field Names

Lookups

Log Messages

Email Messages

Settings

Scripting

Debugging

Operation

Logging

Appendix A – Custom Date-Time Formats

Appendix B - Expressions

EXPRESSION SYNTAX

USER-DEFINED VALUES

STRING OPERATORS

WILDCARD CHARACTERS

FUNCTIONS

CONVERT

LEN

ISNULL

IIF

TRIM

SUBSTRING

Appendix C – DX Scripting Command Tips and Tricks

REGEX

Link

PARSE

SENDFILE

Introduction

This document describes the setup and operation of the TMW SmartLink Data Exchange Agent (TMWDX). TMWDX is a Microsoft VB .NET based application used to exchange data with external systems and the TMWSuite database.

It uses a script to translate data in delimited or fixed position flat files, or in Microsoft Excel spreadsheets. A typical use is to import orders for transportation services into TMWSuite.

The TMWDX agent is not dependent on the TMWSuite database. It operates with MS SQL 2000 or greater database stored procedures.

TMWDX can replace the TMW Microsoft Excel-based Order Import Toolkit, with the appropriate script.

Application Installation

NOTE: TMWDX requires that the .NET framework 1.1 be installed on the computer on which it will run.

As well, the TMWDX.sql file must be applied through MS Query Analyzer to the TMWSuite database. The TMWDX.sql will be found in the DX folder in the distribution.

Application installation itself is accomplished with a standard Microsoft Windows installation script, Setup1.msi in the DX folder. Double-clicking on this file starts the installation.

When the title page displays, hit “Next.” Clicking “Cancel” will cancel the installation.

If the installation options displayed are satisfactory, click “Next”. Clicking “Cancel” will cancel the installation. Clicking “Back” will go back to the previous step.

Click “Next” to proceed with the installation on the next displayed page. Clicking “Cancel” will cancel the installation. Clicking “Back” will go back to the previous step.

Installation progress is then displayed. Installation takes only a few seconds. If you wish to cancel, click the “Cancel” button on the page.

When the installation is complete, a completion screen will be displayed. Click “Close”.

When the TMW Data Exchange application is run for the first time, the connection screen will be displayed. Once a good connection to the TMWSuite database is confirmed, the application will automatically start.

Application Overview

The figure below shows the general flow for order processing in TMWDX.

TMWDX is a script-driven “robot” that executes stored procedures:

1. Data from an external source is placed in the “Source” tab.
2. The script in the “Setup” tab processes the “Source” data one row at a time.
3. The script moves data to the various tabs that act as holding tanks.
4. At the appropriate point in the script, an “EXEC tabName” command is issued.
5. The data in the current row of the tab referenced in the “EXEC” are used as the parameters for the stored procedure associated with the tab.
6. Successive execution of the various tabs put the data on the database.
   

TMWDX is similar to an Excel workbook. It has tab pages with grids like spreadsheets to hold the script, settings, incoming data, and outgoing data. Under the “Edit” menu item, clicking on “Edit/Edit Setup” shows the script and other settings.

Scripts are typically loaded from base scripts for various import types into SQL tables, and modified to suit the particular application. The following figure shows a sample script.

If “Edit Setup” is not checked, the setup is hidden, as in normal operation, shown in the next figure:

For the most typical imports, after a standard script is installed and modified for any custom needs, standard data exchange stored procedures are associated with the tabs to hold imported data.

After setup, the import can run unattended. There is both an error log table, and an email messaging system included in the import to report on errors and other significant events as they occur.

Setup

The first tab shows as “Status” when not in setup mode. If “Edit Setup” is checked in the Edit menu, the tab shows as “Script”, displaying current processing script. When other items are selected from the “File” menu item, the records associated with that item are displayed in the tab, and the tab name changes accordingly. Setup items from the File menu include:

• Import types,
• Field names to clearly identify the data fields being imported,
• Scripts to run processing and translation,
• Lookups for string translation,
• Log message templates, including those for error messages.
• SMTP Mail message templates.
• Settings, and
• Cross references (xref) (work in process).

Whenever data is changed in the grid in the setup tab, the Update Table button must be clicked to save the settings. This button changes color to red when an update is needed. During the update, the button is yellow. The button is shown circled in red in the figure below:

Source Tab

The “Source” tab contains a data grid displaying the current data to import. The script works against this data.

Work Tabs Associated with Stored Procedures

These tabs are the temporary destination for the data. Each data field copied into a cell in the data grid in a given tab represents a parameter for stored procedure associated with at tab.

When the script invokes an EXEC with a particular tab name, it executes the SQL stored procedure using the parameters contained in current row in that tab. There is any EXEC …ALL command that will execute all the rows.

In the figure below, the drop down box is used to select the stored procedure to be tied to that tab. When a procedure is selected, the tab automatically refreshes with the stored procedure’s parameters are column headers. The application automatically remembers the selection. It is not necessary to click the “Update table” button.

Any of the Work tabs can be renamed through the Edit menu, by selecting the Edit tab name menu item. The tab name is then used as a reference to that destination in the script. These tab names are remembered until changed, and are stored in the Settings table.

Any of the Work tabs can be renamed through the Edit menu, by selecting the Edit tab name menu item. The tab name is then used as a reference to that destination in the script. These tab names are remembered until changed, and are stored in the Settings table.

Type in the new name and click the “GO” button, and the name changes:

Import Types

Import types tie all the parts for each type of import together. Specifying at least one import type is mandatory.

Set up Import Types by selecting the “Edit/Edit Setup” menu item, then selecting the “File/Import Types” item.

When more than one import type has been set up, each can be selected through the drop down box circled in red in the figure below.

Creating an import type consists of selecting “Edit/Edit Setup,” then “File/Import Types.”

A screen like that below is displayed.

You must fill in the parameters for the new import as in the table below.

dx_Import_Types / Used to store various import configurations
dx_importid / varchar(8) / Import ID that ties to other items, uniquely identifying an import
dx_importname / varchar(255) / name of import ("EDI 204,” "gLOG IMPORT,” "MANUGISTICS", etc.)
dx_importtype / varchar(8) / FLAT
dx_recordtype / varchar(8) / ORDER
dx_location / varchar(255) / origin location of data (ODBC connection string, file path, etc.)
dx_query / varchar(255) / origin location subset of data (file extension, table name, sheet name, etc.)
dx_headersincluded / char(1) / Y or N to indicate if data starts with a row of field names
dx_archive / bit / Check to enable archiving of parsed records to dx_Archive database table
dx_archivedir / varchar(255) / Directory path to archive directory
dx_filewatch_enabled / bit / Check to enable filewatch on a folder—as opposed to polling (work in progress
dx_polling_enabled / bit / Check to enable polling-recommended method
dx_polling_minutes / float / Number of polling minutes. Decimal fractions are allowed
dx_timecheck_seconds / bigint / How often to check the time for polling.
dx_import_file_mask / varchar(50) / Mask used for input files
dx_delimiters / varchar(30) / Delimiters for delimited flat files
dx_faileddir / varchar(255) / Directory path to failed directory
dx_auto_purge / bit / Check for auto-purge of history

Field Names

The Fields setup is used to name and define the columns specifying the format of the data coming in. This data will be placed in the Source tab, and the columns there will be labeled with the column names specified.

Set up Fields by selecting the “Edit/Edit Setup” menu item, then selecting the “File/Field Names” item.

dx_FieldDefinitions / used to translate and name fields in files
dx_importid / varchar(8) / Import ID that ties to other items, uniquely identifying an import
dx_recordtype_name / varchar(8) / unique ID to identify translation ("204_01", "204_02", "HEADER", "STOP REC", "FREIGHT", etc.)
dx_fielddef_start / int / field number (or starting character position for flat files)
dx_fielddef_length / int / length of characters to parse (only used for flat files)
dx_fielddef_type / varchar(20) / SQL type of field ("int", "datetime", "char(8)", "money", etc.)
dx_fielddef_name / varchar(40) / User-defined name of field ("Shipper Name", "Weight", "Pickup Date", etc.)**

Lookups

Lookups substitute one data value for another.

Set up Lookups by selecting the “Edit/Edit Setup” menu item, then selecting the “File/Lookups” item.

dx_Lookup / used to lookup a value and return a different user-definable value
dx_importid / varchar(8) / Import ID that ties to other items, uniquely identifying an import
dx_lookuptable / varchar(40) / name of common list of values (called from dx_Transforms)
dx_lookuprawdatavalue / varchar(255) / value contained in raw data
dx_lookuptranslatedvalue / varchar(255) / value to return

Log Messages

The templates in the Log Messages setup are used to translate return codes, successful or failed, into messages. The dx_errormsg field contains a message string with embedded column names enclosed by brackets (“{}”) to specify where values from the tab name in the dx_command field should be inserted in the message.

dx_Message / used to translate return codes into messages*****
dx_command / varchar(40)
dx_returncode / Int
dx_errormsg / Text
*****Messages aren't necessary error messages, but success messages as well.

Email Messages

The dx_messagebody field contains a message string with embedded column names enclosed by brackets (“{}”) to specify where values from the tab name in the dx_command field should be inserted in the message.

dx_SMTP / used to create an SMTP message
dx_importid / varchar(8) / Import ID that ties to other items, uniquely identifying an import
dx_SMTPaddress / varchar(15) / IP address or name of the SMTP server
dx_messagename / varchar(40) / Internal name of message ("Log Message", "New Company Added"), not subject of e-mail
dx_messageto / Text / Email address(es)
dx_messagefrom / Text / Email address(es)
dx_messagesubject / Text / Email subject
dx_messageintro / Text / Email intro
dx_messagebody / Text / Email body (can contain data fields and be repeated)
dx_messagefooter / Text / Email footer
dx_messagesignature / Text / Email signature

Settings

Several items are controlled from the Settings table, including tab names, the specification of user variables and user defined commands. The screen below shows the settings table.

The Application column is always TMWDX. The settingSection column is the import id.

If the settingKeyword is “UserVars”, the settingValue will appear as a column in the UserVars tab once the Update Table button is clicked, and TMWDX is restarted. The setting Default column will set the default value for this variable.

If the settingKeyword is “UserCommands”, the settingValue will become a new command invoked from the script. The setting Default column will set the SQL stored procedure to invoke when this command is encountered in the script. The new command will be effective once the Update Table button is clicked, and TMWDX is restarted.

The entries with a settingKeyword beginning with “Work” and ending with ProcName are the records of the stored procedures, shown in the settingValue column, associated with the respective work tabs. These are maintained normally by selecting a stored procedure from the drop-down list on the tab with which you want the stored procedure linked.

The entries with a settingKeyword beginning with “tpWork” are the names, in the settingValue column, of the tabs set through “Edit/Edit Tab Name” on the menu bar.

There are special settings to influence TMWDX behavior.

The settingKeyword PreImportTask, with a task specified in the setting value field runs that task prior to importing data.

The settingKeyword PostImportTask, with a task specified in the setting value field runs that task after importing data.

The settingKeyword AddArchDTTM, with a setting value of a date time format (see appendix A), will append that date time to the import file name when it moves it to the archive folder, should an archive folder be specified.

For example, with an import file of InFile and a datetime format of _yyyyMMdd_HHmmss_fff, the InFile is archived as InFile_20051005142133_325, if it was archived at 14:21:33.325 on October 5, 2005.

Company Proprietary / TMW Systems, Inc. 2019 / 1
TMW SmartLink Data Exchange Agent / Version: 1.6
User Guide / Date: 1/19/2019

Scripting

Imports are carried out under the control of scripts for each process. These scripts contain simple commands to:

These commands:

• Move data from the “Source” tab page to the other stored procedures tab pages,
• Test the data on any tab page,
• Convert data with expressions,
• Look up substitution for data fields
• Control the flow of script commands,
• Change the current position on each page,
• Execute the stored procedure associated with each tab page.
• Run special functions.

The following figure gives a screen showing a partial script.

The table below contains the use and syntax of each scripting command.

TMW Data Exchange Agent Script Commands
Description / Command / Parameter1 / Parameter2 / Parameter3 / Parameter4 / Parameter5 / Parameter6
Append a string value from a cell in a source tab to a cell in a destination tab / APPEND / Source Tab / Source Cell / Destination Tab / Destination Cell
Copy from one grid cell to another / COPY / Source Tab / Source Cell / Destination Tab / Destination Cell / “int” or Custom Date Format (see Appendix A) / Default value
Copy cells in a column from one grid to a column for all the rows that exist in a destination grid. / COPYALLFROM / Source Tab / Source Cell / Destination Tab / Destination Cell / “int” or Custom Date Format (see Appendix A) / Default value
Copy cells in a column from all rows in one grid to a column n a destination grid. Create the row in the destination if it doesn’t exist. / COPYTOALL / Source Tab / Source Cell / Destination Tab / Destination Cell / “int” or Custom Date Format (see Appendix A) / Default value
Copy an expressions from one grid cell to another (see Appendix A for expression syntax) / COPYEXP / Source Tab / Source Cell / Destination Tab / Destination Cell
Label script entry points for subroutines and GOTO commands. The label is a single word, prefixed with a colon / :label
Go to :label / GOTO / :label
Call routine at :label / CALL / :label
Return from routine / RETURN
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / EQUAL TO / PREVIOUS / CALL / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / EQUAL TO / NEXT / CALL / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / EQUAL TO / Value / CALL / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / EQUAL TO / PREVIOUS / GOTO / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / EQUAL TO / NEXT / GOTO / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / EQUAL TO / Value / GOTO / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / NOT EQUAL TO / PREVIOUS / CALL / :label
Compare values and pass control to another part of the script on result of comparison / IF / Source Tab / Source Cell / NOT EQUAL TO / NEXT / CALL / :label