Create the Staging Table
1) In the AOT create a staging table for the entity, with the following table properties:
2) Create fields with the following properties
The wizard creates these four fields, but the fields “IsSelected” and “TransferStatus“ are created without EDT/Enum. They must be inserted manually!
3) Create field groups with the following properties:
|Field group name
||DefinitionGroup, IsSelected, TransferStatus, ExecutionId
||OPTIONAL. Fields from the staging table which you want to make part of template by default
||Fields from staging table which are the source for the specified method (fields that are used in the method)
NB.: The field group “Generate” must be created even if there are no fields in them. If in the class is present the method “Generate”, the DMF table must have the associated field group (empty or not). For example, the table “DMFProductEntity” has a field group named GenerateItemGroupCompany_2”, that is empty, but the class “DMFProductEntityClass” has a method called “GenerateItemGroupCompany”.
4) Create a primary index for the table:
|<<any name>>Ex.: Idx
||DefinitionGroup, ExecutionId, fields from staging table to define uniqueness
5) Specify the relationship between the staging table and the target table
Create an Enum fields in the target entity
The Enum field in the target entity is represented by a string field in the staging table. You must create a new extended data type (EDT) of type string, and of appropriate length to take the enum label strings.
HcmEmployment.EmploymentType = Enum HcmEmploymentType
DMFEmployeeEntity. EmploymentType = Type String -> EDT = DMFEmploymentType
If the target table contains fields that are RecIds from other tables, you must convert the natural key to a RecId. There are two options:
Add a data source so that the referenced table can be added to the target entity query
Create a function
Add a datasource
For example, the target VendTable contains VendExceptionGroup, which is a RecId that comes from VendExceptionGroup Table.
In this case, the staging table must include
VendExceptionGroup. We add the table in the query, under VendTable datasource. The relationship must be specified manually.
The staging field DMFVendorEntity.VendExecptionGroup must be mapped to the target field query for DMFVendorTargetEntity . DS:VendExceptionGroup.VendExceptionGroup
Create a function
Create a method on the entity class to convert the string to a RecId. For example, the method DMFCustomerEntity.GenerateCompanyIdNAF:
public container GenerateCompanyIdNAF(boolean _stagingToTarget = true)
select firstOnly1 RecId from companyNAFCode where companyNAFCode.CompanyIdNAF == entity.CompanyIdNAF;
res = [companyNAFCode.RecId];
select firstOnly1 CompanyIdNAF, RecId from companyNAFCode where companyNAFCode.RecId == target.CompanyNAFCode;
res = [companyNAFCode.CompanyIdNAF];
When you create a function approach, you must create a field group for the staging table, and the return fields on the target must be specified in the getReturnFields method in the entity class.
case methodStr(DMFCustomerEntityClass, GenerateCompanyIdNAF) :
con += [fieldstrToTargetXML(fieldStr(CustTable, CompanyNAFCode))];
Create a class
1) In the AOT create a class for the entity with the following properties:
DMFClassName extends DMFEntityBase
Declare the object for the staging table with the name “entity”.
Declare the object for the main table for the target entity with the name “target”.
The following example shows how the DMFBEMATableEntityClass is declared.
public class DMFBEMATableEntityClass extends DMFEntityBase
2) Create the new method:
Must take the staging table as parameters
The value entity should be initialized from a parameter. The following example shows the DMFBEMATableEntity new method
public void new(DMFBEMATableEntity _entity)
entity = _entity;
3) Create the construct method:
Must take the staging table as a parameter
Must create and return the object of the current class by using a parameter. The following example shows the DMFBEMATableEntity construct method
public static DMFBEMATableEntityClass construct(DMFBEMATableEntity _entity)
DMFBEMATableEntityClass entityClass = new DMFBEMATableEntityClass(_entity);
4) Create the setTargetBuffer method:
A target entity can have multiple data sources. One of the data sources is the main table that represents the entity. In the setTargetBuffer method, the parameter _dataSourceName represents the data sources that are present in the target entity query
Depending on the data source, you may need to initialize a local instance of the table for the data source. The local instance can then be used in the functions that are required for data migration. The target should be initialized by the main table that represents the target entity
public void setTargetBuffer(Common _common, Name _dataSourceName = "")
case tableNum(BEMATable) :
target = _common;
5) Set the RunOn property of the class to the value CalledFrom
Write functions to import and export data
You can map data from the staging to the target table in two ways:
Assign fields: A field from staging is directly assigned to a field in target. In this case the data types for the staging and target fields must be same
Write a function to transform the field values from staging to target. You can write X++ functions to transform and map data from staging to target
Data import and export functions that you define must perform the following actions:
Input (Source): the entire staging record is available as a local variable to the class, so there is no need to pass any parameters to this class
Output (Target): as a result of executing the function, zero or multiple fields in the target entity are set. The return type of the function is a container, which can hold zero or more values to set on the target
The sequence of values that are returned by a particular function must be defined in the getReturnFields method.
The addStagingLink method is used to define the relationship between the staging and target table when it cannot be defined by using the relations property of the staging table.
The target query and the staging record are available in the method, so the range between target and staging can be added using code.
public static Query addStagingLink(Query query, TableId _entityTableId, Common _staging)
qbd = query.dataSourceTable(tableNum(HcmWorker));
The getReturnFields method is used to specify the default output or target fields for the functions that are used for data migration.
_entity: entity name
_name: function name
The function must return a container, as well as the name of the data source in the target entity query with which the method should be executed, and the name of the data source field in the target entity query which should be initialized by executing the function.
public static container getReturnFields(Name _entity, MethodName _name)
DataSourceName dataSourceName = queryDataSourceStr(DMFEmployeeTargetEntity, HcmEmploymentDetail);
Container con = [dataSourceName];
Name fieldstrToTargetXML(FieldName _fieldName)
case methodStr(DMFEmployeeEntityClass,GenerateReasonCode) :
con += [fieldstrToTargetXML(fieldStr(HcmEmploymentDetail, TransitionReasonCode))];
case methodStr(DMFEmployeeEntityClass,GenerateDefaultDimension) :
dataSourceName = queryDataSourceStr(DMFEmployeeTargetEntity, HcmEmployment);
con = [dataSourceName];
con += [fieldstrToTargetXML(fieldStr(HcmEmployment, DefaultDimension))];
con = conNull();
This tells the migration tool which table and field, the value passed back from the generate method should go.
When you create a custom entity, you must create a staging table, a project, a query, a class, and functions. You must always create the class and functions manually, but you can use one of two methods to create the staging table, project, and query:
- Use the Create a custom entity for data import/export wizard.
- Create the staging table and query manually
the query is a definition of a target table. You can add relationships if required for mapping
The table suffixed with “Entity” is the staging table to use for mapping
the foreign key using RecId. The EDT generated, is a string field representing the code
The class is where we can put business logic. For example, the EDT with RecId need to be resolved
Creation by using the “Create a custom entity for migration
- Data Import/Export Framework > Common > Create a custom entity for data import/export [Data migration framework > Common > Create a custom entity for migration] (2012)
- Select a table that is related to the entity that you want to migrate, and then click Next
- On the “Select code generation parameters”, the wizard will suggest names for the
staging table, query, and class
1) Prepare Source Data
The file must have a header, which includes the field names identifying each data column.
2) Define type of AX entity to be used
Define entities that will be loaded to AX. For example, Customers, Vendors…
3) Mapping and validation of source file fields to staging AX fields
The DMF tool will allow you to map each field from the source file to an AX field in the staging table. The DMF uses the field names to map the fields in the staging table.
4) Transferring data from source file to the staging table
Once fields from the source file have been mapped to AX fields in the staging table data should be ready to be transferred to the staging table.
5) Transfer of data from staging to target AX destination
Once data has been reviewed and validated in the stating table, the data is then moved to the target AXentity and AX records are created.
1) Determine entities – DMF > Setup > Target entities
There is a set of predefined entities. These entities also include procedures to move data from the staging table to the destination.
2) Define the source data format – DMF > Setup > Source data formats
- Create a new data format
- File format: delimited by a character or fixed
- First row header: indicate if first row of the file, is a header row
- Row / column delimiter: which character will be used to determine the end of the line / column
- Text qualifier: indicates characters to encase text and commas
- Regional settings: regional settings for the file
- Role separator: used to indicate multiple entries, for example: 317-509 ; 508-999
3) Define the data format for each entity in the source environment
The data format can be a file that is delimited or fixed width, an ODBC data source, or a Microsoft Dynamics AX table
4/5) Define the processing group / Source to staging table
- Create a new processing group
- Create a new entity and select the source data format
- Run business logic: two flags to enable the check for AX business logic
- Sample file path: the path of the file
- Generate source mapping: maps the source file against the staging table. If you use AX column names as your header, the DMF tool will automatically try to do the mapping
- Modify source mapping: the user can review and modify the mappings
- Validate: the validation ensure that all fields in the source file are correctly mapped to the staging table
- View target mapping: this option can be used to validate the mappings
- Preview source file: the user can view a preview of the source file before the file is copied to the staging table
- Get staging data: data is ready to be copied to the staging table. This button is used to start the process. A message indicate that the data has been copied to the staging table
6) Moving data from staging table to final ax destination
Select the “Copy data to target”.