No matter how thoughtfully you plan your database structure, there is a chance that you may keep reconsidering it later. Your business process may evolve slightly, and your vision of the structure may evolve as well.
For example, some fields in your database were initially planned to be filled with the data that is not going to change. While using the database, you may realize that some of these values are changing constantly, and you’d better organize them as the “details” linked to the main record. In this post we will explain how to grab columns with the data, move them to a separate table and link to the main table as “details”.
For example, you have the Clients table keeping info about clients.
There is the “Referred by” section on the Client form. It includes the following fields: Consultant, Partner and Internet.
Moreover, each field is a reference column that refers to the other table. The “Consultant” reference column refers to the “Consultants” table, “Partner” column refers to the “Partners” table, “Internet” column – to the “WebSites” table.
Initially you planned to enter only a single value to the “Referred by” section. Soon after that you realized that a client can be referred by more than one consultant or a partner or an internet source. So you decided to move these columns in a separate table, named “Referred by” and relate this table to the Clients. As the result, the “Referred by” info will be displayed as details of a Client record. The difficulty is that the Clients table already has many records and you may want to keep this existing data linked accurately after the columns moving.
To move columns with the data into a separate table with a link to the main table, you should:
At first, create a Table view including the columns you want to move. Besides the Consultant, Partner and Internet reference columns it’s important to include the Client Id column into the created table view. All reference columns keep Id numbers that will be displayed in the table view.
After that, you should export the view data to the CSV file. Just click on the “Export” button.
Now you can import this CSV file to the newly created “Referred by” table.
If you imported reference column Id numbers, please be sure that they are imported as a text. So the Text column type has to be chosen.
When the data is imported, you should create relations to match Id numbers with the corresponded records. Also you can delete all the columns you moved from the Clients table.
To create the relation between the “Referred by” and the “Clients” table, go to the Setup, select the “Referred by” table, click on the “Manage relations” link and click on the “New Relation” button.
Choose the “Clients” table as the Second Table.
Select the relation direction.
Choose the existing “Client Id” column as the reference column in the Relation.
Select the Details View to display “Referred by” records as Client details and save the Relation.
As the result, the “Client Name” lookup column will be created automatically and client Id numbers will be replaced by client names.
Create similar relations for the Consultant, Partner and Internet reference columns. When it is done, the Id numbers will be replaced with the lookup columns.
Now each “Referred by” record is related to a Client and displayed under a client form as details, so you can add as many “Referred by” details as you need.
