User Guide : Scripting : Script Functions : Script Functions G-M
 
Share this page             
Script Functions G-M
This section includes information on setting the scope of functions, as well as details on each of the available functions.
GetDirectory Function
 
Description
Finds the name of the current directory
Return
Name of the current directory or folder
Return (Null)
Not applicable for this function
Syntax
GetDirectory()
Parameters
No parameters are required, however, GetDirectory must be followed by parentheses.
Remarks
This function returns the full path name of the current working directory of the application process.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
FileWrite("The current directory is " & GetDirectory())
GetObject Function
 
Description
Locate an ActiveX object reference
Return
Returns a reference to an object provided by an ActiveX component
Return (Null)
Error: Invalid expression syntax
Syntax
GetObject(objectName [,className])
Parameters
This function has the following parameters:
objectName (required) - Full path name of the file containing the object to retrieve. Enclose in quotation marks.
className (optional) - A string representing the object class name.
Remarks
Use GetObject to access an ActiveX object from a file and assign the object to an object variable. Use the Set statement to assign the object returned by the GetObject to the object variable.
Use GetObject when there is a current instance of the object, or if you want to create the object with a file already loaded. If there is no current instance, and you do not want the object started with a file loaded, use CreateObject.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Dim xlApp As Object
Set xlApp = GetObject("Excel.Application")
GetThreadID Function
 
Description
Identifies the individual thread running a specific process
Return
Unique string that represents the thread ID
Return (Null)
Not applicable for this function
Syntax
GetThreadID()
Parameters
None.
Remarks
When you run a process using multiple threads, it is sometimes critical to know, within the transformation, which particular thread that transformation is using. This enables you to create and use global objects such as message objects and import objects.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
'Returns 000003c4:
GetThreadID()
GSub Function
 
Description
Globally replaces every instance of an expression string in one field of data with a specified string. You may substitute ReplaceAll for GSub, as both are identical in syntax.
Return
Value specified in newstring (overwrites oldstring)
Return (Null)
Error: Incorrect argument for function.
Syntax
GSub(oldstring, newstring, string)
Parameters
This function has the following parameters:
oldstring - The original string that the expression searches for to replace with the newstring. If a string literal, enclose in quotation marks.
newstring - The string that replaces every instance of the oldstring. If a string literal, enclose in quotation marks.
string - Any string containing data. If a string literal, enclose in quotation marks.
Remarks
The return value of this function is the string specified by the fieldname parameter. All sections that match the regular expression specified as the oldstring parameter are replaced by the string specified as the newstring parameter.
Be careful to observe case when using a function that performs a string-search. String-search functions are case-sensitive.
GSub recognizes all special characters in the oldstring parameter. All special characters must be escaped to be recognized. See Regular Expressions.
If you attempt to use this function on very large strings (50K – 100K and above), you may experience a performance hit. To avoid long run times of your transformations, do not use GSub on very large strings.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
The '(' and ')' characters are used for grouping in regular expressions. The expression "(abc)" matches one or more occurrences of the string abc. If you want to search for the literal characters "("and ")", use a backslash as an escape character.
Below, the special characters "(" and ")" in Field1 are escaped with the "\" symbol, and replaced by an empty string in the target:
GSub("\(", " ", GSub("\)", " ", FieldAt("/SOURCE/R1/Field1")))
Replace "Avenue" with "Ave." in the Address field:
GSub("Avenue", "Ave.", FieldAt("Address"))
Look for "Mon" or "Fri" in ("Field1") of the source file and replace them with "Monday" and "Friday", respectively. If any value is found in ("Field1") other than "Mon" or "Fri", leave data alone:
T = GSub("Mon.", "Monday", FieldAt("/SOURCE/R1/Field1"))
GSub("Fri.", "Friday", T)
Another way to do the previous example is to nest the two substitutions together:
GSub("Mon.", "Monday", GSub("Fri.", "Friday", FieldAt("/SOURCE/R1/FIELD1")))
If you want to delete blank spaces within a character field, you must put two spaces before the asterisk in the first parameter and no space between the quotes in the second parameter. "FIELD" is the field name of the character field in your source file from which you want to delete the blank spaces:
GSub(" *", "", FieldAt("/SOURCE/R1/FIELD"))
To delete all non alphanumeric characters from a field:
GSub("[^A-Za-z0-9n]","", FieldAt("/SOURCE/R1/Field1"))
Hex Function
 
Description
Prints the hexadecimal representation of a number
Return
String representing the hexadecimal value of a supplied number
Return (Null)
Error: Incorrect argument for function.
Syntax
Hex(number)
Parameters
This function has the following parameters:
number (required) - Any numeric expression:
If number is null, Hex returns null.
If number is empty, Hex returns zero (0).
If number is any other number, Hex returns up to eight octal characters.
Remarks
You can represent hexadecimal numbers directly by preceding numbers in the proper range with &H. For example, &H10 represents decimal 16 in hexadecimal notation.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
Dim MyHex
MyHex = Hex(5) 'Returns 5.
MyHex = Hex(10) 'Returns A.
Hour Function
 
Description
Converts clock time to an hour integer
Return
Integer between 0 and 23, inclusive, which represents the hour of the day corresponding to the time provided as an argument.
Return (Null)
Error: Incorrect argument count for function.
Syntax
Hour(dateString)
or
Hour(TimeString)
Parameters
This function has one of the following parameters:
dateString if containing time value.
TimeString: Any value or expression that can represent a date/time.
Remarks
Numbers to the left of the decimal point in number represent the date; numbers to the right of the decimal point represent the time. Negative numbers represent dates prior to December 30, 1899.
If number is null, this function returns a null.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
Return the hour portion of the system time to the target field (if system time is 1:12:30, this function returns "1" to the target:
Hour(FieldAt("/SOURCE/R1/Field1"))
Return the hour portion from the source ("Hour"). The TimeValue function defines each time to be time values, while the Hour function defines those values as hours:
a = TimeValue(Trim(FieldAt("/SOURCE/R1/Hour")))
Hour(a)
IIf Function
 
Description
Returns a value based on whether an expression evaluates to true or false
Return
One of two arguments depending on the evaluation of an expression
Return (Null)
Error: Incorrect argument count for function
Syntax
IIf(expr, truepart, falsepart)
Parameters
This function has the following parameters:
expr - Expression you want to evaluate.
truepart - Value or expression returned if expr is True (-1). If a string literal, enclose in quotation marks.
falsepart - Value or expression returned if expr is False (0). If a string literal, enclose in quotation marks.
Remarks
You may use IIf (immediately if) to evaluate an expression and return either of two other values.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
In this example, if "Account_No" is greater than 10025 and less than 10090, then insert "Bill", else insert an empty string "":
IIf(Val(FieldAt("/SOURCE/R1/Account_No")) > 10025 and Val(FieldAt("/SOURCE/R1/Account No")) < 10090, "Bill", "")
ILAddrow Function
 
Description
Adds a row in an existing lookup table in resident memory.
Return
Not applicable for this function.
Return (Null)
Error: Incorrect argument count
Syntax
ILAddrow (Name, KeyValue, Field1, Value1, [Field2, Value2, ...])
Parameters
This function has the following parameters:
Name (required) - Name of the existing incore lookup table. If a string literal, enclose in quotation marks.
KeyValue (required) - Key value to add to the lookup table. If a string literal, enclose in quotation marks.
Field1 (required) - Result field name in existing lookup table. If a string literal, enclose in quotation marks.
Value1 (required) - Value of the first result field for the row being added. If a string literal, enclose in quotation marks.
Field2 (optional) - Additional result field names. If a string literal, enclose in quotation marks.
Value2 (optional, though required if using Field2) - Additional result field values. If a string literal, enclose in quotation marks.
Remarks
Multiple lookups can be performed with this function. The number of fields for lookup is not limited.
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
See Also
Example
ILAddrow ("MyIncoreTable", "Jon123", "Name", "Jones", "ZIP_Code", "12345")
ILBuild Function
 
Description
Builds an incore lookup table in resident memory
Return
Not applicable for this function.
Return (Null)
Error: Incorrect argument count
Syntax
ILBuild(Name, Connector.self, [SQL Statement], KeyField, Field1, [Field2, ...])
Parameters
This function has the following parameters:
name (required) - Name of the incore lookup table to build. If a string literal, enclose in quotation marks.
Connector.self (required) - Connector property that returns an object reference (source/target/DJImport).Without this reference, a string is passed containing the type name of the Connector instead of the import object you instantiated.
SQL Statement (optional) - SQL/query language statement used to build the lookup table. If a string literal, enclose in quotation marks.
If not used, substitute an empty string ("").
Note: Can only be used with SQL-aware connectors.
KeyField (optional) - Key field used for lookups. If a string literal, enclose in quotation marks.
Field1 - Result field. If a string literal, enclose in quotation marks.
Field2 (optional) - Additional result fields. Can continue with more fields. If a string literal, enclose in quotation marks.
Caution!  The KeyField, Field1, and Field2 parameter names must match the names used in the SQL Statement. Otherwise, you may receive a type mismatch error at run time.
Remarks
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
See Also
Example
This example builds an incore lookup table and uses a SQL Statement to query the table. This example creates a lookup table called ILTest consisting of the Account No, Company, City, State, and ZIP fields from tutor1.asc (named ANO, ANAME in the table):
' Declare an import object, instantiate it as a SQL Server Connector in a BeforeTransformation event:
Dim lConnector as djimport
Set lConnector = new DJImport("SQL Server")
' Type the connect string to the SQL Server database:
lConnector.connectstring="database=pubs;username=sa"
' Clear the lookup table (to create or replace the table in multiple transformation runs):
ILClear(ILTest)
' Build a table based upon a SQL query statement:
ILBuild(ILTest, lConnector.self, select ANO, ANAME from LOOKUP, ANO, ANAME)
' Next, include this expression in a Target Field Expression cell to do the actual lookup:
ILookup(ILTest, ANAME, Fields("ANO"))
' Destroy the Connector instance:
Set lConnector = nothing
ILClear Function
 
Description
Clears a lookup table in resident memory.
Return
Not applicable for this function.
Return (Null)
Error: Incorrect argument count
Syntax
ILClear(Name)
Parameters
This function has the following parameters:
name (required) - Name of the incore lookup table to clear. If a string literal, enclose in quotation marks
Remarks
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
Tip...  Use this function to clear an incore table as a precaution in case the table was previously loaded into memory during the process run.
See Also
Example
ILClear("MyLookup")
ILFirstKey Function
 
Description
Positions the cursor at the first key of an incore lookup table.
Return
Key values of an incore table.
Return (Null)
Error: Incorrect argument count
Syntax
ILFirstKey(Name)
Parameters
This function has the following parameters:
Name (required) - Name of the incore lookup table to step through. If a string literal, enclose in quotation marks.
Remarks
This function is used in conjunction with the ILNextKey Function.
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Create a local variable called key. Build an incore lookup table called test. Step through test using this function and ILNextKey:
Dim key
ILClear("test")
ILAddrow("test", 1, "field1", A)
ILAddrow("test", 2, "field1", B)
ILAddrow("test", 3, "field1", C)
ILAddrow("test", 4, "field1", D)
ILAddrow("test", 5, "field1", E)
key = ILFirstKey("test")
While Not IsNull(key)
    MsgBox("Key: " & key)
    key = ILNextkey("test")
Wend
ILNextKey Function
 
Description
Allows stepping through the key values in an incore lookup table.
Return
Key values of an incore table.
Return (Null)
Error: Incorrect argument count
Syntax
ILNextKey(Name)
Parameters
This function has the following parameters:
Name (required) - Name of the incore lookup table to step through. If a string literal, enclose in quotation marks.
Remarks
This function is used in conjunction with the ILFirstKey Function.
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Create a local variable called key. Build an incore lookup table called test. Step through test using this function and ILNextKey:
Dim key
ILClear("test")
ILAddrow("test", 1, "field1", A)
ILAddrow("test", 2, "field1", B)
ILAddrow("test", 3, "field1", C)
ILAddrow("test", 4, "field1", D)
ILAddrow("test", 5, "field1", E)
key = ILFirstKey("test")
While Not IsNull(key)
    MsgBox("Key: " & key)
    key = ILNextkey("test")
Wend
ILookup Function
 
Description
Queries a lookup file in resident memory.
Return
Value of a field on a key match.
Return (Null)
Error: Incorrect argument count
Syntax
ILookup (Name, FieldName, KeyValue, [DefaultValue])
Parameters
This function has the following parameters:
Name (required) - Name of the incore lookup file to query. If a string literal, enclose in quotation marks.
FieldName (required) - Result field to return. If a string is literal, enclose in quotation marks.
KeyValue (required) - Key value to find in lookup file.
DefaultValue (optional) - Default value to return if the key value is not found (if not specified, null is returned).
Remarks
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
Caution!
When you use ILookup in conjunction with ILNextKey, be aware that calling ILookup causes the current key position to point to the key value used in the ILookup call (if the key value exists). If the key value does not exist, the current key position moves to the first key of the incore table.
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Place this expression in the appropriate target field expression:
ILookup("iltest", "ANAME", FieldAt("/SOURCE/R1/Name"))
ILRemoveRow Function
 
Description
Removes a row from an existing lookup table in resident memory.
Return
Not applicable for this function.
Return (Null)
Error: Incorrect argument count
Syntax
ILRemoveRow(Name, KeyValue)
Parameters
This function has the following parameters:
Name (required) - Name of the incore lookup table to be removed. If a string literal, enclose in quotation marks.
KeyValue (required) - The key value identifies the row to be removed. If a string literal, enclose in quotation marks.
Remarks
Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
See Also
Example
ILRemoveRow ("MyIncoreTable", "Jon123")
Increment Function
 
Description
Returns the next serial value.
Note:  The Serial and Increment functions are interchangeable and use the same syntax.
Return
Next serial value of a numeric expression
Return (Null)
Error: Incorrect argument count
Syntax
Increment(number)
Parameters
This function has the following parameter:
number - Any numeric expression. If number is provided, it sets the starting point.
Remarks
The Increment function returns the next serial value.
If number is provided, it sets the starting serial number. Increment returns the next serial value. This means number must be entered as the required starting value less one.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example writes an incremental number in a field for every record, starting with the number 1:
incremental(0)
 
The following example writes an incremental number in a field for every record, starting with the number 223:
incremental(222)
InitCaps Function
 
Description
Converts the first character of each word to uppercase. If the word resembles a name, a recognized abbreviation, or contains a hyphen, InitCaps applies additional capitalization. See Remarks for further information.
Return
Returns the modified string.
Return (Null)
Error: Incorrect argument count for function.
Syntax
InitCaps(string)
Parameters
This function has the following parameters:
string - Any valid string expression. If a string literal, enclose in quotation marks.
Remarks
Data in the string may be all lowercase, all uppercase, or any combination of the two.
InitCaps applies additional capitalization under specialized conditions, as follows:
Certain titles and abbreviations are always capitalized this way:
RR, PO, PhD, MD
If the string starts with "mc" and the third letter is a consonant, it is converted to McX.
Example: McKone
If the string starts with "mac", "van" or "von" and the fourth character is a consonant, it is converted to MacX, VanX or VonX. The only exception to this behavior is "Vancouver."
Example: VanDyke
If the string starts with a word followed by a hyphen, the second word is capitalized.
Example: Martin-Lewis
If the string starts with a capital letter followed by an apostrophe, the second letter is capitalized.
Example: O'Toole
Because of these special cases, certain words may be capitalized inappropriately by the InitCaps function. For example, "machine" and "vantage" become "MacHine" and "VanTage."
See Also
Example
The following expression would be used to convert the first character of the names in a field in the source called "NAME" to uppercase (with the remainder in lowercase) in the target file:
InitCaps(FieldAt("/SOURCE/R1/NAME")
InputBox Function
 
Description
Displays a dialog box with a editable text box and a prompt. The text box contains a default value.
You must click OK or Cancel to continue. If OK is clicked, this function returns the value that was provided in the text box. If Cancel is clicked, the function returns a zero-length string.
You can press Tab to select OK, and then press Enter or the space key.
Return
Dialog box input value
Return (Null)
Error: Too few arguments for function
Syntax
InputBox(prompt [, [title] [, [default] ])
Parameters
InputBox has these parameters:
prompt: The string expression displayed as the message in the dialog box. The maximum length of prompt is approximately 255 characters, depending on the width of each character used in the string. If the prompt is required to be more than one line, you must include a carriage return (ANSI 13) and a line feed (ANSI 10) between each line. If a string literal, enclose in quotation marks. InputBox(“prompt”).
title - The string expression displayed in the title bar of the dialog box. If you exclude the title, the title bar is blank. If a string literal, enclose in quotation marks. InputBox(“title”).strexpr2 (required) - String expression being searched. If a string literal, enclose in quotation marks.
default - The string expression displayed in the text box as the default response, if no other input is provided. If you exclude default, the text box is displayed empty.
Remark
InputBox returns a String.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression includes a prompt for user information and a dialog box title. For each converted record, this expression causes an InputBox dialog box to pop up:
Global A
If A = "" Then
b = InputBox("Data Entry")
a=1
b
Else
b
End If
InStr Function
 
Description
Find the position of the first occurrence of one string within another string. You may use Search instead of InStr, as both have the same syntax.
Return
Position of the first occurrence of one string within another string
Return (Null)
Error: Too few arguments for function.
Syntax 1
InStr([start,] strexpr1, strexpr2)
Syntax 2
InStr(start, strexpr1, strexpr2, compare)
Parameters
This function has the following parameters:
start (required with the compare parameter, otherwise optional) - Numeric expression that sets the starting position for each search; start must be between 1 and approximately 65,500. If start is omitted, the search of strexpr1 begins at the first character position. The start argument is not optional if the compare argument is specified.
strexpr1 (required) - String expression being searched. If a string literal, enclose in quotation marks.
strexpr2 (required) - String expression being sought. If a string literal, enclose in quotation marks.
compare (optional) - Specifies the string-comparison method. The argument compare must be 0 or 1. If compare is 0 or not provided, string comparison is case-sensitive. If compare is 1, string comparison is not case-sensitive.
Remarks
If strexpr2 is found within strexpr1, InStr returns the position at which the match was found. If strexpr2 is zero-length, start is returned. If start is greater than strexpr1, strexpr1 is zero-length, or strexpr2 can't be found, InStr returns 0.
If either string expression is null, the function returns a null. If start or compare is null, an error occurs.
Be careful to observe case when using a function that performs a string-search. String-search Functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument compare, and you do not use the optional compare argument, the expression is case-sensitive by default.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example illustrates how you can find the position of a dollar sign ($) within source field "Field1". It begins the search in the first byte of data in "Field1" and the search is not case sensitive:
Instr(1, FieldAt("/SOURCE/R1/Field1"), "$", 1)
Applied to a field containing:
Sale price $14.00
Returns: 12
InStrRev Function
 
Description
Find the position of an occurrence of one string within another, from the end of string
Return
Position of the first occurrence of one string within another string
Return (Null)
Error: Too few arguments for function
Syntax
InstrRev(stringcheck, stringmatch [,start][,compare])
Parameters
This function has the following parameters:
stringcheck - String expression being searched. If a string literal, enclose in quotation marks.
stringmatch - String expression being searched for. If a string literal, enclose in quotation marks.
start - Numeric expression that sets the starting position for each search. If omitted, -1 is used, which means that the search begins at the last character position. If start contains null, an error occurs.
compare - Numeric value indicating the kind of comparison to use when evaluating substrings. If omitted, a binary comparison is performed. A value of 0 performs a binary comparison (default), while a value of 1 performs a text comparison.
Return Values
If stringcheck is zero-length, InStrRev returns 0
If stringcheck is null, InStrRev returns Null
If stringmatch is zero-length, InStrRev returns start
If stringmatch is null, InStrRev returns Null
If stringmatch is not found, InStrRev returns 0
If stringmatch is found within stringcheck, InStrRev returns Position at which match is found.
If start > Len(stringmatch), InStrRev returns 0
Remarks
This function resembles the InStr function, but it starts at the end of the string and moves backward.
Remember case when using a function that performs a string-search. String-search functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default.
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example illustrates how you can find the position of a dollar sign ($) within a source field "Field1". It begins the search in the last byte of data (-1) in "Field1". The search is not case-sensitive:
InStrRev(FieldAt("/SOURCE/R1/Field1"), "$", -1)
Applied to a field containing:
Sale price $14.00
Returns: 12
Int Function
 
Description
Extract the integer of a number
Return
Integer portion of a number
Return (Null)
Error: Incorrect argument count for function.
Syntax
Int(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression.
Remarks
Int removes the fractional part of number and returns the resulting integer value.
The data type of the return value is the same as that of the number parameter. If the numeric expression results in a null, Int returns a null.
If number is negative, Int returns the first negative integer less than or equal to number.
The Int and Fix functions behave identically unless you are working with negative numbers. The Int function rounds to the lower negative integer, while the Fix function rounds to the higher one.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example returns -99:
Int(-98.6)
IsArray Function
 
Description
Test a variable to determine if it is an array
Return
Boolean value indicating whether a variable is an array
Return (Null)
Error: Incorrect argument count for function.
Syntax
IsArray(identifier)
Parameters
This function has the following parameters:
identifier (required) - Specifies a variable to test.
Remarks
IsArray returns True (-1) if the variable is an array; otherwise, it returns False (0).
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example
The following expression returns –1 if VarX is an array, and returns 0 if VarX is not an array:
IsArray(VarX)
IsDate Function
 
Description
Test a value to determine if it can be converted into a date type
Return
Value indicating whether or not another value can be converted to a date
Return (Null)
Error: Incorrect argument count for function.
Syntax
IsDate(value)
Parameters
This function has the following parameters:
value - Expression to be tested. If a string literal, enclose in quotation marks.
Remarks
Returns True (-1) if the expression is a valid date or can be converted one; otherwise, it returns False (0). The range of valid dates is January 1, 1 A.D., through December 31, 9999 A.D.
IsDate checks for date formats yyyy-m-d and yyyy/m/d. If neither matches, it tries converting the value to a number; if that succeeds, it tries assigning it as a date and checking for a year between 1 and 9999. If no match, it returns False.
To evaluate to True:
There must be a field separator.
The expression must include a year (when converted to a date) between 1 and 9999, inclusive.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Formats
This list shows expressions for January 1, 2003 that evaluate to True (-1) or False (0).
Evaluates to True
IsDate("2003/01/01")
IsDate("01/01/2003")
IsDate("2003-01-01")
IsDate("03-01-01")
IsDate("01-01-2003")
IsDate("01-01-03")
IsDate("2003-1-1")
IsDate("January 01, 2003")
IsDate("01-Jan-03")
IsDate("2003.01.01")
IsDate("01.01.2003")
Evaluates to False
IsDate("010103")
IsDate("January 01 2003")
IsDate("Jan 01 2003")
IsDate("Jan-01-2003")
IsDate("20030101")
IsDate("030101")
See Also
Example
Examine Field1 in the source file. If Field1 contains a valid date, then write "This is a valid date." to the target. If Field1 does not contain a valid date, then write "This is an invalid date." to the target.
IIf(IsDate(FieldAt("/SOURCE/R1/Field1")) == -1, "This is a valid date.", "This is an invalid date.")
IsDirectory Function
 
Description
Determines whether a filename is a directory or not.
Return
-1 (True) if the specified filename parameter is a directory
0 (False) if the specified filename parameter is a file
ERROR if the filename parameter does not exist (is not a file or directory)
Return (Null)
Error: Incorrect argument count for function.
Syntax
IsDirectory(directoryname)
Parameters
This function has the following parameters:
directoryname - A string containing the name of the directory to be found. A complete path specification (either absolute or relative) must be provided if the directory is not expected to exist in the current working directory. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
The IsFile and IsDirectory functions check for three possible conditions (True, False, Error). Use the FileExists function first if you want to check for a file or directory without the possibility of returning an error. If it exists, you can then test with IsFile or IsDirectory.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following script tests for the existence of a directory.
If FileExists("C:\temp") Then
  If IsDirectory("C:\temp") Then
    LogMessage("INFO","Is a Directory"))
  End If
Else
  LogMessage("INFO","Not a Directory")
End If
IsFile Function
This function works only with files. You cannot name a database file and specify a table. You cannot use this function with SQL-based connectors such as Oracle and SQL Server.
Description
Determines whether a file name is a file or not.
Return
-1 (True) if the specified filename parameter is a file
0 (False) if the specified filename parameter is a directory
ERROR if the filename parameter does not exist (is not a file or directory)
Return (Null)
Error: Incorrect argument count for function
Syntax
IsFile(filename)
Parameters
This function has the following parameters:
filename - A string containing the name of the file whose existence is to be determined. A complete path specification (either absolute or relative) must be provided if the file is not expected to exist in the current directory. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
The IsFile and IsDirectory functions check for three possible conditions (True, False, Error). Use the FileExists function first if you want to check for a file or directory without the possibility of returning an error. If it exists, you can then test with IsFile or IsDirectory.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following script tests for the existence of a file:
If FileExists("C:\test.dbf") Then
If IsFile("C:\test.dbf") Then
LogMessage("INFO","Is a File"))
End If
Else
LogMessage("INFO","Not a File")
End If
IsMacroDefined Function
 
Description
Test and see if a macro is defined or not
Return
Boolean value indicating whether or not a macro is defined
True returns a value of -1, while False returns a value of 0.
Return (Null)
Error: Incorrect argument count for function.
Syntax
IsMacroDefined(macroName)
Parameters
This function has the following parameters:
macroName - Name of the macro to determine whether it is defined or undefined. If a string literal, enclose in quotation marks.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Checks for the existence of a macro called "mymacro":
IsMacroDefined("mymacro")
IsNull Function
 
Description
Test a value for a special null value
Return
Value that indicates whether or not another value contains the special null value.
True returns a value of -1, while False returns a value of 0.
Return (Null)
True
Syntax
IsNull(value)
Parameters
This function has the following parameters:
value - Any expression. If a string literal, enclose in quotation marks.
Remarks
The IsNull function returns True if the expression contains the null value; otherwise, it returns False.
The null value indicates that the value contains no data. Null is not the same as Empty, which indicates that a value has not yet been initialized. It is also not the same as a zero-length string, which is often referred to as a null string.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
The following expression examines source "Field1" and determines whether or not it is null:
IsNull(FieldAt("/SOURCE/R1/Field1"))
The following example builds upon the example above by using additional functions. It uses the IsNull function to determine whether the value of a field is null. If the field is null, a string stating that no data was provided is returned to the target. If the field is not null, the contents of source "Field1" are returned to the target:
If IsNull(FieldAt("/SOURCE/R1/Field1")) Then "No data provided." Else FieldAt("/SOURCE/R1/Field1") End If
IsNumeric Function
 
Description
Test a value variable and determine if it can be converted to a numeric data type
Return
Boolean value indicating whether or not a value variable can be converted to a numeric data type. True returns a value of –1, while False returns a value of 0.
Return (Null)
Error: Incorrect argument count for function.
Syntax
IsNumeric(value)
Parameters
This function has the following parameters:
value - Any expression. If a string literal, enclose in question marks.
Remarks
This function returns True (-1) if value can legally be converted to a number; otherwise, it returns False (0).
Some operating systems (including Windows) recognize the letters 'd' and 'D' as valid exponent notations. For example, the string "1d4" can be converted to a valid number (1x10^4, or 10,000).
A valid number is composed of:
an optional sign (+ or -)
one or more decimal digits
an optional exponent consisting of: the letter 'd', 'D', 'e', 'E'.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following expression examines the data in the source ("Account No") field and determines whether or not it can be converted to a numeric data type:
IsNumeric(FieldAt("/SOURCE/R1/Account No"))
Join Function
 
Description
Concatenates the elements of an array together into a single string
Return
String consisting of all of the elements of an array
Return (Null)
Error: Too few arguments for function
Syntax
Join(sourceArray[,delimiter])
Parameters
This function has the following parameters:
sourceArray (required) - One-dimensional array containing substrings to be joined.
delimiter (optional) - String character used to separate the substrings in the returned string. If a string literal, enclose in quotation marks.
Remarks
If delimiter is omitted, the space character (" ") is used. If delimiter is a zero-length string (""), all items in the list are concatenated with no delimiters.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
In the following examples, MyArray holds the names "John Smith", "Joe Jones", and "Dave Davis":
Join(MyArray)
Returns: John Smith Joe Jones Dave Davis
In the second example, commas are added to separate the names:
Join(MyArray,",")
Returns: John Smith,Joe Jones,Dave Davis
LBound Function
 
Description
Find the index of the first element (lower bound) in an array or an array dimension
Return
Lower bound of an array
Return (Null)
Error: Invalid expression syntax.
Syntax
LBound(arrayName[,dimension])
Parameters
This function has the following parameters:
arrayName (required) - The array variable name.
dimension (optional) - Is a whole number indicating which dimension's lower bound is returned.
Use 1 for the first dimension, 2 for the second, and 3 for the third.
If dimension is omitted, 1 is assumed.
Remarks
Each element of an array stores one value and is referenced by using its index (coordinate position) after the array name. The index of the first element of an array is called its lower bound. The index of the last element is called its upper bound. (See UBound Function. ) By default, an array is indexed beginning with zero. The upper and lower bounds of an array can be inspected using LBound and UBound.
Scalar and Undeclared Variables: Using scalar variables and/or undeclared variables results in a runtime error. Always declare all variables before using LBound.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example
Dim MyArray(1 To 100, 0 To 10, -5 To 5)
LBound(MyArray) 'returns 1
LBound(MyArray, 1) 'returns 1
LBound(MyArray, 2) 'returns 0
LBound(MyArray, 3) 'returns -5
LCase Function
 
Description
Converts a string argument to lowercase.
Return
String in which all letters of an argument have been converted to lowercase
Return (Null)
Error: Incorrect argument count
Syntax
LCase(strexpr)
Parameters
This function has the following parameters:
strexpr - Any string expression or source field. If a string literal, enclose in quotation marks.
Remarks
LCase returns a string
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
This expression causes data in source "Field1" the to be returned to the target as all lowercase:
LCase(FieldAt("/SOURCE/R1/Field1"))
This example cleans the source "Field1" by making a standard case requirement in specific parts of addresses, such as "St" and "Ste". Any periods used in abbreviation are removed when data is returned to the target. Note the use of the Add1Part and InitCaps functions in the expression:
Add1Part("NU", FieldAt("/SOURCE/R1/Field1"))&" "& Add1Part("PRD", FieldAt("/SOURCE/R1/Field1")) &" "& InitCaps(LCase(Add1Part("SN", FieldAt("/SOURCE/R1/Field1")))) & " "& InitCaps(LCase(Add1Part("SU", FieldAt("/SOURCE/R1/Field1")))) &" "& Add1Part("POD", FieldAt("/SOURCE/R1/Field1")) &" "& InitCaps(LCase(Add1Part("SAU", FieldAt("/SOURCE/R1/Field1")))) &" "& Add1Part("SAR", FieldAt("/SOURCE/R1/Field1")) &" "& Add1Part("REM", FieldAt("/SOURCE/R1/Field1"))
Left Function
Description
Returns the left part of a string.
Return
Leftmost n characters of a string argument
Return (Null)
Error: Incorrect argument count
Syntax
Left(string, length)
Parameters
This function has the following parameters:
string - Any string expression. If a string literal, enclose in quotation marks.
length - A Long expression indicating how many characters to return. It must be between 0 and approximately 65,500, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the number of characters in string, the entire string is returned.
Remarks
Left returns a String.
To find the number of characters in string, use Len(string).
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example returns the three leftmost characters of the source field named ("Field1"):
 
Left(FieldAt("/SOURCE/R1/Field1"), 3) OR Left(FieldAt("/SOURCE/R1/Field1"), 3)
If field1 contains "Richard"
Returns: Ric
 
This example returns the five leftmost characters of the source field named ("Field1"):
Left("Dr. Jane Doe II", 5) OR Left$("Dr. Jane Doe II", 5)
Returns: Dr. J
LeftB Function
 
Description
Reads and returns the leftmost bytes of a string
Return
Leftmost bytes of a string
Return (Null)
Error: Incorrect argument count
Syntax
LeftB(string, length)
Parameters
This function has the following parameters:
string (required) - Any string. If a string literal, enclose in quotation marks.
length (required) - The length of bytes to return. It must be between 0 and 255, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the bytes in string, the entire string is returned.
Remarks
LeftB returns a String.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example returns the three leftmost bytes of the source field "Address1":
LeftB(FieldAt("/SOURCE/R1/Address",) 3)
Address field contains "123 Main Street, New York, NY 10030"
Returns: 123
Len Function
 
Description
Determines the length of a string in characters.
Return
Number of characters in a string expression
Return (Null)
Error: Incorrect argument count
Syntax
Len(strexpr)
Parameters
This function has the following parameters:
strexpr - Any string expression. If a string literal, enclose in quotation marks.
Remarks
Len returns the number of characters in the argument strexpr.
Because Len works with user-defined data types, it is particularly useful for determining the record length when you are performing file input/output with random-access files. However, if a user-defined data type contains variable-length string elements, Len may not be able to properly determine the actual number of storage bytes required.
If the argument to Len is null, the function returns a null.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example returns the number of characters in a string expression:
Len(FieldAt("/SOURCE/R1/Field1"))
If Field1 contains value "Richard", then it returns 7.
If Field1 contains value "Dr. James", then it returns 9.
LenB Function
 
Description
Determines the number of bytes required to store a variable
Return
Number of bytes required to store a variable
Return (Null)
Error: Incorrect argument count
Syntax
LenB(string)
Parameters
This function has the following parameters:
string - A string expression. If a string literal, enclose in quotation marks.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Example 1
This example shows how LenB can be used to return the number of bytes needed to store the data in Field1:
LenB(FieldAt("/SOURCE/R1/Field1"))
If Field1 contains string "Richard"
Return: 7
 
Example 2
LenB("Rose")
Return: 4
Log10 Function
 
Description
Finds the base 10 logarithm of a number
Return
Base 10 logarithm of a number
Return (Null)
Error: Incorrect argument count
Syntax
Log10(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression greater than zero.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example returns the base 10 logarithm of numbers in the source "Field1" to the target:
Log10(FieldAt("/SOURCE/R1/Field1"))
Applied to a field containing:
2147483647
Returns: 9
Log Function
 
Description
Determine the natural logarithm of a number
Return
Natural logarithm of a number.
Return (Null)
Incorrect argument count for function
Syntax
Log(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression greater than zero.
Remarks
The natural logarithm is the logarithm to the base e. The constant e is approximately 2.718282.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example returns the natural logarithm of numbers in the source "Field1" to the target:
Log(FieldAt("/SOURCE/R1/Field1"))
LogMessage Function
 
Description
Creates an entry in the error and event log file while a transformation or batch is running
Return
Not applicable for this function
Return (Null)
Error: Incorrect argument count
Syntax
LogMessage(ErrString, MsgString)
Parameters
This function has the following parameters:
ErrString (required) - "ERROR", "WARN", "INFO", and "DEBUG" keyword labels for the various LogMessage types.
An integer value representing the log level appears in the log file. Enclose in quotation marks if it is a string literal.
MsgString (required) - Text of the message that appears in the log. Enclose in quotation marks if it is a string literal.
Remarks
The maximum length of a message that can be returned by LogMessage is 2048 bytes.
You must select the types of messages you would like to write to the transaction log file. Log messages generated by the LogMessage function are displayed only if the corresponding error type is selected.
Example
If FieldAt("/SOURCE/R1/Account No") > 10019 Then LogMessage("ERROR", "Account" & FieldAt("/SOURCE/R1/Account No") & " number exceeds 10019") End If
The above expression produces messages like the following in the error and event log:
09/03/2003 12:31:45 Account 10030 number exceeds 10019
09/03/2003 12:31:45 Account 10031 number exceeds 10019
09/03/2003 12:31:45 Account 10032 number exceeds 10019
Lookup Function
 
Description
Lookup a value in an external table.
Return
Values from a two-column file.
Return (Null)
Error: Too few arguments for function.
Syntax
Lookup("value", "file", "sep"
[, direction] [, default] [, encoding])
Parameters
This function has the following parameters:
key - The key to search for.
table - The name of the file containing the lookup table.
separator - The character separating the two columns of data; such as "," (comma) or "|" (pipe). It must be a unique character that does not exist in the data.
keyColumn- Specifies whether the key column is the left column (0), or the right column (1). The Default is 0.
default - Optional. If no default is given, the expression returns an empty string if the input value or field is not found in the lookup file. If a default is given, the string specified as the default is returned if the input value or field is not found on the lookup file.
Note:  If you want to specify a default, you must also specify the direction. This is because Lookup uses the first optional argument it finds as the direction.
encoding - Optional. Enables you to specify the character encoding to be used when reading the lookup file. For example, ENC_CP437. For a list of all encodings, see Encoding Reference
Note:  If you want to specify encoding, you must also set the direction and a default. This is because Lookup uses the first optional argument it finds as the direction and the second as the default.
Remarks
Tip...   The following section gives details on manually creating lookup files that can be accessed using the Lookup function. It is no longer necessary to manually create lookup files. Now you can use Lookup Wizards to automatically create lookup files and their corresponding functions. For step-by-step instructions, search for the phrase “lookup wizard” in the documentation.
To manually create lookup files: Create and save a lookup file in a simple text editor. The file must contain two columns of data. Each line in the lookup file must contain a lookup key in one column, a separator (such as a comma or a pipe), and a corresponding value in the second column.
Limitations of lookup files: Maximum line length is 4096 characters; maximum key length is 31 bytes (the field can be longer, but only the first 31 characters are used for indexing). Expect linear performance degradation. The larger the lookup file, the slower the performance. Also, a smaller key is significantly faster.
If you use the example syntax, Map Designer looks up the value in the lookup file and writes the corresponding value for that one key in every record in that field in the target data file..
The LookupIndexDir file setting is used as the directory to store lookup function index files. If not set, the default is still the InstallDir/lookup directory. Modify this file setting in the UserInfo section of the cosmos.ini file.
If the index directory cannot be created, or is not accessible as read/write, and the size of the lookup file is more than 32000 bytes then an error occurs. If the size is less than 32000 the lookup will continue to process without an index.
Note:  The use of duplicate keys is not permitted because they can result in index corruption that causes the lookup to behave incorrectly.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow
See Also
Example
In this example, Map Designer looks for any key in the file lookup.txt, and looks for any found key in FIELD1 of the source file. Then Map Designer writes the corresponding values in the current field for every record in the target file. The separator between the value columns in the lookup file is a pipe ( | ):
Lookup(Trim(Fields("FIELD1")), "lookup.txt","|")
Trim is added to this expression to eliminate the possibility of extra spaces causing the lookup to fail. Use of Trim is recommended.
Lookup Function Instructions
This topic gives details on manually creating lookup files. However, you can use wizards to automatically create lookup files and their corresponding functions. For step-by-step instructions, search for “dynamic SQL lookups” in the documentation.
Manually Creating Lookup Files
The Lookup function resembles the GSub and Sub functions. When you use the Lookup function, you (1) search for specific data in a source file field and (2) replace the source file field data with values from an external target file.
You must first create a lookup file. This file consists of two columns of data. Each line in the file must contain a lookup key, a separator such as a comma or a pipe, and a corresponding value.
Suggestion: Create your lookup file in a text editor. Save the file in the default installation directory under the LookupTables subdirectory. As in the following example, you can also create a test directory called MyData:
C:\MyData\LOOKUP.TXT:
Mon|Monday
Tue|Tuesday
Wed|Wednesday
Thu|Thursday
Fri|Friday
Sat|Saturday
Sun|Sunday
The separator between the two columns is a pipe (|). Press Enter after typing each line.
There are four ways to do a lookup. The syntax for each is as follows:
Lookup("string", "file", "separator")
where
"string" is a value from the first column in the file (key).
"file" is the name of the external file.
"separator" is the separator between the columns of data in the lookup file.
Map Designer writes the corresponding data from the second column in every record in your target data file.
Target Field Name
Target Field Expression
FIELD1
=Lookup(Fields("FIEL1D"), "lookup.txt", "|")
The above example instructs Map Designer to write "Monday" in FIELD1 in every record in your target data file.
Lookup(Fields("FIELD1"), "file", "separator")
Where ("FIELD1") is the field name in the source data file in which Map Designer looks up values. Where "file" is the name of the external file. Where "separator" is the character that separates the columns of data in the lookup file. When values from the key column are found, Map Designer will write the corresponding value from the right column of the lookup file into the target data file.
Target Field Name
Target Field Expression
FIELD1
=Lookup(Fields("FIEL1D"), "lookup.txt", "|")
This example instructs Map Designer to write "Monday" in FIELD1 of every record in your target data file where it finds "Mon" in FIELD1 of the source data file, and "Tuesday" in FIELD1 of every record in your target data file where it finds "Tue" in FIELD1 of the source data file, and so on.
Lookup("string", "file", "separator" [, reverse])
or
Lookup(Fields("FIELD1"), "file", "separator" [, reverse])
These work basically the same, but 'reverse' can instruct Map Designer to either look for the value in the first or second column in your external file, and replace it with the corresponding data. If 'reverse' is 0 (zero), Map Designer looks for the value in the first column and replace it with the value in the second column. If 'reverse' is not 0 (zero), Map Designer looks for the value in the second column and replaces it with the value in the first column (reverse order). This syntax gives you the opportunity to use the same external lookup file in multiple transformations.
Lookup File Notes
The following notes refer to lookup functions, but not to incore lookup functions.
Max Key Len = 31 characters (the Key field (first column) can be longer than 31 chars, but Map Designer will just use the first 31 for key/indexing).
Must have only 2 columns (left and right).
No limit on number of rows (except performance - expect linear performance degradation; that is, bigger is slower). A lot depends on machine size/speed, and KeyLen (that is, a smaller key is faster).
After making changes to a lookup file, call ResetLookup to enable lookup functions to use new values.
IMPORTANT!  All lookup functions in an integration should use the exact same path, including capitalization and direction of slashes. Best practice is to use a macro in all cases so that the path is entered only once and therefore always identical.
See Also
For more information about lookups on live SQL files, search for the phrase “dynamics SQL lookups” in the documentation:
ResetLookup Function
GSub Function
Sub Function
LTrim Function
 
Description
Trims leading spaces from a string.
Return
Specified string with leading (leftmost) spaces removed
Return (Null)
Error: Incorrect argument count
Syntax
LTrim(stringexpr)
Parameters
This function has the following parameters:
stringexpr - Any expression containing numeric or text characters. If a string literal, enclose in quotation marks.
Remarks
LTrim removes leading spaces from a string.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example removes the leading spaces from the string
"  TestString":
LTrim("  TestString")
Result: "TestString"
MacroExpand Function
 
Description
Expands macros embedded in a character string
Return
Expanded value of the specified macro
Return (Null)
Incorrect argument count for function
Syntax
MacroExpand(string)
Parameters
This function has the following parameters:
string - Any expression containing characters. If a string literal, enclose in quotation marks.
Remarks
This function takes a character string and returns a string with all macro occurrences expanded.
MacroExpand returns an empty string for the value of an encrypted macro. Encrypted macros are only resolved by the integration engine at run time.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
For the following example, a macro named fileLocation contains the name of a folder (C:\MyFolder).
Dim myString,fullString
myString = "The file is located at $(fileLocation)."
fullString = MacroExpand(myString)
The value of fullString is set to:
The file is located at C:\MyFolder.
MacroValue Function
 
Description
Looks up the value of a macro
Return
Defined value of a specified macro
Return (Null)
Error: Too few arguments for function
Syntax
MacroValue(macroName [,default])
Parameters
This function has the following parameters:
macroName (required) - String containing the name of the macro. If not defined, then default parameter is returned. If a string literal, enclose in quotation marks.
default (optional) - Default name of macro. Returned if no macroName parameter is specified (must be provided to be returned). If no default is provided, an empty string is returned.
Remarks
MacroValue returns an empty string for the value of an encrypted macro. Encrypted macros are only resolved by the integration engine at run time.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
In this example, the defined macro called "mymacro" represents the path to the source file. It returns the following path: \myprocess\mysources\sourcefilename.asc:
MacroValue("mymacro")
MakeDirectory Function
 
Description
Creates a new directory
Return
Not applicable for this function
Return (Null)
Error: Incorrect argument count
Syntax
MakeDirectory(dirName)
Parameters
This function has the following parameters:
dirName - A string containing the name of the directory to create. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
If dirName contains a path, all directories up to the parent of the new child directory must be included in the path and must exist.
An error is returned if a directory cannot be created. This is usually because user permissions do not allow changes, a parent directory does not exist, or the directory already exists.
See Also
Example
This expression creates a directory named xmlfiles:
MakeDirectory("C:\xmlfiles")
Mid Function
 
Description
Returns a string that is part of some other string.
Return
Specified portion of a string
Return (Null)
Error: Too few arguments for function
Syntax
Mid(string, start, length)
Parameters
This function has the following parameters:
string (required) - String expression from which another string is created. This can be any string expression. If a string literal, enclose in quotation marks.
start (required) - Long expression that indicates the character position in string at which the part to be taken begins.
length (optional) - Long expression that indicates the number of characters to return.
Remarks
The parameters start and length must be between 1 and approximately 65,500, inclusive. If length is omitted or if there are fewer than length characters in the text (including the character at start), the Mid function returns all characters from the start position to the end of the string.
If start is greater than the number of characters in string, then Mid returns a zero-length string.
Use the Len function to find the number of characters in string.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Example 1:
The following function returns a part of the string:
Mid("Hello", 3, 2)
Returned Value: ll
 
Example 2:
If the field contains value 20201207 date format string. This functions converts the date into a 12/07/2020 date string:
Mid(FieldAt("/SOURCE/R1/Field1"), 5, 2)
Returned Value: 12
Mid(FieldAt("/SOURCE/R1/Field1"), 7, 2)
Returned Value: 07
Mid(FieldAt("/SOURCE/R1/Field1"), 1, 4)
Returned Value: 2020
 
The preceding expressions are appended with a "/" in between the returned value.
Mid(FieldAt("/SOURCE/R1/Field1"), 5, 2) & "/" & Mid(FieldAt("/SOURCE/R1/Field1"), 7, 2) & "/" & Mid(FieldAt("/SOURCE/R1/Field1"), 1, 4)
Returned value: 12/07/2020
MidB Function
 
Description
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify.
Return
Returns part of a string, specified in bytes.
Return (Null)
Error: Too few arguments for function
Syntax
MidB(string, start, length)
Parameters
This function has the following parameters:
string- A string expression.
start - The starting byte position of the substring to return.
length - The number of bytes to return.
Remarks
If start is greater than the number of characters in string, then MidB returns a zero-length string.
If start is less than the length of text, but length exceeds the length of text, then MidB returns the characters up to the end of text.
See Also
Example
The following examples returns a part of the string:
 
Example 1:
MidB("Hello", 3, 2)
It returns two characters from the third position of the string.
Returned Value: ll
 
Example 2:
MidB("$3,000.00",2,1)
It returns one character from the second position of the string.
Returned Value: 3
 
Minute Function
 
Description
Convert clock time to a minute integer
Return
Integer between 0 and 59, inclusive, which represents the minute of the hour corresponding to the time provided as an argument
Return (Null)
Error: Incorrect argument count
Syntax
Minute(dateString) - if containing time value.
Or
Minute(TimeString)
Parameters
This function has the following parameters:
dateString - If containing time value.
Or
TimeString - Any value or expression that can represent a date/time.
Remarks
Numbers to the left of the decimal point in number represent the date; numbers to the right represent time. Negative numbers represent dates prior to December 30, 1899.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The example below returns the minute portion of the current system time (if system time is 1:12:30, "12" is returned to the target):
Minute(Now())
The example below returns the minute portion from the source ("Minute"). The TimeValue function defines each time to be time values, while the Minute function defines those values as minutes:
a = TimeValue(Trim(FieldAt("/SOURCE/R1/Minute")))
Minute(a)
Month Function
 
Description
Find the month integer of a date
Return
Integer between 1 and 12, inclusive, which represents the month of the year for a date argument
Return (Null)
Error: Too few arguments for function
Syntax
Month(dateString)
Parameters
This function has the following parameters:
dateString - Any value or expression that can represent a date.
Remarks
Numbers to the left of the decimal point in number represent the date; numbers to the right represent the time. Negative numbers represent dates prior to December 30, 1899.
If number is null, this function returns a null.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example will examine the data in the source field called ("ShipDate"), which contains a valid date, and return the two-digit month designation to the target field:
Month(FieldAt("/SOURCE/R1/ShipDate"))
The example below returns the month portion from dates in the source ("ShipDate"). The DateValue function defines each date as date values, while the Month function defines those values as months:
a = DateValue(Trim(FieldAt("/SOURCE/R1/ShipDate")))
Month(a)
MsgBox Function
 
Description
Displays a message in a dialog box, and waits for the user to choose a button.
Return
Value indicating which button the user has chosen.
Return (Null)
Error: Incorrect argument count
Syntax
MsgBox(Msg, [Style], [Title])
Parameters
This function has the following parameters:
msg - String expression displayed as a message in the dialog box. If a string literal, enclose in quotation marks. MsgBox(“msg”).
style - (Optional) Style is a numeric expression that is the sum of values indicating the number and style of buttons to display, the style of icon to use, the identity of the default button, and the modality. The following table lists the values you may use:
0: Display OK button only.
1: Display OK and Cancel buttons.
2: Display Abort, Retry, and Ignore buttons.
3: Display Yes, No, and Cancel buttons.
4: Display Yes and No buttons.input
5: Display Retry and Cancel buttons.
16: Display a stop sign icon.
32: Display a question mark icon.
48: Display an exclamation icon.
64: Display an information icon.
0: First button is default.
256: Second button is default.
512: Third button is default.
0: Application modal. The user must respond to the message box before continuing work in the current application.
4096: System modal. All applications are suspended until the user responds to the message box.
 
The first group of values (1 - 5) describes the number and type of buttons displayed in the dialog box.
The second group (16, 32, 48, 64) describes the icon style.
The third group (0, 256, 512) determines the default button.
The fourth group (0, 4096) determines the modality of the message box.
When adding numbers to create a final value for the argument type, use only one number from each group. If type is excluded, the default value for type is 0 (zero).
title
The string expression displayed in the title bar of the dialog box. If you exclude the title, the title bar displays RIFL Script MsgBox as the default title.
For application modal message boxes, MsgBox displays a maximum of 1024 characters. Longer messages are truncated. Message strings longer than 255 characters with no intervening spaces are truncated after the 255th character.
For system modal message boxes, the number of characters that can be displayed depends on the screen resolution and whether or not the string to be displayed is one or more lines.
MsgBox breaks lines automatically at the right edge of the dialog box. If the user wants to set their own line breaks, a linefeed (ANSI 10) must be placed before the first character of the text that begins on each new line.
The value returned by the MsgBox function indicates the selected button:
1: OK button was selected.
2: Cancel button was selected.
3: Abort button was selected.
4: Retry button was selected.
5: Ignore button was selected.
6: Yes button was selected.
7: No button was selected.
If the dialog box displays a Cancel button, pressing the Esc key has the same effect as choosing Cancel.
Remarks
This function has three parameters, msg, type and title.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example 1
‘Causes a message box with a Stop sign to pop up for every record that is being converted. The message box displays "This is the Account Number", and the actual account number as it is stored in the Account Number field in the source data file:
MsgBox("This is the Account Number: " & Fields("Account No"),16, "Account Number")
Example 2
Display a YES/NO message box asking the user whether to continue:
If MsgBox("Do you want to continue?", 4, "Validation Error") = 7 Then
The user clicks the No button; stop the transformation:
Abort(-1)
Else
The user clicks the Yes button; discard the record and continue the transformation:
Discard(-1)
End If