Help Functoid Definitions
Advanced Functoids
Call Nested Map
Function
Allows you to call another map from within a map (ideal for breaking down a complex map into separate tasks).
Use
Specify the map to be called, and any more parameters; the first parameter is the source document to the map being called. Any other parameters can be embedded in parameter values using {1} to {10} syntax, for additional parameters 1 to 10.
Call Nested XML
Function
Allows you to call another map from within a map, but assumes the input and output to the map being called are both in Xml format.
Use
Specify the map to be called, and any more parameters; the first parameter is the source document to the map being called. Any other parameters can be embedded in parameter values using {1} to {10} syntax, for additional parameters 1 to 10.
Code Functoid
Function
Allows custom C# or VB.Net Visual Studio for Applications code to be executed.
Use
Double click to edit the properties, then enter a function name, click OK and the VSTA editor is launched allowing you to enter custom code. You can edit the generated function to take appropriate parameters and return required values.
 |
NOTE You can debug Code Functoids, by simply using the Debug | Start menu option in Visual Studio for Applications. |
Collection Accessor
Function
Allows an item from a System.Collections based object to be accessed.
Use
Accesses an item from a collection, using the second parameter as the index ( 1 based ).
Collection Enumerator
Function
To iterate through any collection, and fire an output value once per item in the collection.
Use
The collection parameter (the only parameter) can be any object implementing IEnumerable. Ideal for retrieving the contents of an array individually.
Count
Function
Counts all scoped instances of the parameter link.
Use
Returns the current numeric count of all scoped instances of the parameter link. This differs from the Cumulative Count functoid, which returns the final rather than current count.
Default Value Functoid
Function
Replaces an empty or null value with a default value.
Use
The Default Value Functoid allows you to specify a default value to replace any empty or null value. Null values are returned from certain functoids to indicate the absence of a result, whereas empty values are valid but empty strings. In the absence of a Default Value Functoid, null values will not generally be propagated through the map.
Duplicate Suppress
Function
Removes duplicate values leaving only unique values.
Use
This Functoid only allows unique values (within any specified scope, or the Page scope by default) through by suppressing any parameter value it has ‘seen’ before.
Multi-Channel Collection Enumerator
Function
Iterates through multiple collections, firing an output value once per item in each collection.
Use
Has the same effect as the Collection Enumerator functoid, except it may have multiple input parameters and will iterate through each of them.
Multi-Channel Enumerator Channel Select
Function
Selects a channel from a Multi-Channel Enumerator output.
Use
The first parameter is the array object, the second is the channel index (starting from one)
Passive Value Functoid
Function
Prevents a functoid from being active, and thus automatically creating an output value.
Use
The Passive Value Functoid can be used to remove the active attribute of a value, such that it will no longer force the creation of the output node to which it connects. Thus the value specified through a Passive Value Functoid will only be used if something else causes the relevant output node to be created. Passive Value Functoids can be used to minimize If / Then logic in Maps.
Value Change
Function
Suppresses any successive outputs of the same value.
Use
This Functoid pushes an active value out only if the value is different to any previous value. With sorted, flattened input this thus allows the creation of structured output by triggering new Xml output grouping elements when values change. An example of this is below.
Suppose you have input data on telephone numbers, in the following format:
| Contact ID | Telephone |
| 1 | 09673 372813 |
| 1 | 012 233 7281 |
| 2 | 09633 422813 |
| 3 | 013 232 4923 |
| 3 | 08633 2932323 |
Note that each contact can have more than one telephone number.
Suppose you also desire Xml data in the following format:
<SSIS>
<Row ContactId="1">
<Telephone>09673 372813</Telephone>
<Telephone>012 233 7281</Telephone>
</Row>
<Row ContactId="2">
<Telephone>09633 422813</Telephone>
</Row>
<Row ContactId="3">
<Telephone>013 232 4923</Telephone>
<Telephone>08633 2932323</Telephone>
</Row>
</SSIS>
This is readily achieved. Simply create an approprite Xml schema (making sure Telephone has a MaxOccurs > 1), and map through to the ContactId attribute via a Value Change functoid. This will trigger the creation of a new attribute (and thus containing element) only when the value changes. Note that this approach relies on the input data being appropriate sorted.
Constant Functoids
Constant
Function
Provides a constant value.
Use
The Constant Functoid allows you to specify a constant value as output, and is used to populate fixed values, for example a customer reference number when creating a supplier order. This Functoid takes no parameters – you specify the constant value via its’ properties form as shown below.
Constant Value Map
Function
Maps a constant value to another.
Use
The Constant Value Map allows you to add a number of mappings from constant value to constant value.). This can be useful in tidying up excess if/then/else logic. The parameters can be of any type, although are compared as strings. If a match is not found, there are four options to choose from:
- Fail: throw an exception
- Return null:
nullis returned instead of a constant - Suppress output: nothing is returned
- Set to default: a specified value is returned
Below is the Constant Value Map Properties window, with the Constant Value Map tab selected.
Cumulative Functoids
Convert to Integer
Function
Turns the specified string or object into an integer.
Use
The Convert to Integer Functoid takes an input parameter and converts it to an integer, default value 0 if the value cannot be converted. Floating point values are rounded down.
|
NOTE To round up first add 0.5 by using Constant and Numeric Add functoids |
Is Number
Function
Checks the given value is a number.
Use
This Functoid takes a single argument, and returns true or false depending on whether the given argument is numeric (or can be converted to numeric) or not.
Concatenate
Function
Returns the string concatenation of all scoped instances of the parameter link.
Use
All instances of the parameter link are appended together into a single string.
Count Distinct
Function
Returns the numeric count of all distinct scoped instances of the parameter link.
Use
All the distinct instances of the parameter link are counted, returning an integral total.
Count
Function
Returns the numeric count of all scoped instances of the parameter link.
Use
All the scoped instances of the parameter link are counted, returning an integral total.
First
Function
Returns the first scoped instance of the parameter link.
Use
The first scoped instance of the parameter link is returned. Any following instances are ignored.
Last
Function
Returns the last scoped instance of the parameter link.
Use
The last scoped instance of the parameter link is returned. Previous instances are ignored.
Maximum String
Function
Returns the string maximum of all scoped instances of the parameter link.
Use
Compares all scoped instances of the parameter link, and returns the string maximum.
Maximum
Function
Returns the numeric maximum of all scoped instances of the parameter link.
Use
Compares all scoped instances of the parameter link, and returns the maximum.
Mean
Function
Returns the numeric mean (average) of all scoped instances of the parameter link.
Use
Compares all scoped instances of the parameter link, and returns the average.
Minimum String
Function
Returns the string minimum of all scoped instances of the parameter link.
Use
Compares all scoped instances of the parameter link, and returns the maximum.
Minimum
Function
Returns the numeric minimum of all scoped instances of the parameter link.
Use
Compares all scoped instances of the parameter link, and returns the minimum.
Sum
Function
Returns the numeric sum of all scoped instances of the parameter link.
Use
Adds all scoped instances of the parameter link together and returns the total.
Database / Table Functoids
Database Table Functoid
Function
A functoid that executes a SQL statement to return a Data Table.
Use
The Database Functoid allows you to specify a database connection and a query (which can have token replacements from Functoid parameters), and returns a DataSet (System.Data.DataSet) which can then be further processed by other Functoids, typically the DataSetTableSelector.
The database connection and query template of the Database Functoid are configured via a Properties page, which can be viewed by double-clicking. Token parameters are added by dragging them into the left of the Functoid, as normal.
Database Value Functoid
Function
Retrieves a value from a database.
Use
The Database Value Functoid allows you to look up a value from a database directly.
DataRow Field Extractor
Function
Returns a Data Value field from a DataRow (System.Data.DataRow).
Use
Allows the selection of a Data Value from a DataRow (System.Data.DataRow) by providing a name or index in the second parameter. Typically the DataRow would have been returned by a TableRowEnumerator or TableRowExtractor Functoid.
DataSet Table Selector
Function
Selects a Table (System.Data.DataTable) from a DataSet (System.Data.DataSet).
Use
Allows the selection of a Table (System.Data.DataTable) from a DataSet (System.Data.DataSet) by providing the table name in the second parameter.
Make Table
Function
Creates a table by logically grouping the input parameters.
Use
The functoid structures all of the input parameters into a table, allowing a lookup by row and column.
Drag the incoming links to the Functoid, double click to edit the Functoid properties, set the number of rows and columns, and for each cell in the table specify either a fixed value or select an incoming link.
Table Lookup
Function
Extracts a value out of the given System.Data.DataTable.
Use
This Functoid allows you to extract a value from a DataTable, by specifying a DataTable, a Key (first column) value, and the name of the column for which you want to extract the value. null is returned if no matching value is found.
Table Row Count
Function
Returns the count of rows in the given table (System.Data.DataTable).
Use
This Functoid returns the count of rows in the specified DataTable.
Table Row Enumerator
Function
Enumerates the rows in a table.
Use
This Functoid enumerates the rows in a DataTable, firing an output DataRow for each (which can in turn be used by a DataRowFieldExtractor Functoid).
Table Row Extractor
Function
Returns the given row of the System.Data.DataTable (1-based).
Use
This Functoid extracts a row from a DataTable by index (1-based – i.e. the first record is 1). It takes two parameters, the DataTable (typically obtained from another Database Functoid) and the index.
Date / Time Comparison Functoids
Equals
Function
Compares two date/times for equality.
Use
Compares two date/time parameters and returns true if they are equal.
Greater than or Equals
Function
Compares two date/times for one being great than or equal to the other.
Use
Compares two date/time parameters and returns true if they are equal or if the first is later.
Greater Than
Function
Compares two date/times for one being greater than the other.
Use
Compares two date/time parameters and returns true if the first is later.
Less than or Equals
Function
Compares two date/times for one being less than or equal to the other.
Use
Compares two date/time parameters and returns true if they are equal or if the first is earlier.
Less than
Function
Compares two date/times for one being less than the other.
Use
Compares two date/time parameters and returns true if the first is earlier.
Not Equals
Function
Compares two date/times for inequality.
Use
Compares two date/time parameters and returns true if they are not equal.
Date / Time Functoids
Date Add
Function
Adds an offset to a Date/Time.
Use
A quantity (second parameter) is added to a date (first parameter). An optional time unit (y,mo,d,h,mi,s,ms) can be provided in a third parameter.
Date Difference
Function
Calculates the difference between two Date/Times.
Use
Calculates the difference between two Date/Times, as a floating point number if a Time unit is provided, otherwise as a System.TimSpan.
Date/Time Format
Function
Formats a Date/Time into string format.
Use
An explicit date/time format string can be provided in a second parameter, (in .Net format).
Date/Time Parse
Function
Parses a Date/Time from string format into a System.DateTime value.
Use
This functoid does the reverse of DateTimeFormat, again using a format string in a second parameter. See above.
Now (Date)
Function
Returns the current date.
Use
Returns the current date as a System.DateTime.
Now (Date/Time)
Function
Returns the current date/time.
Use
Returns the current date and time as a System.DateTime.
Now (Time)
Function
Returns the current time.
Use
Returns the current time as a System.TimeSpan.
Time Add
Function
Adds an offset to a Time.
Use
A quantity (second parameter) is added to a time (first parameter). An optional time unit (h,mi,s,ms) can be provided in a third parameter.
Time Parse
Function
Parses a Time from a string.
Use
Parses a Time from string format into a System.TimeSpan value.
HL/7 Functoids
HL/7 ACK
Function
Creates a HL/7 v2.x ACK message.
Use
The functoid takes a string message and two optional boolean flags. The first to indicate whether to acknowledge success or failure (default = success), the second to indicate of any additional freetext information to specify (v3 only).
Logical Functoids
AND
Function
The logical AND of the input conditions.
Use
Returns true if all the boolean parameters are true. Otherwise returns false.
EXISTS
Function
Checks the given value/node exists.
Use
Returns true if the given value/node is not null.
EXISTS and not empty
Function
Checks the given value/node exists and is not empty
Use
Returns true only if the given value/node is not null and is not empty.
NOT
Function
The logical NOT of the input condition.
Use
Returns the inverse of a boolean parameter. i.e. true returns false, false returns true.
OR
Function
The logical OR of the input conditions.
Use
Returns true if any of the two or more parameters are true.
Flow Control Functoids
If/Then/Else
Function
If/Then/Else logic.
Use
This Functoid allows you to specify a condition and one or two alternate values. If the condition equates to true, the first value is returned from the Functoid. If the value is false, the second value will be returned (or if no second value was specified there will be no output value).
The condition can be in the form of the output of a logical or comparison Functoid, or it can be a scope, or it can be convertible to a string – in which case it will be false if it is empty, null, 0, or any established English representation of false and otherwise true.
If/Then
Function
If/Then logic.
Use
If the first boolean parameter is true, the second parameter is returned, if false it isn’t.
Conditional Go
Function
Conditionally allows data passing through a link.
Use
The Conditional Go functoid stops data passing through a link unless the specified condition is met. It is different to the If / Then functoid as instead of propagating a null value if the condition is not met, it gives no output at all.
Conditional Stop
Function
Conditionally stops data passing through a link.
Use
The Conditional Stop functoid stops data passing through a link if the specified condition is met.
Conditional Stop If Null Or Empty
Function
Stops null or empty values passing through a link .
Use
The functoid stops data passing through a link if it is either null or is empty.
Numeric Comparison Functoids
Equals
Function
Compares two or more numbers for equality.
Use
Returns true if all the numeric parameters are equal.
Greater than or Equal
Function
Compares two numbers for difference and equality.
Use
Compares two numeric parameters and returns true if they are equal or if the first has the greater value.
Greater Than
Function
Compares two numbers for difference.
Use
Compares two numeric parameters and returns true if the first has the greater value.
Less than or Equal
Function
Compares two numbers for negative difference and equality.
Use
Compares two numeric parameters and returns true if they are equal or if the first has the lesser value.
Less than
Function
Compares two numbers for negative difference.
Use
Compares two numeric parameters and returns true if the first has the lesser value.
Not Equal
Function
Compares two numbers for inequality.
Use
Compares two numeric parameters and returns true if their value’s differ.
Between
Function
Determines whether a value lies between two numbers inclusively.
Use
Returns true if the parameter is greater or equal to the lower boundary and less than or equal to the upper boundary
Numeric Functoids
Abs
Function
Returns the absolute value of a number.
Use
If a numeric parameter is negative, this functoid changes the sign to positive. Positive parameters are returned unchanged.
Add Percent
Function
Adds the specified percentage to the given number.
Use
The second parameter is the percentage, which is added to the value in the first parameter.
Add
Function
Adds numbers together.
Use
Adds two or more numeric parameters together and returns the total.
Divide
Function
Divides one number by another.
Use
Divides the first numeric parameter by the second numeric parameter and returns the result.
Log
Function
Returns the power 10 log of the given number.
Use
Returns the power 10 log of the given number so that if the parameter is x and the returned value is y, x= 10y
Maximum
Function
Returns the maximum of a set of numbers.
Use
Compares two or more numeric parameters and returns the parameter of greatest value.
Minimum
Function
Returns the minimum of a set of numbers.
Use
Compares two or more numeric parameters and returns the parameter of least value.
Multiply
Function
Multiplies numbers together.
Use
Multiplies two or more numeric parameters together and returns the product.
Power
Function
Raises one number to the power of another.
Use
The first parameter is the base, the second the power. The result is returned.
Remainder
Function
Returns the integer remainder after dividing one number by another.
Use
Divides the first numeric parameter (dividend) by the second numeric parameter (divisor) and returns the difference between the integral result (quotient), and the first parameter (dividend).
Square Root
Function
Returns the square root of the given number.
Use
Returns the square root of the numeric parameter such that param = result * result
Subtract
Function
Subtracts one number from another.
Use
Returns the result of the first parameter minus the second.
String Comparison Functoids
Equals
Function
Compares two strings for equality.
Use
Compares two string parameters and returns true if they are equal. If an optional third boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
Greater than or Equals
Function
Compares two strings for difference and equality.
Use
Compares two string parameters and returns true if they are equal or if the first is alphabetically later. If an optional third boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
Greater Than
Function
Compares two strings for difference.
Use
Compares two string parameters and returns true if the first is alphabetically later. If an optional third boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
Less than or Equals
Function
Compares two strings for negative difference and equality.
Use
Compares two string parameters and returns true if they are equal or if the first is alphabetically earlier. If an optional third boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
Less than
Function
Compares two strings for negative difference.
Use
Compares two string parameters and returns true if the first is alphabetically earlier. If an optional third boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
Not Equal
Function
Compares two strings for inequality.
Use
Compares two string parameters and returns true if they are not equal. If an optional third boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
String Functoids
Concatenate
Function
Concatenates strings together.
Use
Appends two or more strings together in parameter order, and returns the combined string.
First Word
Function
Extracts the first word of the specified string.
Use
Returns the sequence of characters before the first TAB or SPACE.
Last Word
Function
Extracts the last word of the specified string.
Use
Returns the last sequence of characters after the final TAB or SPACE.
Length
Function
Returns the length of the given string.
Use
Returns the length of the string parameter.
Pad
Function
Pads the given string to a minimum number of characters.
Use
Pads a given string (first parameter) to a minimum set size (second parameter). An optional (third parameter) padding character can be specified. If one isn’t SPACE is used. A boolean (fourth parameter) can be optionally specified; if true or 1, the string is padded on the right side, otherwise it is padded on the left.
Split
Function
Splits a string at a separator.
Use
Splits a string according to an optionally supplied separator. A comma or newline is used if a separator isn’t supplied.
String Find
Function
Searches for an instance of a substring within a string.
Use
Finds an instance of the second string specified in the first string, and returns its’ index (0 if not found, 1 is the index of the first character)
String Format
Function
Forms a tokenized string.
Use
Forms a tokenized string from an optional number of parameter tokens. The format string is specified in the properties window as shown below.
For example if the first parameter is ‘fluffy’ and the second parameter is ‘cat’, the format string above would return the result ‘fluffy is a cat’.
String Replace
Function
Replaces all instances of a string with another.
Use
Replaces all instances of the second string parameter in the first string parameter with the replacement third string parameter. If an optional forth boolean parameter is true, the comparison is case sensitive. Otherwise the case is ignored.
Substring
Function
Extracts a substring from a string.
Use
An input string, index, and length parameter (in that order) are provided and a substring starting at the index (from 1) and of specified length is extracted from the input string.
To Lower
Function
Turns a string to lower case.
Use
Returns the specified string parameter in lower case.
To NameCase
Function
Turns a string to name-case.
Use
Returns the specified string parameter in name-case; the first character of each word is capitalized, the rest are in lower case.
To Upper
Function
Turns a string to upper case.
Use
Returns the specified string parameter in upper case.
Trim
Function
Trims a specified string of whitespace.
Use
Trims the specified string parameter of leading and trailing whitespace.
XML Functoids
Deep Copy
Function
Copies an XML element, with all child elements and attributes.
Use
This Functoid copies a complete tree from the source document to a similar tree in the destination document – that is including the complete child structure. This can be particularly useful when using ‘Any’ schema elements.
Literal XML
Function
Allows literal XML to be specified.
Use
This Functoid allows you, via its’ properties page accessible by double-clicking, to specify a literal Xml document which can then be passed as parameter to other Xml related Functoids including the XPathExpression and XslTransformation ones.
Merge XML
Function
Merges two Xml documents, by appending the records under the root element.
Use
The Merge Xml Functoid takes two Xml documents, and merges them together. Effectively all child-nodes of the root of the second document are added to the root of the first document.
XPath Expression
Function
Allows execution of an Xpath expression on either a given document node (if specified) or the whole document (if no parameters are specified).
Use
This Functoid allows you to execute an XPath expression, which can include tokens corresponding to parameters. It takes an XML Document as parameter, plus up to 10 parameter tokens. The XPath expression is configured via its’ properties form, which can be obtained by double-clicking, and includes a link to embed tokens in the expression, thus allowing dynamic lookup.
This Functoid can return a collection, value or System.XmlNode as detailed on this form.
XSL Transformation
Function
Allows execution of an XSL stylesheet on either a given document node (if specified) or the whole document (if no parameters are specified).
Use
This Functoid allows you to execute an Xsl transformation on an Xml document. The Xsl is configured via the properties form, launched by double-clicking. The Xml document is taken as parameter.
