Importing Data into Reference Columns
When managing relational databases, you may need to import data that links one table to another. This is achieved using reference columns or multi-reference columns.
A reference column connects a record in one table to a single record in another table. For example, a Projects table might have a reference column to the Status table to indicate the status.
A multi-reference column connects a single record to multiple records in another table. For example, a Projects table might have a multi-reference column to the Products table to indicate all products needed in a project.
Importing Data into a Single-Reference Column
When importing values into a reference column, the key requirement is to import the IDs of the referenced records, not the text values.
-
Export the data from the reference table to retrieve the IDs of the records.
-
Create an import file for the target table. The first column must contain the Project key column value and the second column - Status key column value (ID in our example).
-
Import the file or copy/paste the data into the target table.
As a result, the reference column (e.g., Status) will display the respective value from the linked table.
Importing Data into a Multi-Reference Column
Importing data into a multi-reference column can be done as follows:
-
Export the data from the reference table to retrieve the IDs of the records.
-
Structure the data for import appropriately. This data must be imported into the linking table.
| Parent Record ID | Reference Record ID |
|---|---|
| Parent Record ID 1 | Reference Record ID 1 |
| Parent Record ID 1 | Reference Record ID 2 |
| Parent Record ID 1 | Reference Record ID 3 |
| Parent Record ID 2 | Reference Record ID 1 |
| Parent Record ID 2 | Reference Record ID 2 |
| Parent Record ID 2 | Reference Record ID 3 |
Below is an example of data for import, where the first column is the key column of the Project table and the second column contains the IDs of the products to be imported into the multi-reference column.
- Since the linking table is created automatically when the relationship is defined, it is not visible to users by default.
Go to Setup > Customize existing columns, select the multi-reference column. In the column settings you can find the link table. Open that table and import the data there.
As a result, the imported values will be displayed as the respective reference records.
