TMW Systems



TMW SmartLink( Data Exchange Agent (TMWDX)

User Guide

Version 1.6

TMW Proprietary Information

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|jday |

| | |application. | |

|8/5/2005 |1.6 |Revised for additions to the TMWDX command set. Add |jday |

| | |updated images. | |

| | | | |

Table of Contents

TMW SmartLink( Data Exchange Agent (TMWDX) 1

User Guide 1

Version 1.6 1

TMW Proprietary Information 1

Revision History 2

Introduction 4

Application Installation 5

Application Overview 11

Setup 14

Source Tab 15

Work Tabs Associated with Stored Procedures 16

Import Types 18

Field Names 20

Lookups 21

Log Messages 22

Email Messages 23

Settings 24

Scripting 26

Debugging 36

Operation 38

Logging 39

Appendix A – Custom Date-Time Formats 40

Appendix B - Expressions 46

EXPRESSION SYNTAX 46

USER-DEFINED VALUES 47

STRING OPERATORS 48

WILDCARD CHARACTERS 48

FUNCTIONS 49

CONVERT 49

LEN 49

ISNULL 49

IIF 50

TRIM 50

SUBSTRING 50

Appendix C – DX Scripting Command Tips and Tricks 51

REGEX 51

Link Error! Bookmark not defined.

PARSE 56

SENDFILE 58

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.

[pic]

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.

[pic]

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

[pic]

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

[pic]

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.

[pic]

Application Overview

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

[pic]

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.

[pic]

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

[pic]

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:

[pic]

Source Tab

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

[pic]

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.

[pic]

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.

[pic]

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

[pic]

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.

[pic]

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

A screen like that below is displayed.

[pic]

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.

[pic]

|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.

[pic]

|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.

[pic]

|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.

[pic]

|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.

[pic]

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.

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.

[pic]

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

|TMW Data Exchange Agent Script Commands | | | | | | |

|  |  |

|d |Displays the current day of the month, measured as a number between 1 and 31, inclusive. If the |

| |day is a single digit only (1-9), then it is displayed as a single digit. |

| |Note that if the 'd' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard short date pattern format specifier. If the 'd' format specifier is |

| |passed with other custom format specifiers or the '%' character, it is interpreted as a custom |

| |format specifier. |

|dd |Displays the current day of the month, measured as a number between 1 and 31, inclusive. If the |

| |day is a single digit only (1-9), it is formatted with a preceding 0 (01-09). |

|ddd |Displays the abbreviated name of the day for the specified DateTime. If a specific valid format |

| |provider (a non-null object that implements IFormatProvider with the expected property) is not |

| |supplied, then the AbbreviatedDayNames property of the DateTimeFormat and its current culture |

| |associated with the current thread is used. Otherwise, the AbbreviatedDayNames property from the|

| |specified format provider is used. |

|dddd (plus any number of |Displays the full name of the day for the specified DateTime. If a specific valid format |

|additional "d" characters) |provider (a non-null object that implements IFormatProvider with the expected property) is not |

| |supplied, then the DayNames property of the DateTimeFormat and its current culture associated |

| |with the current thread is used. Otherwise, the DayNames property from the specified format |

| |provider is used. |

|f |Displays seconds fractions represented in one digit. |

| |Note that if the 'f' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the full (long date + short time) format specifier. If the 'f' format specifier |

| |is passed with other custom format specifiers or the '%' character, it is interpreted as a |

| |custom format specifier. |

|ff |Displays seconds fractions represented in two digits. |

|fff |Displays seconds fractions represented in three digits. |

|ffff |Displays seconds fractions represented in four digits. |

|fffff |Displays seconds fractions represented in five digits. |

|ffffff |Displays seconds fractions represented in six digits. |

|fffffff |Displays seconds fractions represented in seven digits. |

|g or gg (plus any number of |Displays the era (A.D. for example) for the specified DateTime. If a specific valid format |

|additional "g" characters) |provider (a non-null object that implements IFormatProvider with the expected property) is not |

| |supplied, then the era is determined from the calendar associated with the DateTimeFormat and |

| |its current culture associated with the current thread. |

| |Note that if the 'g' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard general format specifier. If the 'g' format specifier is passed with|

| |other custom format specifiers or the '%' character, it is interpreted as a custom format |

| |specifier. |

|h |Displays the hour for the specified DateTime in the range 1-12. The hour represents whole hours |

| |passed since either midnight (displayed as 12) or noon (also displayed as 12). If this format is|

| |used alone, then the same hour before or after noon is indistinguishable. If the hour is a |

| |single digit (1-9), it is displayed as a single digit. No rounding occurs when displaying the |

| |hour. For example, a DateTime of 5:43 returns 5. |

|hh, hh (plus any number of |Displays the hour for the specified DateTime in the range 1-12. The hour represents whole hours |

|additional "h" characters) |passed since either midnight (displayed as 12) or noon (also displayed as 12). If this format is|

| |used alone, then the same hour before or after noon is indistinguishable. If the hour is a |

| |single digit (1-9), it is formatted with a preceding 0 (01-09). |

|H |Displays the hour for the specified DateTime in the range 0-23. The hour represents whole hours |

| |passed since midnight (displayed as 0). If the hour is a single digit (0-9), it is displayed as |

| |a single digit. |

|HH, HH (plus any number of |Displays the hour for the specified DateTime in the range 0-23. The hour represents whole hours |

|additional "H" characters) |passed since midnight (displayed as 0). If the hour is a single digit (0-9), it is formatted |

| |with a preceding 0 (01-09). |

|m |Displays the minute for the specified DateTime in the range 0-59. The minute represents whole |

| |minutes passed since the last hour. If the minute is a single digit (0-9), it is displayed as a |

| |single digit. |

| |Note that if the 'm' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard month day pattern format specifier. If the 'm' format specifier is |

| |passed with other custom format specifiers or the '%' character, it is interpreted as a custom |

| |format specifier. |

|mm, mm (plus any number of |Displays the minute for the specified DateTime in the range 0-59. The minute represents whole |

|additional "m" characters) |minutes passed since the last hour. If the minute is a single digit (0-9), it is formatted with |

| |a preceding 0 (01-09). |

|M |Displays the month, measured as a number between 1 and 12, inclusive. If the month is a single |

| |digit (1-9), it is displayed as a single digit. |

| |Note that if the 'M' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard month day pattern format specifier. If the 'M' format specifier is |

| |passed with other custom format specifiers or the '%' character, it is interpreted as a custom |

| |format specifier. |

|MM |Displays the month, measured as a number between 1 and 12, inclusive. If the month is a single |

| |digit (1-9), it is formatted with a preceding 0 (01-09). |

|MMM |Displays the abbreviated name of the month for the specified DateTime. If a specific valid |

| |format provider (a non-null object that implements IFormatProvider with the expected property) |

| |is not supplied, the AbbreviatedMonthNames property of the DateTimeFormat and its current |

| |culture associated with the current thread is used. Otherwise, the AbbreviatedMonthNames |

| |property from the specified format provider is used. |

|MMMM |Displays the full name of the month for the specified DateTime. If a specific valid format |

| |provider (a non-null object that implements IFormatProvider with the expected property) is not |

| |supplied, then the MonthNames property of the DateTimeFormat and its current culture associated |

| |with the current thread is used. Otherwise, the MonthNames property from the specified format |

| |provider is used. |

|s |Displays the seconds for the specified DateTime in the range 0-59. The second represents whole |

| |seconds passed since the last minute. If the second is a single digit (0-9), it is displayed as |

| |a single digit only. |

| |Note that if the 's' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard sortable date/time pattern format specifier. If the 's' format |

| |specifier is passed with other custom format specifiers or the '%' character, it is interpreted |

| |as a custom format specifier. |

|ss, ss (plus any number of |Displays the seconds for the specified DateTime in the range 0-59. The second represents whole |

|additional "s" characters) |seconds passed since the last minute. If the second is a single digit (0-9), it is formatted |

| |with a preceding 0 (01-09). |

|t |Displays the first character of the A.M./P.M. designator for the specified DateTime. If a |

| |specific valid format provider (a non-null object that implements IFormatProvider with the |

| |expected property) is not supplied, then the AMDesignator (or PMDesignator) property of the |

| |DateTimeFormat and its current culture associated with the current thread is used. Otherwise, |

| |the AMDesignator (or PMDesignator) property from the specified IFormatProvider is used. If the |

| |total number of whole hours passed for the specified DateTime is less than 12, then the |

| |AMDesignator is used. Otherwise, the PMDesignator is used. |

| |Note that if the 't' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard long time pattern format specifier. If the 't' format specifier is |

| |passed with other custom format specifiers or the '%' character, it is interpreted as a custom |

| |format specifier. |

|tt, tt (plus any number of |Displays the A.M./P.M. designator for the specified DateTime. If a specific valid format |

|additional "t" characters) |provider (a non-null object that implements IFormatProvider with the expected property) is not |

| |supplied, then the AMDesignator (or PMDesignator) property of the DateTimeFormat and its current|

| |culture associated with the current thread is used. Otherwise, the AMDesignator (or |

| |PMDesignator) property from the specified IFormatProvider is used. If the total number of whole |

| |hours passed for the specified DateTime is less than 12, then the AMDesignator is used. |

| |Otherwise, the PMDesignator is used. |

|y |Displays the year for the specified DateTime as a maximum two-digit number. The first two digits|

| |of the year are omitted. If the year is a single digit (1-9), it is displayed as a single digit.|

| | |

| |Note that if the 'y' format specifier is used alone, without other custom format strings, it is |

| |interpreted as the standard short date pattern format specifier. If the 'y' format specifier is |

| |passed with other custom format specifiers or the '%' character, it is interpreted as a custom |

| |format specifier. |

|yy |Displays the year for the specified DateTime as a maximum two-digit number. The first two digits|

| |of the year are omitted. If the year is a single digit (1-9), it is formatted with a preceding 0|

| |(01-09). |

|yyyy |Displays the year for the specified DateTime, including the century. If the year is less than |

| |four digits in length, then preceding zeros are appended as necessary to make the displayed year|

| |four digits long. |

|z |Displays the time zone offset for the system's current time zone in whole hours only. The offset|

| |is always displayed with a leading sign (zero is displayed as "+0"), indicating hours ahead of |

| |Greenwich mean time (+) or hours behind Greenwich mean time (-). The range of values is –12 to |

| |+13. If the offset is a single digit (0-9), it is displayed as a single digit with the |

| |appropriate leading sign. The setting for the time zone is specified as +X or –X where X is the |

| |offset in hours from GMT. The displayed offset is affected by daylight savings time. |

|zz |Displays the time zone offset for the system's current time zone in whole hours only. The offset|

| |is always displayed with a leading or trailing sign (zero is displayed as "+00"), indicating |

| |hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-). The range of |

| |values is –12 to +13. If the offset is a single digit (0-9), it is formatted with a preceding 0 |

| |(01-09) with the appropriate leading sign. The setting for the time zone is specified as +X or |

| |–X where X is the offset in hours from GMT. The displayed offset is affected by daylight savings|

| |time. |

|zzz, zzz (plus any number of |Displays the time zone offset for the system's current time zone in hours and minutes. The |

|additional "z" characters) |offset is always displayed with a leading or trailing sign (zero is displayed as "+00:00"), |

| |indicating hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-). The |

| |range of values is –12:00 to +13:00. If the offset is a single digit (0-9), it is formatted with|

| |a preceding 0 (01-09) with the appropriate leading sign. The setting for the time zone is |

| |specified as +X or –X where X is the offset in hours from GMT. The displayed offset is affected |

| |by daylight savings time. |

|: |Time separator. |

|/ |Date separator. |

|" |Quoted string. Displays the literal value of any string between two quotation marks preceded by |

| |the escape character (/). |

|' |Quoted string. Displays the literal value of any string between two " ' " characters. |

|%c |Where c is both a standard format specifier and a custom format specifier, displays the custom |

| |format pattern associated with the format specifier. |

| |Note that if a format specifier is used alone as a single character, it is interpreted as a |

| |standard format specifier. Only format specifiers consisting of two or more characters are |

| |interpreted as custom format specifiers. In order to display the custom format for a specifier |

| |defined as both a standard and a custom format specifier, precede the specifier with a % symbol.|

|\c |Where c is any character, the escape character displays the next character as a literal. The |

| |escape character cannot be used to create an escape sequence (like "\n" for new line) in this |

| |context. |

|Any other character |Other characters are written directly to the result string as literals. |

Appendix B - Expressions

EXPRESSION SYNTAX

When creating an expression in a cell, use the column name to refer to columns. For example, if the column name for one column is "UnitPrice", and another "Quantity", the expression would be:

"UnitPrice * Quantity"

When creating an expression for a filter, enclose strings with single quotes:

"LastName = 'Jones'"

The following characters are special characters and must be escaped, as explained below, if they are used in a column name:

\n (newline)

\t (tab)

\r (carriage return)

~

(

)

#

\

/

=

>

<

+

-

*

%

&

|

^

'

"

[

]

If a column name contains one of the above characters, the name must be wrapped in brackets. For example to use a column named "Column#" in an expression, you would write "[Column#]":

Total * [Column#]

Because brackets are special characters, you must use a slash ("\") to escape the bracket, if it is part of a column name. For example, a column named "Column[]" would be written:

Total * [Column[\]]

(Only the second bracket must be escaped.)

USER-DEFINED VALUES

User-defined values may be used within expressions to be compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs (#). Decimals and scientific notation are permissible for numeric values. For example:

"FirstName = 'John'"

"Price 1000, 'expensive', 'dear')

TRIM

Description Removes all leading and trailing blank characters like\r,\n,\t, ' '

Syntax TRIM(expression)

Arguments expression-- The expression to trim.

SUBSTRING

Description Gets a sub-string of a specified length, starting at a specified point in the string.

Syntax SUBSTRING(expression, start,

length)

Arguments expression-- The source string for the substring.

start-- Integer that specifies where the substring begins.

length-- Integer that specifies the length of the substring.

Example: myColumn.Expression = "SUBSTRING(phone, 7, 8)"

Appendix C – DX Scripting Command Tips and Tricks

REGEX

TMWDX Implements REGEX of two kinds:

IF srctab srccell REGEX regex_expression GOTO|CALL label

If there is a match with the source cell goto the label or call the routine at the label.

More powerfully:

IF srctab srccell REGEX regex_expression desttab destcell

If there is a match with the source cell, copy the MATCH to the destination cell.

In the screen shot below, line 1010 sets up a string to parse. Line 1030 matches and places the match in UserVars work3, using the following e-mail regex matcher:

[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]

[pic]

Here’s the result:

[pic]

LINK

You don’t have to just replace, say, stop numbers in stop reference records, which is a typical use. As long as the target field has the sequence of the record in the parent, any field from the parent can be copied into the target field in the child.

For example, transferring the corresponding @cmp_id from stops grid to notes grid:

Notes:

[pic]

Stops:

[pic]

LINK command for linking in company id from stops to notes:

[pic]

Notes after LINK executes:

[pic]

PARSE

PARSE [zero-based item pointer] [source tab] [source cell] [dest tab] [dest cell] [optional default value]

Picks the nth value out of the parsed source string and places that result in the destination.

[pic]

[pic]

[pic]

SENDFILE

The SENDFILE command at line 1200 in the figure below will use the mail message with the name TESTER and substitute the data in the tab into the message for each row in the tab, and then make the specified file.

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download