The creation of a custom table allows users to compile additional contact information outside of the project, thus reducing database overloading.
For example, you could use a custom table to keep track of your contacts' consuming history or activities. When a project includes custom tables, it is possible to search these tables to find the corresponding records instead of finding related contacts. Therefore, searching a custom table finds in the table the records that correspond to the specified criteria. In addition, you can display information on the contact related to this data.
|Prerequisite||The custom table module must be activated to take advantage of the related functions.|
|Access Path|| > Configuration > Relational data|
Creating a custom table requires multiple steps:
- In the page listing the existing custom tables, click on Create a custom table.
- In the creation window, specify the name and the code of the new table and then click on Add.
Once the table is created, you need to add fields to it, which can be done right after creation or later on.
When configuring the table's fields, it is important to take into consideration the following points:
• The table MUST contain at least one field defined as a primary key.
• If the primary key is composed of only one field, this field must also be required.
• The table MUST contain at least one field defined as a foreign key.
- Once the table is created, click on Add a field.
- Specify information about the new field.
|Primary key||Check box that lets you indicate whether the field is part of the primary key, that is if the value entered in this field is part of the contact unique identifier. Fields that are part of the primary key show the related icon. If however the primary key is composed of only one field, this field will also be required, flagged with the icon.|
For example, if you indicate that the CompanyCode field is part of the primary key, each record in the table will need to have a unique and distinct company code.
|Name||Name that displays as a column header in the custom table.|
The name must:
|Data type||The data type lets you define the format of data valid in the field. The default data type is BigInt since it is the first in the list.|
Description of data types
|Visible by default in user interfaces||Check box option used to indicate whether the field is displayed by default in the custom table.|
|Is required||Check box option used to indicate if it is required to enter a value in this field.|
|Default value||Default value to assign to the field, required or not, when no value has been provided. When the field is not required and no data has been provided, providing a default value just prevents the field from being empty. When a default value is defined, this value is assigned to ALL the records that do not have a value in the related field.|
|Foreign key||Check box option used to indicate if the field is part of the foreign key. At least one foreign key must be defined in order to associate the table field to a project field, which displays the table field value in the contact information card. The fields that can be associated depend on the type of data selected, as the data type must be the same for both fields (in the table and in the project).|
- Click on OK
- Repeat the previous steps for each field to add to the custom table.
- Once you have added all the desired fields to the table, you need to build the structure of the table and the data to include by clicking on Build.
A built table is always considered in preparation and cannot be used until it is published.
Custom tables offer additional configuration settings, all optional, that let you specify how data from the tables should be integrated in the application. The table below lists all the options offered under Configuration > Relational data > Configuration tab. Once the options have been selected, it is important to save your changes so that the custom tables are integrated as defined.