TR Function Reference

All standard transformation functions provided with migration-center are described below. Full context-sensitive information is also available in the WebClient when inserting or editing any transformation function.

CalculateNewDate

Computes a new date by adding Years, Months and Days to a valid source date. The Database’s Datetime pattern must be followed by the input value. Negative integer parameters are accepted.

Example: CalculateNewDate('01.01.2001 12:32:05', 1,2,3) returns '04.03.2002 12:32:05'

CalculateNewNumber

It adds or substract a number to another number. Decimal and negative numbers are allowed. If a provide value cannot be converted to a number then an error is reported during the transformation.

Ex: CalculateNewNumber(10, 5.2) returns 15.2

CalculateNewNumber(1000, -100) returns 900

Concatenate

Concatenate() concatenates up to three strings values into one and returns it as a single value Concatenate('AAA',' and ','BBB') returns "AAA and BBB".

IMPORTANT: The function returns only the first 4000 bytes of the resulted string since this is the maximum length allowed for an attribute value.

ConvertDateTimezones

Converts a given date from one timezone to another. The accepted timezones are the ones provided by Oracle, so you can see them with the following query:

SELECT DISTINCT tzname FROM v$timezone_names;

Count Values

Counts the number of values of a repeating attribute. If null or no values provided it returns 0.

GetDataFromSql

Map one or multiple source attributes with the data extracted from an external table. The external table must be located on the migration-center database in any schema that is accessible by FMEMC user.

The user FMEMC must have "select" permission on that table. The query must return a single column and must have at least one parameter and maximum 3 that will be replaced at runtime with the values of the parameter1, parameter2 and parameter3.

The parameter name in the query is any string the start with : (colon). The number of parameters in the query must match the number of parameters set in the function.

If the query returns multiple values the following behavior applies:

• if the rule is single value only the first value returned by the query will be taken in consideration by the transformation engine.

• if the rule is multivalue then all values returned by the query will be taken in consideration by the transformation engine. Nevertheless, to prevent loading millions of values in the database because of a wrong query, the number of values that can be returned by this function are limited to 10,000.

Example:

select user_id from mcextra.users_data where username = :username

Important Note: Since the SQL query is executed for each object in the migset, you should ensure that it is executed fast, i.e. the columns used in the where condition should be indexed.

GetDateFromString

GetDateFromString() extracts a date type expression from any string if it matches the date expression specified by the user.

Use this function to extract non-standard or partial dates from source attributes like a filename.

Example: GetDateFromString('filename 2007-Dec 14. 16:11','YYYY-

MON DD. HH24:MI') will identify and extract the non-standard date format contained within the input string

GetInternalAttributeValue

It is used to get the value of an internal attribute of the source objects. An internal attribute is a fix column, in the source objects view that is used internally by migration-center for different purposes. The available internal attributes that can be used with this function are: Id, Is_update, Content_location, Content_hash, Id_in_source_system, Parent_version_object_id, Level_in_version_tree, Scanned_date

The name of the internal attributes are not case sensitive.

If other internal attribute name is provided an error is reported during the transformation.

GetPathLevel

It can understand strings representing paths and can extract specific levels from that path. The path separator character, the path level to start from and the path level up to which the function should extract the subpath can be specified as input parameters. The function will also strip leading and ending path separators from the result.

Example: GetPathLevel('/this/is/the/folder/structure','/','2','4') will parse the input string looking for "/" as the path separator, and return path levels 2-4, i.e. "is/the/folder"

GetSnippetRuleValue

It returns the values of another rule that was marked as a Snippet Rules. By using GetSnippetRuleValue, users can avoid duplicating the same steps across multiple rules.

If the specified rule does not exists within the migset, the step will fail.

It is not allowed to use a snippet rule inside another snippet rule.

GetValue

It is used to migrate attributes where the attribute's value is not supposed to be changed or to generate and set user defined values which are not present in the source data and cannot or have not been generated by other transformation functions. The GetValue() function always returns the exact same value it gets as input without altering it in any way. Examples: GetValue('user') outputs the string value "user" for all objects to which the current rule applies GetValue(filename[1]) outputs the value of the source attribute filename for all objects to which the current rule applies. For each object, the value will vary according to the actual value from that objects' source attribute named filename.

GetValueAt

It gets the value at specific index from a multi-value attribute. Index counting starts with 1. If the provided index is out of range the function returns null.

Example: GetValuesAt('a,b,c', 2) reruns 'b' and GetValuesAt('a,b,c', 4) returns null.

GetValueIndex

Get the first index number of a given value for a multi-value attribute. If no value was found 0 is returned.

The parameter "ExactMatch" specifies if exact match will be used for comparing the values. Use '1' or 'T' for exact match and '0' or 'F' for "contains" search. In any case the search is case sensitive.

Example: GetValueIndex('abc,def,ghi', 'de', 'F') returns 0

GetValueIndex('abc,def,ghi', 'de', 'T') returns 2

GetValueIndex('abc,def,ghi', 'DE', 'T') returns 0

GetValueIndex('a,b,c,b','b' ', 'F') returns 2

If

If() evaluates a logical condition and returns different outputs depending on whether the condition is found to be true or false.

A previous transformation step from the current rule, a source attribute or a user specified string are all valid arguments for both input and output values as well as for the logical condition.

The If() function can correctly evaluate conditions based on various types of data such as strings, numbers, dates, null values, etc. and offers a number of predefined conditional operators.

Length

Calculates the length of the string using Unicode characters.

Ltrim

The Ltrim function removes characters from the left of the given Source String, with all the leftmost characters that appear in the Characters to trim removed. The function begins scanning the Source String value from its first character and removes all characters that appear in the Characters to trim until reaching a character that is not in the trim expression and then returns the result. If second parameter is empty the leading spaces will be removed, i.e. Ltrim('babcde','ab') will remove the first 3 characters so the result will be 'cde'".

MapValue

MapValue() considers the input value a key, looks for a row with a matching key in a specified mapping list and returns the value corresponding to that key if a suitable match is found. Keys with no match can be reported as transformations errors (optional).

A mapping list must be defined before using a MapValue function. Mapping lists can be defined either on the Mapping lists tab in the Transformation Rules window of a migration set (case in which they would be available only to that particular migration set), or as a global mapping list (available to all migration sets) from the Manage menu in the main application window. Use the MapValue() function to define direct mappings of source attribute values to target attribute values based on simple key-value lists.

MultiColumnMapValue

MultiColumnMapValue() considers the input value a key, looks for a row with a matching key in a specified mapping list, and returns the value corresponding to that key if a match is found. Keys with no match will be assigned a null value, or they can be reported as transformations errors instead, forcing the user to take action with regard to such values. A mapping list must be defined before using a MultiColumnMapValue function. Mapping lists can be defined either on the Mapping lists tab in the Transformation Rules window of a migration set (case in which they would be available only to that particular migration set), or as a global mapping list (available to all migration sets) from the Manage menu in the main application window. Use the MultiColumnMapValue() functions to define direct mappings of source attribute values to target attribute values based on simple key-value lists.

Multivalue_RemoveDuplicates

Remove duplicates from a multivalue attribute. To use this function in a rule, the rule must be a multi-value rule. If the input values are a,b,b,c,a the result will be a,b,c.

MultiValue_ReplaceNulls

MultiValue_ReplaceNulls() can replace null values in a multi-value attribute with another, user defined value. The function can also remove null values from a multi-value attribute if no replacement string is defined. To use this function in a rule, the rule must be a multi-value rule. Examples: MultiValue_ReplaceNulls(multi_value_input[all],'default') will replace all null values from the multi-value source attribute named "multi_value_input" with "default" MultiValue_ReplaceNulls(multi_value_input[all],'') will remove all null values from the multi-value source_attribute named "multi_value_input", thereby reducing the total number of values for the multi-value attribute.

RemoveDuplicates

Provided for removing duplicates from a given string.

Example:

RemoveDuplicates('DE|RO|IT|DE|P','|') will remove duplicates form the first string by using the delimiter ‘|’ so it will return "DE|RO|IT|P.

The function can be used in combination with RepeatingToSingleValue and SingleToRepeatingValues for removing duplicated values from a repeating source attribute.

Example:

#1 RepeatingToSingleValue (countries[all], ‘|’)

#2 RemoveDuplicates(#1, ‘|’)

#3 SingleToRepeatingValues(#2,’|’)

RepeatingToSingleValue

RepeatingToSingleValue() concatenates all values of the source string value into one single string.

Optional parameters include the delimiter to be used (can be zero, one or multiple characters), the range of values which should be concatenated and a replacement string to be used in place of any NULL values the source may contain.

It is recommended to use a multi-value (repeating) attribute or previous step as source for this function

Example:

SingleToRepeatingValues(keywords[all],'|') will return value1|value2|value3.

SingleToRepeatingValues(keywords[all],'|', 2, 3) will return value2|value3.

IMPORTANT: The function returns only the first 4000 bytes of the resulted string since this is the maximum length allowed for an attribute value.

ReplaceStringRegex

ReplaceStringRegex() Replaces the parts of the input value that match the regular expression specified by the user with a user defined value Example: ReplaceStringRegex('AAAAA-CX-9234-BBBBB','\w{2}-\d{4}','AB-0000') will parse the input string looking for a match; according to the regex this would be a sequence of 2 letters followed by a dash and four numbers. Since the input does contain a matching part, it will be replaced with "AB-0000", and the final output of the function will be "AAAAA-AB-0000-BBBBB"

Rtrim

The Rtrim function removes characters from the right of the given Source String, with all the rightmost characters that appear in the Characters to trim removed. The function begins scanning the Source String value from its last character and removes all characters that appear in the Characters to trim until reaching a character that is not in the trim expression and then returns the result. If second parameter is empty the trailing spaces will be removed, i.e. Rtrim('cdebab','ab') will remove the last 3 characters so the result will be 'cde'.

SingleToRepeatingValues

SingleToRepeatingValues() separates a string value based on a user specified separator character and returns all resulting values as a multi-value result Use this function to transform a string of comma separated values into a multi-value attribute with multiple individual values. To use this function in a rule, the rule must be a multi-value rule Example: SingleToRepeatingValues(comma_separated[all],',') will parse the source attribute named "comma_separated" looking for commas (","), strip the commas and create a multi-value list from the resulting values.

SplitStringRegex

SplitStringRegex() is an advanced function for splitting up a string value by specifying the separator as a regular expression rather than a single character (the regex can represent a single character as well). Depending on the number of matches for the specified separator, multiple substrings can result from the function; which one of the resulting substrings the function should return can also be specified by the user.

Example: SplitStringRegex('one-(two)-three','(-\()|(\)-)','2') will split the string into substrings based on the separators described by the regex and return the second substring which is "two"

SubStringRegex

SubstringRegex() is an advanced transformation function for extracting a substring from the input value. A regular expression can be used to extract a complex substring from the input string, such as a particular name, a formatted number sequence, a custom date expression, an email address, etc. SubstringRegex('0123abc 4567 ',' \d{4} ') will return " 4567 " according to the regex defining the substring.

Substring

Substring() returns part of the input string. Which part of the string should be returned can be specified as a number of characters starting from a given index within the input string.

Example: Substring('teststring','3','5') returns 5 characters starting with the 3rd character, which is "ststr"

Sysdate

Sysdate() outputs the current system date as a value. Use this function to track the date when a document underwent transformation in migration-center. This function does not have any properties.

ToUpperCase

ToUpperCase() transforms all characters from the input string value to uppercase characters

ToLowerCase

ToLowercase() transforms all characters from the input string value to lowercase characters