The Robust Transform Engine (RTE) operation types are common operation methods for use in ETL without having to write your own complex data transformations.

The following operation types are available in the Integration Commons for CMDB app.

Concatenation

Combines the values from input fields into a single string, joining them on the optional joining_string field.

Details
Table RTE Entity Concatenation Operation [sys_rte_eb_concat_operation]
Input field source_sys_rte_eb_fields

Input is a set of fields and constants.

Output field target_sys_rte_eb_field

Output is the combined value of the provided fields as a single string.

Additional fields joining_string (optional)
Table 1. Example
Input joining_string Result
"input_1", "input_2", "input_3" ", " "input_1, input_2, input_3"

Convert to Boolean

Converts the incoming value to a Boolean.

Details
Table RTE Entity Convert to Boolean Operation [sys_rte_eb_to_boolean_operation]
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_field

Output is true when input is true or 1, otherwise output is false. (case insensitive)

Convert to Date

Attempts to convert the incoming value to a GlideDateTime value by applying the date_format to the incoming value.

Details
Table RTE Entity Convert to Date Operation [sys_rte_eb_to_date_operation]
Input field source_sys_rte_eb_field

Input is a data timestamp value with date format.

Output field target_sys_rte_eb_field

Output is the date timestamp in the specified date format. Attempts to directly convert using GlideDateTime if the date_format is incorrect. Returns an empty value if unable to parse at all.

Table 3. Example
Input Result
"2018/09/20 11:21:00 AM EST" with date_format "yyyy/MM/dd hh:mm:ss a z" "2018-09-20 16:21:00"
"2018/09/20 01:21:00 PM EST" with date_format "yyyy/MM/dd hh:mm:ss a z" "2018-09-20 18:21:00"
"09/20/18" with date_format "yyyy/MM/dd hh:mm:ss a z" ""0018-09-20 00:00:0"

Convert to Numeric

Converts the incoming value to a number.

Details
Table RTE Entity Convert to Numeric Operation [sys_rte_eb_to_numeric_operation]
Input field source_sys_rte_eb_field

Input is a value.

Output field target_sys_rte_eb_field

Output is a numeric value. If the input value is non-numeric the output is empty.

Copy

Copies the value of the source field to all the target fields.

Details
Table RTE Entity Copy Operation [sys_rte_eb_copy_operation]
Input field source_sys_rte_eb_field

Input is a value.

Output field target_sys_rte_eb_fields

Output is the copied source field value.

Additional field overwrite_existing_value (optional, Boolean): If true, then the values of the target fields are replaced. Otherwise, any non-empty value isn’t overwritten.

Extract First Numeric

Sets the target field as the first numeric value found in the source field.

Details
Table RTE Extract Numeric Operation [sys_rte_eb_extract_numeric_operation]
Input field source_sys_rte_eb_field

Input is a value.

Output field target_sys_rte_eb_field

Output is the numeric value found in the input.

Additional fields
  • decimal_places (optional, number) : Forces the output to have a specified number of decimal places.
  • remainder_target_field (optional, reference to field): Set to the trimmed remainder of the source field, after removing the first numeric value.

Glide Lookup Operation

Performs a lookup in the database on the target table specified in the target_table field.

Details
Table RTE Glide Lookup Operation [sys_rte_eb_glide_lookup_operation]
Input field source_sys_rte_eb_fields

The database table for lookup.

Output field target_sys_rte_eb_fields

The resulting data based on the lookup operation.

Additional fields
  • target_table
  • glide_matching_fields (string): Comma-separated list of column names in the target table. For each input field in source_sys_rte_eb_fields, there must be an equal number of values in glide_matching_fields.
  • glide_target_fields (string): Comma-separated list of column names in the target table. For each target field in target_sys_rte_eb_fields, there must be an equal number of values in glide_target_fields.
Table 6. Example
Input Result
  • Input Field 1: 100 South Charles Street, Baltimore
  • Input Field 2: MD
  • Target Table: Location (cmn_location)
  • Glide Matching Fields: street,state
  • Glide Target Fields: sys_id
Output Field 1: 25ab9c4d0a0a0bb300f7dabdc0ca7c1c

Multiple Input Script 

Runs a script with multiple inputs setting the target_sys_rte_eb_field field as the output for that script.

Details
Table RTE Entity Multiple Input Script Operation [sys_rte_eb_multi_in_script_operation]
Input field source_sys_rte_eb_fields

Input is a script.

Output field target_sys_rte_eb_field

Output is the result of the input script.

Additional Fields
  • script (script)
  • use_unique_input_sets(Boolean): When true, only unique input values are included in the data batch for IRE processing. Otherwise, all input object’s field values are included.

Example for using use_unique_input_sets, with a script function that takes record_type and operating_system as input and returns record_with_os:

Table 7. Examples
Record record_type operating_system
1 computer Windows XP
2 computer Linux
3 computer Windows XP

If use_unique_inputs_sets is set to true, then the script processes only two values (computer + Windows XP and computer + Linux). If use_unique_inputs_sets is set to false, then each of the three values is individually processed (computer + Windows XP, computer + Linux, and computer + Windows XP).

Sample script:
(function(batch, output) { 
                for (var i = 0; i < batch.length; i++) { 
                        // batch[i] is the unique set of inputs/individual record 
                        // batch[i].<field> gives access to the field value 
                        var in0 = gs.nil(batch[i].record_type) ? '' : batch[i].record_type;
                        var in1 = gs.nil(batch[i].operating_system) ? '' : batch[i].operating_system;
                        // output[i] is the output for the specific combination of inputs/individual record 
                        output[i] = in0 + "_" + in1; 
                    } 
                } 
            })(batch, output);

Multiple Input/Output Script

Runs a script with multiple inputs setting the target fields specified in the target_sys_rte_eb_fields field as the multiple outputs for that script.

Details
Table RTE Entity Multiple Input/Output Script Operation [sys_rte_eb_multiple_input_output_script_operation]
Input field source_sys_rte_eb_fields

Input is a script.

Output field target_sys_rte_eb_fields

Output is the result of the input script.

Additional Fields script (script)
Sample script:
(function(batch, output) { 
                for (var i = 0; i < batch.length; i++) { 
                        var userId = (batch[i].user_id);
                        var userIdParts = userId.split(".");
                        output[i].first_name = userIdParts[0]; 
                        output[i].last_name = userIdParts[1];
                    } 
                } 
            })(batch, output);

Regex Replace 

Replaces each substring of the input string that matches the regular expression pattern specified in the match_regex field with the string specified in the replacement_regex field.

Details
Table RTE Entity Regular Expression Replace Operation [sys_rte_eb_regex_replace_operation]
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_field

Output is the replaced string.

Additional fields
  • match_regex (string, regular expression)
  • replacement_regex (string) 
Table 8. Example
Input Result
"String&With(Special)$Characters" with match_regex="[^0-9a-zA-Z]+" and replacement_regex=" " "String With Special Characters"

Replace 

Replaces each substring of the input string that matches the string specified in the match_string field with the string specified in the replacement_string field.

Details
Table RTE Entity Replace Operation [sys_rte_eb_replace_operation
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_field

Output is the replaced string.

Additional fields
  • match_string (string)
  • replacement_string (string)
Table 9. Example
Input Result
"Original String" with match_string = "Original" and replacement_string= "Replacement" "Replacement String"

Round Numeric

Rounds off the input numeric value to the nearest whole number.  Non-numbers are truncated.

Details
Table RTE Entity Round Numeric Operation [sys_rte_eb_round_numeric_operation]
Input field source_sys_rte_eb_field

Input is a numeric value.

Output field target_sys_rte_eb_field

Output is a whole number.

Additional fields
  • match_string (string)
  • replacement_string (string)
Table 10. Example
Input Result
"1.5" "2"
"1.4" "1"
"i’m a string" "" 

Script Operation

Runs a script with an input and sets the target_sys_rte_eb_field field as the output for that script.
Note: The Script operation type has been largely superseded by the Multi Input Script Operation and is included for backwards compatibility with the existing configurations. 
Details
Table RTE Entity Script Operation [sys_rte_eb_script_operation]
Input field source_sys_rte_eb_field

Input is a script.

Output field target_sys_rte_eb_field

Output is the result of the input script.

Additional fields
  • script(script)
  • use_unique_input_sets (Boolean): When true, only unique input values are included in the data batch for IRE processing. Otherwise, all input object’s field values are included. For an example and for more details, see the Multiple Input Script transform.
The source field is included in the ‘batch’ variable as the JavaScript field ‘input’.
(function(batch, output) { 
                for (var i = 0; i < batch.length; i++) { 
                        // batch[i] is the unique set of inputs/individual record 
                        // batch[i].input gives access to the field value 
                        var in0 = gs.nil(batch[i].input) ? '' : batch[i].input; 
                        // output[i] is the output for the specific combination of inputs/individual record 
                        output[i] = in0 + " modified by script"; 
                    } 
                } 
            })(batch, output); 
Example:
/* Example Script
 (function(batch, output) {
     for (var i = 0; i < batch.length; i++) {
         //step1: access the input variables
         var a = batch[i].input; //Value of the source field.
 
         //step2: Your script/code goes here.
         var b = a + 1;
         //step3: set the output for each elements
         output[i] = b;
     }
 })(batch, output);
*/ 

Set 

Sets the target_sys_rte_eb_field field value to the string provided in the set_value field.

Details
Table RTE Entity Set Operation [sys_rte_eb_set_operation]
Output field target_sys_rte_eb_field

Output is the value associated with the set_value field.

Additional fields
  • set_value (string)
  • overwrite_existing_value (optional, Boolean) : When true, the current value of the target field is overwritten. Otherwise, a non-empty value isn't replaced.

Set Min/Max

Sets the target field to either the maximum or minimum of the values from all input fields.

Details
Table RTE Entity Min/Max Operation [sys_rte_eb_min_max_operation]
Input field source_sys_rte_eb_fields

Input is a set of values.

Output field target_sys_rte_eb_field

Output is the maximum or minimum value based on the min_max value.

Additional fields
  • data_type (choice list with values as STRING, NUMERIC, and DATE)
  • min_max (choice list with values as MIN and MAX) 

Split 

Splits the string included in the source_sys_rte_eb_field input value at the separator specified in the splitting_string field and assigns the resulting array of strings to the target_sys_rte_eb_field field, in order.

Details
Table RTE Entity Split Operation [sys_rte_eb_split_operation]
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_fields

Output is list of substrings.

Additional fields splitting_string (string) 
Table 12. Example
Input Result
"value1||value2||value3", splitting_string:"||" with target_sys_rte_eb_fields {target1,target2,target3} target1 : value1, target2 : value2, target3 : value3  
"value1||value2||value3", splitting_string:"||" with target_sys_rte_eb_fields {target1} target1 : value1 
"value1", splitting_string:"||" with target_sys_rte_eb_fields {target1,target2,target3} target1 : value1, target2 : <null>, target3 : <null>

Trim 

Removes any whitespaces at the beginning and at the end of the string included in the source_sys_rte_eb_field input value and assigns the result to the target_sys_rte_eb_field field.  This transform is equivalent to the Java String trim() Method.

Details
Table RTE Entity Trim Operation [sys_rte_eb_trim_operation
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_field

Output is the input string value but without any leading and trailing spaces.

Table 13. Example
Input Result
" value 1 " "value 1"

Uppercase

Changes all characters of the source_sys_rte_eb_field input value to upper case and assigns the result to the target_sys_rte_eb_field field.

Details
Table RTE Entity Upper Case Operation [sys_rte_eb_upper_case_operation]
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_field

Output is the upper case string value.

Table 14. Example
Input Result
"value1" "VALUE1"

Uppercase Trim 

Combines both the Uppercase and the Trim transforms.

Details
Table RTE Entity Upper Case Trim Operation [sys_rte_eb_upper_case_trim_operation]
Input field source_sys_rte_eb_field

Input is a string value.

Output field target_sys_rte_eb_field

Output is the upper case string value without any whitespaces at the beginning and end.

Table 15. Example
Input Result
"      value1    " "VALUE1"