User Guide : Scripting : Script Statements : Flow Control Statements
 
Share this page             
Flow Control Statements
Flow control is a term that describes more powerful and efficient ways to write expressions. Conditional mapping and data modification are the primary reasons for writing expressions.
To enter a flow control statement, double-click the desired statement from Script Editor. For details on each statement, select from the following list:
Example of Using a Flow Control Statement
These examples show how to convert a phone number to a (aaa) eee-nnnn format.
Expression 2 is much easier to write and read, and also processes more quickly and efficiently. Expression 1 is not recommended, since the map must evaluate the entire IIf statement before processing, while in If Then Else or Select statements, evaluation can occur separately within each condition. For this reason, Expression 1 takes much longer to process than Expression 2.
Expression 1:
IIf(Trim(FieldAt("/SOURCE/R1/WorkPhone") ) <> "(",
IIf(Trim(FieldAt("/SOURCE/R1/WorkPhone") ) <> " ", "(" &

Left$(FieldAt("/SOURCE/R1/WorkPhone"), 3) & ")" &

Mid$(FieldAt("/SOURCE/R1/WorkPhone"), 4, 3) & "-" &

Right$(FieldAt("/SOURCE/R1/WorkPhone"), 4) , FieldAt("/SOURCE/R1/WorkPhone") ), "" )
Expression 2:
Select Case Left$(Trim(FieldAt("/SOURCE/R1/Field1")),1)
  Case "("
FieldAt("/SOURCE/R1/Field1")
  Case ""
    FieldAt("/SOURCE/R1/Field1")
  Case Else
    "(" & Left$(FieldAt("/SOURCE/R1/Field1"), 3) & ")" &
    Mid$(FieldAt("/SOURCE/R1/Field1"), 4, 3) & "-" &
    Right$(FieldAt("/SOURCE/R1/Field1"), 4)
End Select
ElseIf Statement
 
Description
This variation of the If...Then...Else statement allows you to choose from several alternatives. Adding clauses expands the functionality of the If...Then...Else statement, so you can control program flow based on different possibilities.
Syntax
If condition1 Then
[statementblock1]
[ElseIf condition2 Then
[statementblock2] ]
[Else
[statementblockn] ]
End If
Remarks
You can have as many ElseIf statements as you want in a block If, but none can appear after the Else clause. Block If statements can be nested; that is, contained within one another.
Another way to choose between several alternatives is the Select Case statement.
Example
If(FieldAt("/SOURCE/R1/PRD_NO")) < 3000 Then
  FieldAt("/SOURCE/R1/PRD_NO")
ElseIf FieldAt("/SOURCE/R1/PRD_NO") > 5000 Then
  FieldAt("/SOURCE/R1/PRD_NO3")
Else
  FieldAt("/SOURCE/R1/PRD_NO2")
End If
For...Next Statement
 
Description
Repeat a group of instructions a specified number of times
Syntax
For counter = start To end [ Step increment ]
[statementblock]
[Exit For]
[statementblock]
Next [counter [, counter]]
Arguments
counter - Numeric variable used as the loop counter. The variable cannot be an array element or a record element.
start - Initial value of the counter.
end - Final value of the counter.
increment - The amount the counter is changed each time through the loop. If you do not specify Step, increment defaults to 1.
statement block - Any number of statements or methods to be executed a specified number of times.
Remarks
The Step value controls loop execution is as follows:
When Step is Positive or 0, the Loop executes if counter <= end
When Step is Negative, the Loop executes if counter >= end
Once the loop has been entered and all the statements in the loop have executed, Step is added to counter. At this point, either the statements in the loop execute again (based on the same test that caused the loop to execute in the first place), or the loop is exited and execution continues with the statement following the Next statement.
Caution!  Changing the value of counter while inside a loop can make the program more difficult to read and debug.
Example
You can nest For...Next loops by placing one For...Next loop within another. Give each loop a unique variable name as its counter. The following construction is correct:
For I = 1 To 10
For J = 1 To 10
For K = 1 To 10
...
Next K
Next J
Next I
Another way to construct the Next statements is as follows:
For I = 1 To 10
For J = 1 To 10
For K = 1 To 10
...
Next K,J,I
Note:  If you omit the variable in a Next statement, the value of Step increment is added to the variable associated with the most recent For statement. If a Next statement is encountered before its corresponding For statement, an error occurs.
GoTo Statement
 
Description
Cause execution to branch to a label.
Syntax
GoTo label
Remarks
A label must be of have alphanumeric characters, begin in column 1 and end with a colon.
Do not use the GoTo statement to jump into or out of Select statements or For loops.
If a GoTo statement tries to jump into a Select statement or a For loop, then a compile-time undefined label error is generated.
If a GoTo statement tries to jump out of a Select statement or a For loop, then an error “attempt to jump out of XXX” is displayed, where 'XXX' describes the loop/block type.
Example
This statement causes run-time execution to jump to the label "here":
GoTo here
For Each...Next Statement
 
Description
Repeat a group of statements for each element in an array or collection.
This statement differs from the For . . . Next statement, in that you iterate over the contents of a collection of objects without the need to provide an index. Some ActiveX collections do not provide a way to use indexes, so this statement is the only way to use the contents of a collection.
Syntax
For Each index In container
[statementblock]
Next [index]
Arguments
Index (required) - Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a general object variable, or any specific object variable. For arrays, element can only be a Variant variable.
Container (required) - Name of an object collection or array (except an array of user-defined types).
statementblock (optional) - Any number of statements or methods to be executed a specified number of times that are executed on each item in container.
Next [index] (optional) - Name of the index. If you omit index in a Next statement, execution continues as if an index is included. If a Next statement is encountered before its corresponding For statement, an error occurs.
Remarks
The For . . . Each block is entered if there is at least one element in container. Once the loop has been entered, all the statements in the loop are executed for the first element in container. If there are more elements in container, the statements in the loop continue to execute for each element. When there are no more elements in container, the loop is exited and execution continues with the statement following the Next statement.
If the container is an object, it must resolve to an OLE container object. If it is an OLE container, and the container contains objects, the index variable must be DIMmed as an object also. For each element in the container (array, enumerator for the OLE container), the index variable is set to that value and the statements executed.
You cannot use the For . . .Each . . . Next statement with an array of user-defined types because a Variant cannot contain a user-defined type.
Any number of Exit For statements may be placed anywhere in the loop as an alternative way to exit. Exit For is often used after evaluating some condition. For example, an If …Then statement would transfer control to the statement immediately following Next.
Example
This example uses the For Each...Next statement to display the value of each element in a collection. Here, k is an element of the collection named MyArr.
'Set up the array of numbers
Dim MyArr(4)
Dim j,k
For j = 0 to 4
  MyArr(j) = j
Next j
'Cycle through each element in the array
For each k in MyArr
  LogMessage("Info",k)
Next
Tip...  You can nest For… Each … Next loops by placing one For ... Each ... Next loop within another. However, each loop statement must be unique.
If...Then...Else Statement
The If...Then...Else statement allows conditional execution based on the evaluation of an expression.
There are two ways to write an If...Then...Else statement:
Single-Line
Block
Single-Line
 
Description
The single-line form of If...Then...Else is often useful for short, simple conditional tests.
Syntax
If condition Then thenpart [Else elsepart]
Parts:
condition - A numeric or string expression that evaluates true (nonzero) or false (0 or Null).
thenpart, elsepart - Statements or branches performed when condition is True (thenpart) or False (elsepart).
The thenpart is executed if condition is true; if condition is false, then elsepart is executed. If the Else clause is not present, control passes to the next statement in the program.
You can have multiple statements within a condition, but they must be on the same line and separated by colons.
Example
If A > 10 Then A = A + 1 : B = B + A : C = C + B Else A = A + 2
Block
 
Description
The block form of If...Then...Else provides more structure and flexibility than the single-line form and is usually easier to read, maintain, and debug.
Syntax
If condition1 Then
[statementblock1]
[ElseIf condition2 Then
[statementblock2] ]
[Else
[statementblock3] ]
End If
Parts:
condition1, condition2 - The same conditions as used in the single-line form.
statementblock1 to 3 - One or more statements on one or more lines.
Remarks
In executing a block If, the map tests condition1, the first numeric expression. If the expression is true, the statements following Then are executed. If the first expression is false, the map begins evaluating each ElseIf condition in turn. When the map finds a true condition, the statements following the corresponding Then are executed. If none of the ElseIf conditions is true, the statements following Else are executed. After executing the statements following Then or Else, the program continues with the statement following End If.
The Else and ElseIf blocks are both optional. You can have as many ElseIf clauses as you like in a block If, but none can appear after an Else clause. Any of the statement blocks can contain nested block If statements.
The map looks at what appears after the Then reserved word to determine whether or not an If statement is a block If. If anything other than a comment appears after Then, the statement is treated as a single-line If statement.
A block If statement must be the first statement on a line. The Else, ElseIf, and End If parts of the statement can have only a line number or line label in front of them. The block must end with an End If statement.
Use the If… Then… Else statement to define two blocks of statements. One of the statements runs when the specified condition is True, and the other one runs when the condition is False. When you want to define more than two blocks of statements, use the ElseIf Statement.
You can nest up to ten levels of If... Then... Else statements. If you need to create an expression with more than ten levels, you must redefine it using the ElseIf statement or the Select Case...End Case Statement.
Example
This example tests the length of a field after spaces have been removed to determine if the field contains data. If the source field contains no data, a string is placed in the target field that says No Data Available. If there is data in the source field, the data is placed in the target field.
If Len(Trim(FieldAt("/SOURCE/R1/Field1")))=0 Then
  "No Data Available"
Else
  FieldAt("/SOURCE/R1/Field1")
End If
Option Evaluate Automatic Statement
 
Description
Execute a target field expression and determines its value (default behavior)
Syntax
Option Evaluate Automatic
Remarks
Option Evaluate Automatic is the default behavior, but if you use the Option Evaluate Manual statement and then want to return to default behavior, you must include the Option Evaluate Automatic statement.
Example
The Option Evaluate statements work in ANY expression, be it filter expressions or field expressions. They may also be used multiple times within the same expression:
Option Evaluate Manual
old_val = Targets(0).fields("Field1")
Option Evaluate Automatic
new_val = Targets(0).fields("Field1")
printf("The old value is " & old_val & ", the new value is " & new_val)
Option Evaluate Manual Statement
 
Description
Evaluate the value within a target field memory buffer
Syntax
Option Evaluate Manual
Remarks
If you reference a target field name in an expression, by default, a map will execute the target field expression for that field to determine the value. When Option Evaluate Manual is used, the target field expression is not executed. A map instead references the last value evaluated in the memory buffer for that target field. This is especially useful when manually mapping (using a variable in Event actions to map your target data).
Example
The Option Evaluate statements work in ANY expression, be it filter expressions or field expressions. They may also be used multiple times within the same expression:
Option Evaluate Manual
old_val = Targets(0).fields("Field1")
Option Evaluate Automatic
new_val = Targets(0).fields("Field1")
printf("The old value is " & old_val & ", the new value is " & new_val)
Note:  Option Evaluate Automatic is the default behavior. If you use Option Evaluate Manual and you want to return to default behavior, you must include the Option Evaluate Automatic statement.
Option Explicit Statement
 
Description
Require variables be declared before they are first used
Syntax
Option Explicit
Remarks
The Option Explicit statement tells the expression language to require the use of a Dim, Public, or Private statement. It is usually the first statement in an expression, so it is used more as a policy setting rather than a property that is turned on and off. The default is Option Implicit.
The Option Implicit statement, which does not require that variables be declared before they are first used, is the default behavior.
Option Explicit applies to the entire "expression context", excluding Process steps and Transformation steps. Expression context is defined as either the entire conversion, or all expressions in a context.
If you use variables in functions elsewhere (such as target field expression or BeforeTransformation event) you must use the Option Explicit statement in your expressions each time.
Example
This statement, which includes Option Explicit, generates an error at run time, since the declared variable name is misspelled. If the Option Explicit function is not used, the error is simply written as an error message in the log. The compiler does not error out, since it automatically defines the misspelled variable name for you. It is, therefore, useful to always use Option Explicit when declaring variable names in a transformation.
Option Explicit
Dim srvr_name
srvr_name$ = "NTServer"
Print ("Using server " & svr_name)
'^^^^^^^
'undefined variable name
Best Practice — Declare Option Explicit statements at the beginning of your script and then call the script file in the expression. This file may then be shared between modules in multiple transformations. It is more efficient to declare Private variables once in a script file, rather than declaring them in each transformation script.
Option FieldExplicit Statement
 
Description
Causes the script to fail with a runtime error if referencing a field (or record) that does not exist.
Syntax
Option FieldExplicit
Remarks
This can be set in the BeforeTransformation event, and applies to all code modules. If it is to be used in a single code module, it is usually the very first statement in that module.
When writing user-defined functions, Option FieldExplicit is the default behavior.
To treat missing elements as Nulls, use the Option FieldImplicit Statement.
Option FieldImplicit Statement
 
Description
Cause a null value is pushed on the stack rather than a field (or record) reference if referencing a field (or record) that does not exist.
Syntax
Option FieldImplicit
Remarks
This can be set in the BeforeTransformation event, and applies to all code modules. If it is to be used in a single code module, it is usually the very first statement in that module. When writing user-defined functions, Option FieldExplicit Statement is the default behavior.
Option Implicit Statement
 
Description
Turn off the requirement that variables be declared before they are first used
Syntax
Option Implicit
Remarks
This is usually the very first statement in a code module, so it is used more as a policy setting rather than a property that gets turned on and off.
When writing user-defined functions, keep in mind that Option Implicit is the default behavior. Use of Option Implicit's opposite, the Option Explicit statement, is recommended, since Option Explicit requires that variables be declared before they are used.
Example
This program simply results in a bug in the code. The interpreter does not complain, since it would just automatically define the misspelled variable name for you.
Option Implicit
Dim srvr_name
srvr_name$ = "NTServer"
Print ("Using server " & svr_name)
                        '^^^^^^^
                        'undefined variable name
Select Case...End Case Statement
 
Description
Conditionally execute one of several statement blocks, depending on the value of an expression
Syntax
Select Case testexpression
[Case expressionlist1
[statementblock1] ]
[Case expressionlist2
[statementblock2] ]
...
[Case Else
[statementblockn] ]
End Select
Arguments
textexpression - Any numeric or string expression
statementblock - The elements statementblock1 to statementblockn consist of any number of statements on one or more lines.
expressionlist - These elements can take the following forms:
expression [, expression]
expression To expression
Is comparison-operator expression
expression - Any numeric or string expression. The type of the expression must be compatible with the type of testexpression. (The type of the expression is coerced to the same type as testexpression. For example, if testexpression is an integer, expressionlist can contain a double-precision data type.)
comparison operator - Any valid comparison operator, except the Like operator.
Remarks
If testexpression matches the expressionlist associated with a Case clause, the statement block following that Case clause is executed up to the next Case clause, or for the final one, up to End Select. Control then passes to the statement following End Select.
You can use multiple expressions or ranges in each Case clause.
You also can specify ranges and multiple expressions for character strings.
If testexpression matches more than one Case clause, only the statements following the first match are executed.
Select Case statements can be nested. Each Select Case statement must have a matching End Select statement.
The expression list that follows the Case in a Select Case statement must be a list of constant numeric or string expressions. If you want to use variables for different cases, use the If...Then...Else Statement instead.
Examples
Example 1
'The following expression converts the data in the RepNumber field from
the value contained in the source file to a new value in the target
file:
Select Case FieldAt("/SOURCE/R1/RepNumber")
  Case 44
    "32"
  Case 43
    "33"
  Case 0
    "33"
  Case Else
    "33"
End Select
Example 2
'To use a combination of a set of values and a nested select case:
Select Case FieldAt("/SOURCE/R1/category")
  Case "005","009","004","008","010"
    "P"
  Case "001","003","007","002"
    "R"
  Case "006"
    Select Case FieldAt("/SOURCE/R1/method")
      Case "MX"
        "A"
      Case Else
        "P"
    End Select
  Case Else
    "X"
End Select
 
Example 3
'Look for certain characters:
Select Case MyVariable
  Case "}", ">", ")"
    GSub("[}>)]", "", FieldAt("/SOURCE/R1/Field1"))
  Case Else
    FieldAt("/SOURCE/R1/Field1")
End Select
Example 4
'Look for a value greater than a certain number:
Select Case FieldAt("/SOURCE/R1/Field1")
  Case Is > 3
    FieldAt("/SOURCE/R1/Field1") - 2
  Case Else
    FieldAt("/SOURCE/R1/Field1")
End Select
Example 5
'Choose a value between two numbers:
Select Case 88
  Case Is <= 69
    "D"
  Case 70 To 79
    "C"
  Case 80 To 89
    "B"
  Case Else
    "A"
End Select
While...Wend Statement
 
Description
Execute a series of statements in a loop as long as a given condition is true
Syntax
While condition
[statementblock]
Wend
Remarks
The argument condition is a numeric or string expression that evaluates True (nonzero) or False (0 or Null).
If condition is true, all statements in statementblock are executed until the Wend statement is encountered. Control then returns to the While statement and condition is checked again. If condition is still true, the process is repeated.
If it is not true, execution resumes with the statement following the Wend statement.
While...Wend loops can be nested to any level. Each Wend corresponds to the most recent While.
Example
This example uses While...Wend to print the numbers 1 through 4:
A = 1
While A < 5 'Begin loop.
Print A 'Print A.
A = A + 1 'Increment A.
Wend
Caution!  Do not branch into the body of a While...Wend loop without executing a While statement. Doing so may cause run-time errors or problems that are difficult to locate.