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

How To

Creating a custom table requires multiple steps:

Define the custom table
  1. In the page listing the existing custom tables, click on Create a custom table.
  2. 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.

Add and configuring fields

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.

  1. Once the table is created, click on Add a field.
  2. Specify information about the new field.
Description of data related to custom table fields
Field Description
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:
  • Start with a letter
  • Contain only alphanumerical characters or underscores (_)
  • Not contain any space
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
Data Type Description
BigInt The value must be an integer between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
Bit The value must be 0 or 1.
Date The value must be a date in the yyyy.MM.dd format.
DateTime The value must be a date with a time, with seconds, in the yyyy.MM.dd HH:mm:ss format.
Decimal The value must be a valid decimal number.
With this type of data, it is also possible to define the Precision and Scale fields. The precision is used to specify the maximum number of numbers and the scale, the maximum number of numbers after the decimal point. For example, if the precision is 6 and the scale is 2, the possible values will vary between -9999.99 to 9999.99.
Int The value must be an integer between -2,147,483,648 and 2,147,483,647.
Money The value must be a valid decimal number between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 .
Nchar The value must be a string of characters whose size is smaller or equal to the length of the field. The length of the field can also be defined.
NVarChar The value must be a string of characters whose size is smaller or equal to the length of the field. The length of the field can also be defined and can be infinite (that is extremley large).
SmallDateTime The value must be a date with a time, without seconds, in the yyyy.MM.dd HH:mm.
SmallInt The value must be an integer between -32,768 and 32,767.
SmallMoney The value must be a valid decimal number between - 214,748.3648 and 214,748.3647.
Time The value must be a time in the HH:MM:SS format.
TinyInt The value must be an integer between 0 and 255.
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).
  1. Click on OK
  2. Repeat the previous steps for each field to add to the custom table.
Build the table
  1. 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.

Other configuration settings

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.

Configuration setting Description
Show menu entry dedicated to this table, with links to add and search features This option displays the table in the left pane menu.

Include a link to this table in the "Database" menu This option adds a link to the table in a section called Database in the left pane menu. In addition, you can specify which page to display when this link is selected, the page to add a field or the page to perform a search.

Display a form in the quick search Activating this option adds the table in the shortcuts in the right pane, allowing you to quickly perform a search amongst the table fields.

Add a Timestamp field This option is reserved to the exclusive usage of the development team to facilitate synchronization between custom tables and the client's external data.
Include a link to this table in the "Custom tables" section of the contact profile When this option is selected, a section called Relational data is added to the contact information card and lists all related custom table data.