Transform Data during Import

In SimpleOne, data from the import source can be transformed before being written into the fields of the target table.

For example, the maximum length value in a field of type Phone is 15 characters, and data from your import source contains the value +7 (495)-181-85-20, which exceeds the maximum allowed length. In this case, this data needs to be transformed to keep only the numbers in the target value. 

To set up the transformation and fill in the Phone field, in the related Field Map record, specify the following script to filter the phone number value.

answer = (function transformEntry(source) {
    const parsedDigits = source.imp_telephonenumber.match(/\d/g);
    return parsedDigits ? `+${parsedDigits.join("")}` : null; // E.164 format OR null
})(source);

where imp_telephonenumber is the name of the column containing the telephone number in the temporary _imp_ table. This table contains the data from the import source.

Transformation for Reference fields

Before transforming, you first need to import reference dictionaries.

For example, you need to transform a list of companies that contain CEOs in their records. On the instance, the CEO field in the Company target table is of type Reference. The values stored in the field are record IDs from the user dictionary.

Follow these general steps to perform the transformation:

1. Import the list of users. This list should also contain CEO users.

2. Import the list of companies. In the import source, the CEO field contains the user's full name.

3. To fill in the CEO field, in the related Field Map record, specify the following script to transform the user's full name into the respective ID of the user record from the user dictionary.
   
   

   answer = (function transformEntry(source) {
     const user = new SimpleRecord('user');
     user.get('display_name', source.imp_ceo);
     return user.sys_id;
   })(source);

   where:
   user – name of the user dictionary present on the instance.
   display_name – name of the column containing the user's full name (this column is present in the user dictionary).
   imp_ceo – name of the column containing the user's full name (this column is present in the temporary _imp_ table)

Transformation for Choice fields

To fill in fields of type Choice when importing data, you first need to create a choice option in the target column of type Choice.

For example, a new choice record for the Employment Status column in the Employee dictionary has the following value:  

{instance address}/record/sys_choice?field_table_id=156873090301469473&field_language=en&field_column_id=156873090304832536&field_title=Worker&field_value=worker&field_order=100

During the import process, the corresponding database_value will need to be found by the imported display_value.

The following script transforms the displayed value of the employment type into the corresponding choice option in the database.

answer = (function transformEntry(source) {

  // import source value : sys_choice value
  const employeeTypeDict = {
    "Worker": "worker",
    "Employee": "employee",
    "Self-employed": "self_employed",
    "Director": "director",
    "Contractor": "contractor"
  }
  return employeeTypeDict[source.imp_status] || source.imp_status
})(source);

where:
imp_status – name of the column containing the employment type in the temporary _imp_ table.