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 the 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);

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 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 table.

To perform the transformation, do the following:

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 table.

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

• user – the name of the user dictionary present on the instance.
• display_name – the name of the column containing the user's full name (this column is present in the user dictionary).
• imp_ceo – the 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 the fields of type Choice when importing data, you 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 table 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 is 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);

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