Guide to Built-In Functoids

One of the beauties of the Functoid Palette is the long ToolTip description of each icon, which makes it a quick matter to find the one you need for any given transformation. The help for functoids is fairly good as well, although it does lack precise details, such as whether record indices or string positions begin at 1 or at 0, or what value is returned by, for example, the String Find functoid if the string is not found. Help for functoids can be conveniently located in BizTalk Server Documentation, Index, String Functoids, Mathematical Functoids, Logical Functoids, and so on.

A good technique to determine exactly what most functoids do is to drag the functoid from the palette onto the mapping grid, double-click the functoid to open its Properties dialog, and click the Script tab (see Figure 5.22). You will be able to see (but not edit) the actual VBScript or JScript that would be added to the map file to execute the functoid. Reading the script allows you to know for certain the correct order of the input parameters and what values will be returned in cases such as empty input strings or search strings that are not matched.

To give you an introduction and handy reference to all the built-in functoids, here is a list of the palette tabs and the functoids contained on each. Note that all functoids return a single, string-formatted value, even when the functoid value represents a number.

String Functoids

Here are the functoids to perform string manipulations such as concatenation, extraction, and case conversion (see Figure 5.23):

Here is a quick reference for each String functoid:

• String Find (2 Inputs)— Indicates the position at which second input string begins in first input string. First string position is 1. String not found returns 0.

• String Left (2 Inputs)— Extracts from beginning of first input string the number of characters specified by second input. First input shorter than specified number returns entire string. Second input negative number, or not a number, returns empty string.

• Lowercase (1 Input)— Converts all uppercase letters in input string to lowercase.

• String Right (2 Inputs)— Extracts from end of first input string the number of characters specified by second input. First input shorter than specified number returns entire string. Second input negative number, or not a number, returns empty string.

• String Length (1 Input)— Indicates the number of characters in string.

• String Extract (3 Inputs)— Returns portion of first input (string) starting from position specified by second input, up to and including position specified by third input. Empty string returned if either of second or third inputs is nonnumeric, if second input is negative or greater than string length, or if third input is less than second input. If third input is greater than string length, returns string from second input position to end.

• Concatenate (1 or more Inputs)— Concatenates inputs as strings, in the order specified. No upper limit on number of inputs. Note that you will often add constant parameters to this functoid to place spaces or punctuation between input strings. Can be used without a source link by adding at least one constant parameter, allowing this functoid to be a single-source for other functoids of complex strings such as database connection strings.

• String Left Trim (1 Input)— Removes leading spaces.

• String Right Trim (1 Input)— Removes trailing spaces.

• Uppercase (1 Input)— Converts all lowercase letters in input string to uppercase.

Mathematical Functoids

Here are the functoids to perform mathematical operations such as addition, subtraction, maximum value, or minimum value (see Figure 5.24):

Here is a quick reference for each Mathematical functoid:

• Absolute Value (1 Input)— Converts negative number input to positive number of equal magnitude; returns positive input number unchanged.

• Integer (1 Input)— Returns the closest integer less than or equal to number input. Note that this means a negative number input that is not an integer returns the next lower integer value.

• Maximum Value (1 or more Inputs)— Returns maximum value among all inputs.

• Minimum Value (1 or more Inputs)— Returns minimum value among all inputs.

• Modulo (2 Inputs)— Returns remainder after dividing first input by second input. Returns empty string if second input is zero.

• Round (2 Inputs)— Rounds a decimal-fraction number (first input) to a specified number of decimal places (second input). Number of places can be negative to round off to a multiple of 10, 100, and so on.

• Square Root (1 Input)— Returns empty string if number is negative.

• Addition (1 or more Inputs)— Indicates the sum of all inputs.

• Subtraction (1 or more Inputs)— Returns first input minus all subsequent inputs.

• Multiplication (1 or more Inputs)— Returns product of all inputs.

• Division (2 Inputs)— Returns first input divided by second input. Returns empty string if second input is zero.

Logical Functoids

Figure 5.25 shows the functoids that perform actions such as value comparison, type checking, or Boolean operations.

Here is a quick reference for each Logical functoid:

• Greater Than (2 Inputs)— Returns “true” if first input is greater than second input. If either input is nonnumeric, compares as strings.

• Greater Than or Equal To (2 Inputs)— Returns “true” if first input is greater than or equal to second input. If either input is nonnumeric, compares as strings.

• Less Than (2 Inputs)— Returns “true” if first input is less than second input. If either input is nonnumeric, compares as strings.

• Less Than or Equal To (2 Inputs)— Returns “true” if first input is less than or equal to second input. If either input is nonnumeric, compares as strings.

• Equal (2 Inputs)— Returns “true” if first input is equal to second input. If either input is “true” or “false” (not case-sensitive), comparison is made using the equivalent Boolean value. Otherwise, if either input is nonnumeric, compares as strings.

• Not Equal (2 Inputs)— Returns “true” if first input is not equal to second input. If either input is “true” or “false” (not case-sensitive), comparison is made using the equivalent Boolean value. Otherwise, if either input is nonnumeric, compares as strings.

• Logical String (1 Input)— Returns “true” if input is not an empty string. Note than most functoids (except Logical) return an empty string if their inputs are invalid, so this functoid can be used to test the output of other functoids.

• Logical Date (1 Input)— Returns “true” if input can be read as a date and/or time.

• Logical Numeric (1 Input)— Returns “true” if input can be read as a number.

• Logical OR (1 or more Inputs)— Returns “true” if any of the inputs is true. Any input that converts to Visual Basic Boolean True is treated as true.

• Logical AND (1 or more Inputs)— Returns “true” if all the inputs are true. Inputs that convert to Visual Basic Boolean True are treated as true.

• Logical Existence— Returns “true” if the input field or record is present in the source. Note that this is not the same as a field having an empty string value.

Date/Time Functoids

Here are the functoids to return the current date or time, or to add dates (see Figure 5.26):

Here is a quick reference for each Date/Time functoid:

• Add Days (2 Inputs)— Adds second input (number of days), to first input (a date) and returns result in ISO 8601 format, yyyy-mm-dd. If first input is not a valid date, or second input is not numeric, returns empty string.

• Date (No Inputs)— Returns current local date in ISO 8601 format, yyyy-mm-dd.

• Time (No Inputs)— Returns current local time in ISO 8601 format, hh:mm:ss.

• Date and Time (No Inputs)— Returns current local date and time in ISO 8601 format, yyyy-mm-ddThh:mm:ss.

Conversion Functoids

Here are the functoids to convert to and from character codes or to convert numeric radices (see Figure 5.27):

Here is a quick reference for each Conversion functoid:

• ASCII from Character (1 Input)— Returns ASCII (American Standard Code for Information Interchange) numeric code for the first character in the input string. If input is zero length string, returns empty string.

• Character from ASCII (1 Input)— Returns a one-character string corresponding to input ASCII code. If input is nonnumeric, or is not in the range of 1 to 127, returns empty string.

• Hexadecimal (1 Input)— Returns Base 16 representation of a decimal numeric input. Returns blank string if input is nonnumeric.

• Octal (1 Input)— Returns Base 8 representation of a decimal numeric input. Returns blank string if input is nonnumeric.

Scientific Functoids

Here are the functoids to perform advanced mathematical functions such as exponentiation, logarithms, and trigonometry (see Figure 5.28):

Here is a quick reference for each Scientific functoid:

• Arc Tangent (1 Input)— Returns angle whose tangent is the input value. Result in radians, in range –pi/2 to pi/2. To convert radians to degrees, multiply by 180/pi, and vice versa.

• Cosine (1 Input)— Cosine of input angle in radians.

• Sine (1 Input)— Sine of input angle in radians.

• Tangent (1 Input)— Tangent of input angle in radians.

• Natural Exponential Function (1 Input)— e raised to the power of the input.

• Natural Logarithm (1 Input)— Log base e of the input. If input is zero or negative, returns empty string.

• 10^X (1 Input)— v10 raised to the power of X (the input).

• Common Logarithm (1 Input)— Log base 10 of the input. If input is zero or negative, returns empty string.

• X^Y (2 Inputs)— X (first input) raised to the power of Y (second input).

• Base-Specified Logarithm (2 Inputs)— Log base N (second input) of X (first input). Equivalent to natural log of X divided by natural log of N. If either input is zero or negative, or if second input is 1, returns empty string.

Cumulative Functoids

Cumulative functoids (see Figure 5.29) use global script variables (which retain their values for the life of the map execution) to calculate the sum, average, minimum value, and so on over all record instances in the source document that contain the input field.

Here is a quick reference for each Cumulative functoid:

• Cumulative Sum (1 Input)— Returns sum of the input field over all records

• Cumulative Average (1 Input)— Returns average of the input field over all records

• Cumulative Minimum (1 Input)— Returns minimum value of the input field over all records

• Cumulative Maximum (1 Input)— Returns maximum value of the input field over all records

• Cumulative String— Returns concatenation of the input field over all records

Database Functoids

The Database functoids (see Figure 5.30) work cooperatively to query a database table for a record that contains a specified value in a specified column and then to return a value from another specified column in that record.

Here is a quick reference for each Database functoid:

• Database Lookup (4 Inputs)— Connects to the database using second input, queries table, and column specified by third and fourth inputs for value specified by first input. Normally, the table and column name will be constants in the functoid properties, whereas the value will be a linked source field or result of another functoid. Connection string may be a constant input to a separate Concatenate functoid (so the same connection string can be used by multiple Database Lookup functoids.

• Value Extractor (2 Inputs)— First input is a Database Lookup functoid; second input is a column name whose value in the looked-up recordset is to be returned. If recordset is empty, or column value is NULL, returns empty string. If query selected more than one record, only the value from the first record is returned.

• Error Return (1 Input)— Returns the ODBC error string, if any, for the Database Lookup functoid linked as its input. A separate functoid is needed to return the error string because each functoid can only return a single value.

Advanced Functoids

The Advanced functoids (see Figure 5.31) perform specialized actions such as allowing you to write your own custom functoid using script code, counting or picking out records in the source document, combining multiple source record types into a single destination record, or altering the schema complexity during transformation from source to destination. Specific help and examples for the Advanced functoids can be found in BizTalk Server Documentation, Index, Using the [Name] Functoid.

Here is a quick reference for each Advanced functoid:

• Scripting (Varying Inputs)— Contains your own custom script code. Number of inputs varies as required, but must return a single, string-formatted value. Code can be written in either VBScript or JScript.

• Record Count (1 Input)— Returns count of instances of a record in the source document. Input link must be a record.

• Index (2 or more Inputs)— Returns the value of the first input only for the record instance number(s) specified by the subsequent, index-number inputs. In the ScoreReport specification we have been using as an example, to return the ScorePercent field in the second StudentScores record in the document, in its nested third ScoreItem record, you would link the ScorePercent field to the Index functoid and then add 2 and 3 as constant inputs. An index input is required for each record level in the hierarchy containing the input field, not counting the document root node.

• Iteration (2 Inputs)— Returns the sequential number (starting at 1) of a record instance within its outer containing record (or document). Input must be a record.

• Value Mapping (2 Inputs)— Returns value of second input, if and only if first input is the string “true” (case-sensitive).

• Value Mapping (Flattening) (2 Inputs)— Same as Value Mapping, except that multiple input records (elements) are combined into multiple output fields (attributes), thereby reducing the number of levels in, or flattening, the output schema as compared to the input schema.

• Looping (1 or more Inputs)— Input link(s) and output link must be record specifications. Allows fields from multiple source record types to be linked to a single field under the linked destination record. Separate destination record(s) are created for each source record regardless of record type. Equivalent to a UNION SELECT in a database query, in that it creates a single set of output records from multiple sets of input records.