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()) |
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") |
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() |
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")) |
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. |
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) |
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", "") |
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") |
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 |
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") |
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 |
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 |
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")) |
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") |
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) |
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") |
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 |
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 |
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 |
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) |
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) |
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.") |
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 |
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 |
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") |
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 |
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")) |
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 |
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 |
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")) |
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 |
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 |
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. |
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 |
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 |
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")) |
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 |
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. |
Target Field Name | Target Field Expression |
FIELD1 | =Lookup(Fields("FIEL1D"), "lookup.txt", "|") |
Target Field Name | Target Field Expression |
FIELD1 | =Lookup(Fields("FIEL1D"), "lookup.txt", "|") |
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" |
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. |
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") |
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") |
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 |
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 |
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) |
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) |
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 |