Change a column type

By default, you can only specify the column type when creating a column. After it is created, the Column Type field is read-only.

However, there is a workaround to change the column type after a column is created. The original column type and the new one are not always compatible without using a script.

The following types can be changed without using a script:

• String → URL
• String → Text
• Text → Translated text
• String → Translated text
• Date/Time → Date/Time Specific
• Field Name → Reference
• Reference → List
• Reference → Field Name
• Reference → Choice

Use scripts to convert data of different column types. The following examples illustrate some common transformations:

Date → Date/Time:

const record = new SimpleRecord('table_name');
record.addQuery('date_field', 'isnotempty');
record.query();
record.silentMode(true);
while (record.next()) {
  record.datetime_field = record.date_field + ' 00:00:00';
  record.update();
}

Date/Time → Date:

const record = new SimpleRecord('table_name');
record.addQuery('datetime_field', 'isnotempty');
record.query();
record.silentMode(true);
while (record.next()) {
  record.date_field = record.datetime_field.slice(0, 10);
  record.update();
}

To change the column type for many records, use the quick import of a JSON file. For example, if you need to change the type of the Start Date [start_date] column from Date to Date/Time, complete the following steps:

1. Export records from the table containing start_date column in JSON format.
2. Create a copy of the obtained JSON file.
3. In the file copy, change the row values for the start_date property. The new values must contain the time part { 00:00:00}. It is recommended to use multiple cursor functionality, if it is available in your text editor.
4. Save the changes to the JSON file.
5. Remove the start_date column from the table.
6. Create a new start_date column of type Date/Time.
7. Perform the quick import of the JSON file created in steps 1–4.

Change a column name

By default, the column name can only be specified when creating a column. After a column is created, the Column Name field becomes read-only. It prevents collisions in the work of business logic records (scripts and fields of Conditions type) that refer to the Column Name attribute.

However, there is a workaround to change a column name after a column is created.

Start by searching for the business logic records that use the Column Name attribute of the column you need to rename. To do this, complete the following steps:

1. Navigate to Configuration → VCS Records.
2. In the condition builder, specify two conditions:
   1. Is Current is Yes.
      AND
   2. JSON Copy contains column_name (replace with the column name you need to change).

Next, change in sequence all occurrences of the current column name in all objects specified in the Document Record field of the VCS records you find.

Finally, create a new column with the name you need and import the contents of the column you need to rename into the newly created column. For example, if you need to change the name of the Start Date column from [start_date] to [start_date_time], complete the following steps:

1. Export records from the table containing start_date column in JSON format.
2. Create a copy of the obtained JSON file.
3. In the file copy, search for the property value start_date and replace it with start_date_time. It is recommended to use multiple cursor functionality, if it is available in your text editor.
4. Save the changes to the JSON file.
5. Remove the start_date column.
6. Create a new start_date_time column.
7. Perform the quick import of the JSON file created in steps 1–4.