Skip to content

Migrate data into Custom Fields

In some cases, Custom Fields functionality can be used to replace what had previously been done as customizations. In such cases it might be necessary to migrate data from old customizations into new custom fields. This page will show a few different scenarios where data is migrated into IFS Cloud Logical Units with custom fields.

Custom Field Migration Scenarios

  • Migrate data from a file

A file containing data for an LU with custom fields are migrated into the LU and its custom fields.

  • Migrate data from an LU with added columns

A customization has added columns to an LU. The data is migrated from the old LU to the standard version and the columns that was added to the LU is migrated into custom fields.

  • Migrate data from two LUs

A customization has added columns to an LU in a second LU (connected by primary key - all added columns is located in one row in the second LU). The data is migrated from the old LU to the standard version and the columns in the second LU is migrated into custom fields.

  • Migrate data from an LU and a generic LU

A customization has added columns to an LU in a generic LU (connected by primary key - each added column is located in its own row in the second LU). The data is migrated from the old LU to the standard version and the columns in the generic LU is migrated into custom fields.

  • Migrate Custom Fields data only

A customization has added columns to an LU in a second LU (connected by primary key - all added columns is located in one row in the second LU). The data is migrated from the second LU into the Custom Fields - no data is migrated into the original LU.

  • Migrations with Custom References

Since Custom references are persisted as the objkeys of the referenced records, they cannot be migrated directly. Special handling has to be done to migrate data values as custom references and to migrate custom references between databases.

Custom Fields LU

A simple LU, Intface_Demo_User, is used as an example, The LU Intface_Demo_User has two columns added as Custom Fields, CITY and STREET.

When the Custom Fields are deployed a new package, INTFACE_DEMO_USER_CFP, is created. The package has a method, Cf_New__ that will be used to migrate Custom Fields values into the Intface_Demo_User LU.

The OBJID passed into the Cf_New__ method is the Intface_Demo_User OBJID. In the following data migration jobs (except the last one) two methods are invoked, INTFACE_DEMO_USER_API.New__/Modify__ (the LU package) and INTFACE_DEMO_USER_CFP.Cf_New__ (the LU's Custom Fields package). The OBJID are returned by the call to INTFACE_DEMO_USER_API.New__/Modify__ and used in the call to INTFACE_DEMO_USER_CFP.Cf_New__.

In the last data migration job, data is only migrated into the Custom Fields columns. INTFACE_DEMO_USER_API.New__/Modify__ are never called and the Intface_Demo_User OBJID is not available. To solve that problem a generic method has been added to Data Migration, Intface_Method_List_API.Get_Objid_By_Key. This method is used to find the OBJID value that must be passed to INTFACE_DEMO_USER_CFP.Cf_New__.

NOTE. The method Cf_New__ also handles updates of the Custom Fields values. There is no need to call Cf_Modify__.

Utility function returning OBJID

A function, Get_Objid_From_Key, that returns OBJID from a specified LU has been added to the Intface_Method_List_API package.

The method is generic and take as argument the name of the LU base view, a semicolon separated list of primary key column names, a semicolon separated list of primary key column values and a date format. Date format may be specified if any of the primary key columns is a DATE column and the values passed in is not in the standard date format (YYYY-MM-DD-HH24:MI:SS).

The method is called like INTFACE_METHOD_LIST_API.Get_Objid_From_Key('INTFACE_DEMO_USER','IDENTITY;','CFU1;'). View_name_ and key_columns_ values must be in upper case. Key_columns_ and key_values_ values are a semicolon separated list and must be ended with a semicolon.

Migrate data from a file

A file with data will be migrated into an LU with Custom Fields. The values in IDENTITY, DESCRIPTION, WEB_USER and ACTIVE shall be migrated into the standard columns of the LU and the values in STREET and CITY shall be migrated into custom fields columns.

The file is loaded into a container table, IC_LOAD_CUSTOM_FIELDS_1_TAB, with the data migration job LOAD_CUSTOM_FIELDS_1.

Now, the data is loaded into the container table and the next step is to migrate the data into the LU. The data is migrated with the data migration job MIGRATE_CUSTOM_FIELDS1.

The container table, IC_LOAD_CUSTOM_FIELDS_1_TAB, is entered as Source Name in the Formatting tab.

The Method List tab specifies the two methods that shall be used to insert (or update) the data in the LU. The first method, INTFACE_DEMO_USER_API, inserts (or updates) the standard columns in the LU. The second method, INTFACE_DEMO_USER_CFP.CF_NEW__ inserts (or updates) the custom fields columns. The second method also has a condition, Column Name = DUMMY and Column Value= % which means that the method INTFACE_DEMO_USER_CFP.CF_NEW__ will only be invoked if the column DUMMY contains a value.

The columns INFO_, OBJID_, ATTR_CF_, ATTR_ and ACTION_ are the parameters to the method INTFACE_DEMO_USER_CFP.CF_NEW__. The custom fields column values are packed into an attribute string. The attribute string must be passed to the parameter ATTR_CF_. This is done by specifying the value ATTR_ in Fixed Value. The method must also have the OBJID value. The OBJID value is returned by the method INTFACE_DEMO_USER_API(NEW__/MODIFY__) in the OBJID_ parameter of that method. The value OBJID_@10 in Fixed Value is a reference to the OBJID_ value returned by the method invoked in Execute Seq 10 in the Method List.

Note: Method list attributes for XXX_CFP.Cf_New__, should only contain the custom field attributes ( CF$_), INFO_, OBJID_, ATTR_CF_, ATTR_ and ACTION_. Other fields should be removed.

The columns IDENTITY, DESCRIPTION, WEB_USER, ACTIVE, VALID_FROM and VALID_TO in the container table are mapped to its corresponding columns in the LU. The columns STREET and CITY in the container table are mapped to its corresponding custom fields columns.

The concatenated value of the custom fields columns are mapped to the DUMMY column. This is for the condition above, Column Name = DUMMY and Column Value= %. If all custom fields columns are NULL then DUMMY will also be NULL and the method INTFACE_DEMO_USER_CFP.CF_NEW__ will not be invoked.

When the job is executed it will report running 10 INTFACE_DEMO_USER_API and 20 IC_MIGRATE_CUSTOM_FIELDS1_20. The reason that it will not report running 20 INTFACE_DEMO_USER_CFP.CF_NEW__ is that this is not a standard NEW/MODIFY method. A non standard method will be wrapped by Data Migration in a procedure and it is the name of the wrapping procedure that is reported.

Migrate data from an LU with added columns

In this case two columns, STREET and CITY has been added to an LU as a customization. Now the data in the old table shall be migrated into the standard LU (no customization) and the two added columns shall be migrated into two custom fields columns.

The table, INTFACE_DEMO_USER_OLD_TAB, contains the values that shall be migrated.

The two columns, STREET and CITY, added by the customization shall be migrated into custom fields.

Source Name is the Oracle object, table or view, that contains the data to be migrated.

The first method invoked in the Method List is a call to the LU INTFACE_DEMO_USER_API. This method will create or update the INTFACE_DEMO_USER object. The second method invoked is a call to the custom fields method INTFACE_DEMO_USER_CFP.CF_NEW__.

The columns STREET and CITY are mapped to the custom fields columns INTFACE_DEMO_USER_CFV.CF$_STREET and INTFACE_DEMO_USER_CFV.CF$_CITY. A column DUMMY is added and the concatenated values of STREET and CITY is mapped to it. The DUMMY column is only used in the condition in the Method List tab.

In theMethod List Attribute the OBJID_@10 is mapped to OBJID_ parameter in the INTFACE_DEMO_USER_CFP.CF_NEW__ method and ATTR_ is mapped to ATTR_CF_ in INTFACE_DEMO_USER_CFP.CF_NEW__. OBJID_@10 is a reference to the OBJID_ value returned by the method invoked in Execute Seq 10 in Method List(the OBJID_ value returned by the call to the INTFACE_DEMO_USER.NEW__ or INTFACE_DEMO_USER.MODIFY__ method). The value is used by the custom fields LU to create the connection between the INTFACE_DEMO_USER object and the custom fields object. A custom fields CF_NEW__ method has two attribute strings - the mapping to ATTR_CF_ ensures that the custom fields values are passed in the correct attribute string.

Remember, method list attributes for XXX_CFP.Cf_New__, should only contain the custom field attributes ( CF$_), INFO_, OBJID_, ATTR_CF_, ATTR_ and ACTION_. Other fields should be removed.

When the job is executed it will report running 10 INTFACE_DEMO_USER_API and 20 IC_MIGRATE_CUSTOM_FIELDS2_20. The reason that it will not report running 20 INTFACE_DEMO_USER_CFP.CF_NEW__ is that this is not a standard NEW/MODIFY method. A non standard method will be wrapped by Data Migration in a procedure and it is the name of the wrapping procedure that is reported.

The two rows in INTFACE_DEMO_USER_OLD_TAB has been added to standard table INTFACE_DEMO_USER_TAB and the custom fields table INTFACE_DEMO_USER_CFT.

Migrate data from two LUs

In this case two columns, STREET and CITY has been added to a second LU. Now the data in the two old tables shall be migrated into the standard LU (no customization) and a custom fields LU.

The tables, INTFACE_DEMO_USER_OLD2_TAB and INTFACE_DEMO_USER_OLD3_TAB, contains the values that shall be migrated. The two tables have a common primary key column, IDENTITY.

The tables, INTFACE_DEMO_USER_OLD2_TAB and INTFACE_DEMO_USER_OLD3_TAB, are joined over the key column IDENTITY.

Source Name holds the value "INTFACE_DEMO_USER_OLD2_TAB A, INTFACE_DEMO_USER_OLD3_TAB B".

The first method invoked in the Method List is a call to the LU INTFACE_DEMO_USER_API. This method will create or update the INTFACE_DEMO_USER object. The second method invoked is a call to the custom fields method INTFACE_DEMO_USER_CFP.CF_NEW__.

The columns STREET and CITY are mapped to the custom fields columns INTFACE_DEMO_USER_CFV.CF$_STREET and INTFACE_DEMO_USER_CFV.CF$_CITY. A column DUMMY is added and the concatenated values of STREET and CITY is mapped to it. The DUMMY column is only used in the condition in the Method List tab.

The Source Columns are prefixed with the table alias entered in Source Name in the Formatting tab.

In the Method List Attribute the OBJID_@10 is mapped to OBJID_ parameter in the INTFACE_DEMO_USER_CFP.CF_NEW__ method and ATTR_ is mapped to ATTR_CF_ in INTFACE_DEMO_USER_CFP.CF_NEW__. OBJID_@10 is a reference to the OBJID_ value returned by the method invoked in Execute Seq 10 in Method List(the OBJID_ value returned by the call to the INTFACE_DEMO_USER.NEW__ or INTFACE_DEMO_USER.MODIFY__ method). The value is used by the custom fields LU to create the connection between the INTFACE_DEMO_USER object and the custom fields object. A custom fields CF_NEW__ method has two attribute strings - the mapping to ATTR_CF_ ensures that the custom fields values are passed in the correct attribute string.

Remember, method list attributes for XXX_CFP.Cf_New__, should only contain the custom field attributes ( CF$_), INFO_, OBJID_, ATTR_CF_, ATTR_ and ACTION_. Other fields should be removed. <

The Information Message displayed when the job is finished shows the name of the joined tables and the join condition.

The two rows in INTFACE_DEMO_USER_OLD2_TAB has been added to standard table INTFACE_DEMO_USER_TAB and the two rows in INTFACE_DEMO_USER_OLD23_TAB has been added to the custom fields table INTFACE_DEMO_USER_CFT.

Migrate data from an LU and a generic LU

In this case two columns, STREET and CITY has stored in a generic LU which can hold any column_name/column_value pair. The data in the old table INTFACE_DEMO_USER_OLD4_TAB shall be migrated into the standard LU (no customization) and the data in the generic table INTFACE_DEMO_DATA_TAB shall be migrated into the custom fields LU.

The tables, INTFACE_DEMO_USER_OLD4_TAB and INTFACE_DEMO_DATA_TAB, contains the values that shall be migrated.

The tables, INTFACE_DEMO_USER_OLD4_TAB and INTFACE_DEMO_DATA_TAB, are joined over the key columns IDENTITY and ID.

Source Name holds the value "INTFACE_DEMO_USER_OLD4_TAB A, INTFACE_DEMO_DATA_TAB B".

The first method invoked in the Method List is a call to the LU INTFACE_DEMO_USER_API. This method will create or update the INTFACE_DEMO_USER object. The second method invoked is a call to the custom fields method INTFACE_DEMO_USER_CFP.CF_NEW__.

The COLUMN_VALUE is mapped to INTFACE_DEMO_USER_CFV.CF$_STREET or INTFACE_DEMO_USER_CFV.CF$_CITY depending on the value in COLUMN_NAME. A column DUMMY is added and COLUMN_VALUE is mapped to it. The DUMMY column is only used in the condition in the Method List tab.

In the Method List Attribute the OBJID_@10 is mapped to OBJID_ parameter in the INTFACE_DEMO_USER_CFP.CF_NEW__ method and ATTR_ is mapped to ATTR_CF_ in INTFACE_DEMO_USER_CFP.CF_NEW__. OBJID_@10 is a reference to the OBJID_ value returned by the method invoked in Execute Seq 10 in Method List(the OBJID_ value returned by the call to the INTFACE_DEMO_USER.NEW__ or INTFACE_DEMO_USER.MODIFY__ method). The value is used by the custom fields LU to create the connection between the INTFACE_DEMO_USER object and the custom fields object. A custom fields CF_NEW__ method has two attribute strings - the mapping to ATTR_CF_ ensures that the custom fields values are passed in the correct attribute string.

Remember, method list attributes for XXX_CFP.Cf_New__, should only contain the custom field attributes ( CF$_), INFO_, OBJID_, ATTR_CF_, ATTR_ and ACTION_. Other fields should be removed.

When the job is executed it will report running 10 INTFACE_DEMO_USER_API and 20 IC_MIGRATE_CUSTOM_FIELDS4_20. The reason that it will not report running 20 INTFACE_DEMO_USER_CFP.CF_NEW__ is that this is not a standard NEW/MODIFY method. A non standard method will be wrapped by Data Migration in a procedure and it is the name of the wrapping procedure that is reported.

Due to the nature of the join statement you will also see Updated rows in 10 INTFACE_DEMO_USER_API.

Three rows has been added to the LU table INTFACE_DEMO_USER_TAB and two rows has been added to the custom fields table INTFACE_DEMO_USER_CFT.

Migrate Custom Fields data

This example migrates only custom fields data. The custom fields data is available in a table that also have the primary key value to the LU to which the custom fields data belong. The custom fields data is inserted into the database using the Cf_New__ method in the Custom Fields package. The Cf_New__ method doesn't use the primary key of the LU to which the custom fields data belong. The Cf_New__ method must have the OBJID value present. The Data Migration package Intface_Method_List_API has a generic function Get_Objid_From_Key that will return the OBJID from any LU.

A table,  INTFACE_DEMO_USER_OLD5_TAB, contains the values that will be migrated into the Custom Fields.

The table has three columns, IDENTITY which is the primary key column, STREET and CITY. STREET and CITY shall be migrated into the two Custom Fields columns.

Source Name is the Oracle object, table or view, that contains the data to be migrated.

The first method invoked in Method List is a call to Intface_Method_List_API.Get_Objid_From_Key. The method will return the OBJID value that must be present in the second call to Intface_Demo_User_CFP.Cf_New__ method which will migrate the data into the Custom Fields.

The arguments to Intface_Method_List_API.Get_Objid_From_Key is mapped: the string value 'INTFACE_DEMO_USER' is mapped to VIEW_NAME_, the string value 'IDENTITY;' is mapped to KEY_COLUMNS_ and the column IDENTITY is concatenated with a ';' and mapped to KEY_VALUES_.

NOTE! The values mapped to KEY_COLUMNS_ and KEY_VALUES_ must be ended with a semicolon. If the LU's primary key contains more then one column specify the column names in KEY_COLUMNS_ like 'COMPANY;CUSTOMER_NO;' and map the corresponding columns to KEY_VALUES_, COMPANY||';'||CUSTOMER_NO||';'.

For each row in the INTFACE_DEMO_USER_OLD5_TAB table the method Intface_Method_List_API.Get_Objid_From_Key will be invoked and return the OBJID value in the Intface_Demo_User LU.

The Custom Field columns will have the prefix CF$_ added to its name. The columns STREET and CITY are mapped to CF$STREET and CF$CITY.

The data migration job will create and attribute string containing the mapped values, STREET and CITY. This attribute string must be passed to INTFACE_DEMO_USER_CFP.Cf_New__ method in the ATTR_CF_ parameter. This is specified in the Method List Attribute window with the Fixed Value ATTR_. The method Intface_Method_List_API.Get_Objid_From_Key is a function and the return value from a function is held in the variable FUNCTION_RESULT. So, the value from the function invoked in Execute Seq = 10 is available in FUNCTION_RESULT@10.

Remember, method list attributes for XXX_CFP.Cf_New__, should only contain the custom field attributes ( CF$_), INFO_, OBJID_, ATTR_CF_, ATTR_ and ACTION_. Other fields should be removed.

Now, all the necessary mappings are in place and the data migration job can be executed.

When the job is executed it will report running 10 IC_MIGRATE_CUSTOM_FIELDS5_10 and 20 IC_MIGRATE_CUSTOM_FIELDS5_20. The reason that it will not report running 10 Intface_Method_List_API.Get_Objid_From_Key and 20 Intface_Demo_User_CFP.Cf_New__ is that these two methods are not standard NEW/MODIFY methods. A non standard method will be wrapped by Data Migration in a procedure and it is the name of the wrapping procedure that is reported.

Two rows has been added to the custom fields table INTFACE_DEMO_USER_CFT.

Migrations with Custom References

In the case of custom references, some changes have to be made to the above described procedures in custom field migrations. After the required changes, the custom reference fields can be treated as any other custom field and all the procedures described above can be applied to them.

Migrate data into custom references

Custom references are stored in the Custom Fields table (_CFT) as OBJKEYs of the referenced records. Therefore data values cannot be migrated directly into the custom reference attributes. The corresponding OBJKEYs have to be found first using the key of the referenced logical unit and then migrated into the _CFT table. The entity method Get_Objkey can be used in the migration job method list to retrieve the OBJKEY from the key. And the result of that should be used in place of the custom reference attribute in the method list attribute setup, for the method Xxx_CFP.Cf_New__.

Migrate custom references between databases

The OBKEYs for custom references cannot be migrated to a destination database directly. Instead the logical key values should be extracted at the source database and when migrating into the destination database those keys should be handled as mentioned in migrating data into custom references. For extracting the primary keys, the data source should be joined with the base views of custom references by the OBJKEYs.