Transformation Reference
Transformations Types of mappings used to modify data before migrating it.Transformations include concatenation, split, replace, substring, assignment, and date modifications. are used to transform source data before importing it. Transformations cannot be defined through the user interface of the Import Data Wizard. They must be defined in a data map A file that contains information about how data from a source system corresponds to data in Microsoft Dynamics CRM Online..
There are two types of transformations:
- String transformations: transform string data to be used in string fields, or lookup fields Fields used to create a reference to a related record. Also known as a relationship attribute..
- Date transformations: transform dates to be used in datetime types of fields.
Transformations are used inside an <EntityMap> element in a data map.
- Each input is either a fixed string or the value of a column in the source file referenced in the <EntityMap>.
- The output is mapped to one or more Microsoft Dynamics CRM Online fields in the target record type.
This topic explains the parameters for each transformation, and provides an example of each transformation. For descriptions of each XML element shown in the examples, see Data Map Schema Reference.
Important
Parameters and XML elements in each transformation mapping must be used in the order shown in the examples.
Steps for using a transformation
To use a transformation:
- Use the Import Data Wizard to do a test import. Map all the files and columns, and import the test data.
- Delete records associated with the test import.
- Export the data map for the test import, edit it to add any transformations you need, and then import it.
- Run the Import Data Wizard again in using the data map.
String Transformations
Unless specified as optional, all parameters are required.
Concatenates several input columns into one. Can also add a prefix and suffix.
Input Parameter Sequence |
Description |
|---|---|
1 (optional) |
Prefix to add at the beginning of the string. |
2 (optional) |
Suffix to add at the end of the string. |
3 (optional) |
Delimiter to put between concatenated data. If not specified, a space is used. Valid values: space, comma and space, semi-colon and space, or colon and space. |
4 |
Array containing one <Item> for each piece of data to concatenate. Each <Item> can either be a string or a reference to a source column. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online target field in which to put the concatenated string. |
Sample Data Map Using Concatenate
This sample shows concatenation of two source columns, "Source_building", and "Source_room_number", into the target address1_line2 field.
<Map Name="sample_contact_concatenate">
<Description>Sample map showing Assign Value Transformation</Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
</ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.Concatenate</TransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps/>
<ArrayInputParameterMaps>
<ArrayInputParameterMap>
<ParameterSequence>4</ParameterSequence>
<Items>
<Item>
<ParameterArrayIndex>0</ParameterArrayIndex>
<DataTypeCode>Reference</DataTypeCode>
<Data>Source_building</Data>
</Item>
<Item>
<ParameterArrayIndex>1</ParameterArrayIndex>
<DataTypeCode>Reference</DataTypeCode>
<Data>Source_room_number</Data>
</Item>
</Items>
</ArrayInputParameterMap>
</ArrayInputParameterMaps>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>address1_line2</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Splits one input column into two or more output attributes, 10 maximum.
Input Parameter Sequence |
Description |
|---|---|
1 |
Source column containing the data to split. |
2 |
Delimiter in the input string that separates the data. Can be a space, comma, semi-colon, or colon. |
Output Parameter Sequence |
Description |
|---|---|
1 to 10 |
Microsoft Dynamics CRM Online target field in which to put the string containing the split data. |
Sample Data Map Using Split
This sample shows splitting of the source column, "Source_fullname", into the target firstname, middlename, and lastname attributes.
<Map Name="sample_contact_split">
<Description>Sample map showing Split Transformation</Description>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
</SourceAttributeName>Source_lastname</SourceAttributeName>
</TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.Split</TransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Reference</DataTypeCode>
<Data>Source_fullname</Data>
<SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>2</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data></Data>
<SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>firstname</Data>
</OutputParameterMap>
<OutputParameterMap>
<ParameterSequence>2</ParameterSequence>
<Data>lastname</Data>
</OutputParameterMap>
<OutputParameterMap>
<ParameterSequence>3</ParameterSequence>
<Data>lastname</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</Map>
Finds a substring of specified length starting at a specified point in the string.
Input Parameter Sequence |
Description |
|---|---|
1 |
Source column in which to find a substring. |
2 |
Position in the new string where the substring starts. |
3 |
Length of substring. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online field in which to put the substring. |
Sample Data Map Using Substring
This sample shows creating a substring, new_file_folder_label, based on the first four characters of a contact's last name.
<Map Name="sample_contact_substring">
<Description>Sample map showing Substring</Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.Substring</TransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Reference</DataTypeCode>
<Data>Source_lastname</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>2</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>0</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>3</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>4</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>new_filefolderlabel</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Replaces all occurrences of a specified string with another specified string. The replaced string can be all or part of the data for one column. All parameters are case-sensitive.
Input Parameter Sequence |
Description |
|---|---|
1 |
Source column containing the string to replace. |
2 |
String to replace. |
3 |
Replacement string. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online field in which to put the replacement value. This value includes the complete data for the column with the search string replaced by the input string. |
Sample Data Map Using Replace
This sample shows replacing "Washington" in the source "State" column with "WA", and putting the result in the target address1_stateorprovince attribute.
<Map Name="sample_contact_replace">
<Description>Sample map showing Replace Transformation</Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap<specialCharacter name="greater_than"/>
<TransformationTypeName>Microsoft.Crm.Transformations.Replace</TransformationTypeName>
<ProcessCode>Process<ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Reference</DataTypeCode>
<Data>state</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>2</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>Washington</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>3</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>WA</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>address1_stateorprovince</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Replaces all values with the specified value.
Input Parameter Sequence |
Description |
|---|---|
1 |
Value to assign to all records for the attribute specified in the output parameter. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online field to update with the specified value. The field can be a string field or a lookup field. If the output is a lookup field, a <LookUpMaps> section must be included in the data map. |
Sample Data Map Using Assign
This sample shows assigning the value of the target address1_stateorprovince attribute to "Washington" for all contact records.
<Map Name="sample_contact_assign_value">
<Description>Sample map showing Assign Value Transformation</Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.AssignValue</TransformationTypeName>
</ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>WA</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>address1_stateorprovince</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Sample Assign Transformation with Output as a Lookup Field
This sample shows assigning "Primary Unit" for all product records.
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.AssignValue</TransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>Primary Unit</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>uomid</Data>
<LookupMaps>
<LookupMap>
<LookupEntityName>uom</LookupEntityName>
<LookupAttributeName>name</LookupAttributeName>
<LookupType>System</LookupType>
<ProcessCode>Process</ProcessCode>
</LookupMap>
</LookupMaps>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
Top of pageDate Transformations
Unless specified as optional, all parameters are required.
Adds a specified number of days, months, and years to a date.
Input Parameter Sequence |
Description |
|---|---|
1 |
Date. The source column containing the date to transform. |
2 |
Year offset. The value to add to the year. Can be positive or negative. |
3 |
Month offset. The value to add to the month. Can be positive or negative. |
4 |
Day offset. The value to add to the day. Can be positive or negative. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online field in which to put the new date string. |
Note that the values are added in order: first the day (parameter 4) is added, then the month (parameter 3), and then the year (parameter 2).
Sample Add to Date Transformation
This sample shows adding three months to the initial_contact_date and stores the information in the new_followup_date attribute.
<Map Name="sample_contact_add_to_date">
<Description>Sample map showing Add To Date Transformation</Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.AddToDate</TransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Reference</DataTypeCode>
<Data>Source_initial_contact_date</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>2</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>0</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>3</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>3</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>4</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>0</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>new_followup_date</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Adds a specified number of days, months, and years to the current date, and sets specified time.
Input Parameter Sequence |
Description |
|---|---|
1 (optional) |
Year offset. The value to add to the year. Can be positive or negative. |
2 (optional) |
Month offset. The value to add to the month. Can be positive or negative. |
3 (optional) |
Day offset. The value to add to the day. Can be positive or negative. |
4 (optional) |
Date and time. Set the hours component of date string to this value. |
5 (optional) |
Minutes. Set the minutes component of date string to this value. |
6 (optional) |
Seconds. Set the seconds component of date string to this value. |
7 (optional) |
Day of week. Set the day of week component of date string to this value. If this value contradicts the day calculated using the value from parameter 3, the day will be set to the specified day of week, and will use the closed day just prior to the calculated day. Valid values: empty, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online datetime attribute in which to put the new date. |
Sample Add to Current Date Transformation
This sample shows setting the target new_review_date to three months from the current date.
<Map Name="sample_contact_add_to_currentdate">
<Description>Sample map showing Add To Current Date Transformation</Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap>
<TransformationTypeName>Microsoft.Crm.Transformations.AddToCurrentDate</TransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>2</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>3</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>3</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>4</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>4</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>5</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>5</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>6</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>7</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>3</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>new_review_date</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Adds a specified number of days, months, and years to the current date. You can specify whether offsets are relative to the current date or absolute values.
Input Parameter Sequence |
Description |
|---|---|
1 |
Year offset. The value to add to the year. Can be positive or negative. |
2 |
Year offset type. Whether the value in parameter 1 is relative to the current year or is an absolute value, such as 2008. Valid values: 1 (Relative to Current Date), 2 (Absolute Value). |
3 |
Month offset. The value to add to the month. Can be positive or negative. |
4 |
Month offset type. Whether the value in parameter 3 is relative to the current month or is an absolute value. Valid values: 1 (Relative to Current Date), 2 (Absolute Value). |
5 |
Day offset. The value to add to the day. Can be positive or negative. |
6 |
Day offset type. Whether the value in parameter 5 is relative to the current day or is an absolute value. Valid values: 1 (Relative to Current Date), 2 (Absolute Value). |
7 |
Hours. Set the hours component of date string to this value. |
8 |
Minutes. Set the minutes component of date string to this value. |
9 |
Seconds. Set the seconds component of date string to this value. |
Output Parameter Sequence |
Description |
|---|---|
1 |
Microsoft Dynamics CRM Online datetime attribute in which to put the new date. |
Sample Advanced Add to Current Date Transformation
This sample shows adding a year and day relative to the current year and day, and setting the month to 1 (January). The time is set to 1:01:01.
<Map Name="sample_contact_advanced_add_to_current_date">
<Description>Sample map showing Advanced Add To Current Date Transformation<Description>
<EntityMaps>
<EntityMap TargetEntityName="contact" SourceEntityName="Source_contact">
<AttributeMaps>
<AttributeMap>
<SourceAttributeName>Source_lastname</SourceAttributeName>
<TargetAttributeName>lastname</TargetAttributeName>
<ProcessCode>Process</ProcessCode>
<AttributeMap>
</AttributeMaps>
<TransformationMaps>
<TransformationMap <specialCharacter name="greater_than"/>
<TransformationTypeName>Microsoft.Crm.Transformations.AdvancedAddToCurrentDate<T/ransformationTypeName>
<ProcessCode>Process</ProcessCode>
<InputParameterMaps>
<SingletonInputParameterMaps>
<SingletonInputParameterMap>
<ParameterSequence>1</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>2</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>3</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>4</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>0</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>5</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>6</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>7</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>8</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
<SingletonInputParameterMap>
<ParameterSequence>9</ParameterSequence>
<DataTypeCode>Value</DataTypeCode>
<Data>1</Data>
</SingletonInputParameterMap>
</SingletonInputParameterMaps>
<ArrayInputParameterMaps/>
</InputParameterMaps>
<OutputParameterMaps>
<OutputParameterMap>
<ParameterSequence>1</ParameterSequence>
<Data>description</Data>
</OutputParameterMap>
</OutputParameterMaps>
</TransformationMap>
</TransformationMaps>
</EntityMap>
</EntityMaps>
</Map>
Notes
- You cannot use multiple transformations on the same columns. For example, you can't use the Concatenate transformation to combine the output of a Split transformation with another string or field.
- You cannot use transformations on drop-down list attributes Attributes that contain a set of values that a user must choose from. Also known as a picklist..
