tmws.tmwsystems.com · web viewuser guide version 1.6 tmw proprietary information revision history...

TMW SmartLink Data Exchange Agent (TMWDX)

Version 1.6

TMW Proprietary Information

TMW SmartLink Data Exchange Agent Version: 1.6User Guide Date: 5/15/2023

Revision HistoryDate 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

Company Proprietary TMW Systems, Inc. 2023 2


Table of Contents

TMW SmartLink Data Exchange Agent (TMWDX).......................................................1User Guide.......................................................................................................................1

Version 1.6...........................................................................................................................1TMW Proprietary Information............................................................................................1Revision History..................................................................................................................2Introduction..........................................................................................................................4Application Installation.......................................................................................................5Application Overview........................................................................................................11Setup..................................................................................................................................14

Source Tab.................................................................................................................15Work Tabs Associated with Stored Procedures.........................................................16Import Types..............................................................................................................18Field Names...............................................................................................................20Lookups.....................................................................................................................21Log Messages............................................................................................................22Email Messages.........................................................................................................23Settings......................................................................................................................24

Scripting.............................................................................................................................26Debugging..................................................................................................................36

Operation...........................................................................................................................38Logging......................................................................................................................39

Appendix A – Custom Date-Time Formats.......................................................................40Appendix B - Expressions.................................................................................................46

EXPRESSION SYNTAX..............................................................................................46USER-DEFINED VALUES..........................................................................................47STRING OPERATORS.................................................................................................48WILDCARD CHARACTERS......................................................................................48FUNCTIONS.................................................................................................................49

CONVERT................................................................................................................49LEN............................................................................................................................49ISNULL.....................................................................................................................49IIF..............................................................................................................................50TRIM.........................................................................................................................50SUBSTRING.............................................................................................................50

Appendix C – DX Scripting Command Tips and Tricks...................................................51REGEX......................................................................................................................51Link............................................................................Error! Bookmark not defined.PARSE.......................................................................................................................56SENDFILE................................................................................................................58



IntroductionThis 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 configurationsdx_importid varchar(8) Import ID that ties to other items, uniquely identifying an importdx_importname varchar(255) name of import ("EDI 204,” "gLOG IMPORT,” "MANUGISTICS", etc.)dx_importtype varchar(8) FLATdx_recordtype varchar(8) ORDERdx_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 namesdx_archive bit Check to enable archiving of parsed records to dx_Archive database tabledx_archivedir varchar(255) Directory path to archive directory

dx_filewatch_enabled bitCheck to enable filewatch on a folder—as opposed to polling (work in progress

dx_polling_enabled bit Check to enable polling-recommended methoddx_polling_minutes float Number of polling minutes. Decimal fractions are alloweddx_timecheck_seconds bigint How often to check the time for polling. dx_import_file_mask varchar(50) Mask used for input filesdx_delimiters varchar(30) Delimiters for delimited flat filesdx_faileddir varchar(255) Directory path to failed directorydx_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 filesdx_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 datadx_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_Messageused to translate return codes into messages*****

dx_command varchar(40)dx_returncode Intdx_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 messagedx_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.



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


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


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



TMW Data Exchange Agent Script Commands Description Command Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6Return 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


NOT EQUAL TO NEXT CALL :label


NOT EQUAL TO Value CALL :label



TMW Data Exchange Agent Script Commands Description Command Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6


NOT EQUAL TO PREVIOUS GOTO :label


NOT EQUAL TO NEXT GOTO :label


NOT 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 EMPTY PREVIOUS CALL :label

Compare values and pass control to another part of the script on result of comparison IF Source Tab Source Cell EMPTY NEXT CALL :label

Compare values and pass control to another part of the script on result of comparison IF Source Tab Source Cell EMPTY CURRENT CALL :label

Compare values and pass control to another part of the script on result of comparison IF Source Tab Source Cell EMPTY PREVIOUS GOTO :label

Compare values and pass control to another part of the script on result of comparison IF Source Tab Source Cell EMPTY NEXT GOTO :label

Compare values and pass control to another part of the script on result of comparison IF Source Tab Source Cell EMPTY CURRENT GOTO :label





NOT EMPTY PREVIOUS CALL :label


NOT EMPTY NEXT CALL :label


NOT EMPTY CURRENT CALL :label


NOT EMPTY PREVIOUS GOTO :label


NOT EMPTY NEXT GOTO :label


NOT EMPTY CURRENT GOTO :label

Compare values using a regular expression and pass control to another part of the script on result of comparison. See Appendix C IF Source Tab Source Cell REGEX

Regex expression

CALL or GOTO :label

Extract the data from the source tab cell that matches the regular expression and place it in the destination tab cell. See Appendix C IF Source Tab Source Cell REGEX

Regex expression

Destination tab

Destination Cell

Echo message in message box with the corresponding

ECHO Message



TMW Data Exchange Agent Script Commands Description Command Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6messageCreate a entry in the Log history LOG Message Source Tab Source CellPut a message in the info window in the upper right corner of the TMWDX main screen INFO MessageDisplay cell contents on the status line at the bottom of the main TMWDX screen STATUS Source Tab Source CellEnd of script END Goes to the next row of data in the specified grid. For the Source tab only, if no more rows, script moves to :label NEXTROW Source Tab :label Lookup miles between locations in cells. The origin and destination cells must be in the same column. MILES Source Tab PREVIOUS Source Tab

Source Column

Destination Tab

Destination Cell

Lookup miles between locations in cells. The origin and destination cells must be in the same column. ALK only. MILES Source Tab NEXT Source Tab

Source Column

Destination Tab

Destination Cell

Parse the nth (starting with 0) item from the string at the source tab cell and place it in the destination cell. See Appendix C. PARSE

Zero-based item pointer Source Tab Source Cell

Destination Tab

Destination Cell

OptionalDefault Value




For a user command that returns a result set, fill the tab with the input parameters for the command PARMS

User command Source Tab

Translate a value in a cell to another value. Different flavors specified by type, and the value from the source cell on the source tab. Translations are held in the Lookup table. LOOKUP

Lookup Type Source Tab Source Cell

Destination Tab

Destination Cell

OptionalDefault Value

Clear the tab of data CLEAR Source Tab Clear all working tabs of data CLEAR Source Tab Execute the stored procedure on the tab specified on the current row data in the grid on the tab EXECUTE Source Tab Execute the stored procedure on the tab specified on all data rows in the grid on the tab, optionally calling the routine at :label after each execution, to allow error handling EXECUTE Source Tab ALL CALL :label Dynamically change the stored procedure associated with the tab. The procedure parameters in the grid change automatically. The association will revert to the original setting on restart SETPROC Target Tab

Stored procedure name



TMW Data Exchange Agent Script Commands Description Command Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6Copy from the Source tab ONLY from previous row COPYFROMPREVIOUSROW

Source Column

Copy from the Source tab ONLY to next row COPYFROM NEXTROW

Source Column

Increment specified tab, specified cell INCREMENT Source Tab Cell Copy and increment from the specified tab, specified cell from previous row INCREMENTFROMPREVIOUSROW Source Tab Cell Copy the value in the row number corresponding to the value in the destination column to that destination column, for all rows in the destination. See Appendix C LINK Source Tab Source Cell

Destination Tab

Destination Cell

Set destination cell on destination tab to value, optionally for ALL rows SETVALUE Value

Destination Tab

Destination Cell ALL

Build file using message as specified in the SMTP table. Use value to look up message. Specify “ALL” to use all the rows in the source tab in the message. Otherwise, only the current row is used to replace fields in the message body. SENDFILE Value Source Tab ALL

File specification including path, name and extension

Send mail message as specified in the SMTP table. Use value to look up message. Specify “ALL” to use all the rows in the source tab in the message.

SENDMAIL Value Source Tab ALL



TMW Data Exchange Agent Script Commands Description Command Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6Otherwise, only the current row is used to replace fields in the message body.Set flag to value of Source cell SETFLAG Source Cell Makes Source row the current row on Source tab RESETROW Source Tab Source Row



Debugging

Script debugging support is available in Edit/Edit Setup mode. Single stepping of the script is supporting, as is running until a certain line is executed.

Check the single step check box to enable single stepping. The Single Step button appears. Click this button to single step. You can inspect any of the grids during debugging.

For break points, you can now type in a line number after which to break. If there is a file in the import location, run can bring in the file into the source tab clicking the timer button.

The break window is cleared on a break, and the UI is placed in single step mode.

As well, the script processor in the application checked to make sure that the tabs referred to by the script commands are present, and that labels in the code exist.



If an issue exists, the cell with the error will be flagged with a red dot containing a white exclamation point.

Additionally, a diagnostic message is displayed.



Operation

To operate the TMWDX application once it is set up, click the “TMW Data Exchange” item on your start menu.

A window like the one shown below will be presented. To stop application polling, click on the button with the polling countdown in it, circled in red in the figure below:

To exit the application, click on the red “x” in the upper right hand corner of the TMWDX main window, or select the “Exit” menu item.



Logging

The logging level is set in the main screen.



Appendix A – Custom Date-Time Formats

Format specifier Description

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 additional "d" characters)

Displays the full 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 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 additional "g" characters)

Displays the era (A.D. for example) 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 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 additional "h" characters)

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 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 additional "H" characters)

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 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 additional "m" characters)

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 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 additional "s" characters)

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 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 additional "t" characters)

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



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 additional "z" characters)

Displays the time zone offset for the system's current time zone in hours and minutes. The 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 <= 50.00"

"Birthdate < #1/31/82#"

For columns that contain enumeration values, cast the value to an integer data type. For example:

"EnumColumn = 5"

OPERATORS

Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'



When creating comparison expressions, the following operators are allowed:

<><=>=<>=INLIKE

The following arithmetic operators are also supported in expressions:

+ (addition)- (subtraction)* (multiplication)/ (division)% (modulus)

STRING OPERATORS

To concatenate a string, use the + character.

WILDCARD CHARACTERS

Both the * and % can be used interchangeably for wildcards in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be escaped in brackets ([]). If a bracket is in the clause, the bracket characters should be escaped in brackets (for example [[] or []]). A wildcard is allowed at the beginning and end of a pattern, or at the end of a pattern, or at the beginning of a pattern. For example:

"ItemName LIKE '*product*'"

"ItemName LIKE '*product'"

"ItemName LIKE 'product*'"

Wildcards are not allowed in the middle of a string. For example, 'te*xt' is not allowed.



FUNCTIONS

The following functions are supported:

CONVERT

Description Converts given expression to a specified type.

Syntax Convert(expression, type) Arguments expression-- The expression to convert. type-- The type to which the value will be converted.

Example: myDataColumn.Expression="Convert(total, 'System.Int32')"

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

LEN

Description Gets the length of a string Syntax LEN(expression) Arguments expression-- The string to be evaluated.

Example: myColumn.Expression="Len(ItemName)"

ISNULL

Description Checks an expression and either returns the checked expression or a replacement value. Syntax ISNULL(expression, replacementvalue) Arguments expression-- The expression to check. replacementvalue-- If expression is a null reference (Nothing), replacementvalue is returned. Example: myColumn.Expression="IsNull(price, -1)"



IIF

Description Gets one of two values depending on the result of a logical expression. Syntax IIF(expr, truepart, falsepart) Arguments expr-- The expression to evaluate. truepart-- The value to return if the expression is true.

falsepart-- The value to return if the expression is false.

Example: myColumn.Expression = "IIF(total>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]



Here’s the result:



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:



Stops:

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



Notes after LINK executes:



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.



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.


tmws.tmwsystems.com · web viewuser guide version 1.6 tmw proprietary information revision history...

Documents