Navigation

Creating a Custom Table

At the heart of your project, there is a contact database. To compile additional information outside of that contact list, you could use a custom table. The objective is to separate data from the main database, thus reducing overloading. Custom tables are aimed at stocking data from imports. This approach improves the clarity and organisation of your project. You can thus create a custom table linked to your contact database (profiling table) or not linked (lookup table).

For example, you could use a custom table to keep track of your contacts' consuming history or activities. Some modules automatically generate custom tables, like the E-Commerce module.


Before Starting

Reflect on your import needs to determine the structure of your custom table: What type of data do you want to import? Which of your activities generate valueable data? When you will create your custom table, you will create fields that will be used to host the import data. 


How To

Start by clicking on Create a custom table:If you do not see the button Create a custom table, you may have to activate the module first or make a request to activate it. 
In the New window, specify the Code, Name and Type (profiling or lookup):

Understanding the difference between profiling and lookup tables

Profiling

A profiling table is linked to your project's contact database with a foreign key (a foreign key is mandatory with a profiling table). The foreign key is a field in the table, whose value corresponds to a project's field. When you create a foreign key, the options are different depending on the fields created in your project and data type you choose:

Foreign key examples
Contact ID (Whole number-Int type)Email (Text-NVarChar type)

Since profiling tables stock additional information on your contacts and links th information to your contact database, it could be used to create segments, target contacts or generate product recommendation. Profiling data improve the knowledge on customers by creating a 360° profile. 

Profiling tables use case examples
  • I have a list of my stores. In my table, I have a foreign key to link my list of customers who purchased an item recently and my primary key is the ID of each store. 
  • I have an employee directory and I want to send internal emails to each department. I have a table for departments with a foreign key employeeNo and a primary key which is the name of each department.   
  • A common example of a profiling table is a table for transactions data linked with contacts. This allows to generate RFM scoring (recency, frequency, monetary) to target contacts depending on their buying journey. Your foreign key could be the client number or email. 

Lookup

A lookup table is not linked to the project's contacts database. A foreign key is unnecessary (contrary to profiling tables). However, you could use one to link another custom table in your project. 

Lookup data stock information by linking them to contacts. These data are frequently used to stock representatives or branches, activities organized by your business or products catalogue, etc.  

Lookup tables use case examples
  • I have several stores. Since my contacts table and my stores' table are not linked, I use the closest store with geolocation to target customers. I use the lookup table to update the stores' contact details. My primary key is storeNo.
  • I sell discount trips whose prices change frequently. I use an Excel file to import my trips and integrate them into promotional emails. If there is a change, I can import my updated list again. My primary key is tripNo.
  • I sell events tickets and I want to select the events to display in my communications. I use a lookup table that is automatically updated in order to facilitate integration. My primary key is eventID.
Then, add fields that will be used to host data from imports into your custom table: 


For each custom table, you must add a field defined as a primary key:

If you check Required, it means this field will be mandatory when adding/importing data into the custom table.


Understanding Data types

The data type lets you define the format of valid input in the field. The default data type is Whole Number (Int) since it is the first in the list.

Voir la liste
Data TypeDescription
BigIntThe value must be an integer between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
BitThe value must be 0 or 1.
DateThe value must be a date in the yyyy.MM.dd format.
DateTimeThe value must be a date with a time, with seconds, in the yyyy.MM.dd HH:mm:ss format.
DecimalThe 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.
IntThe value must be an integer between -2,147,483,648 and 2,147,483,647.
MoneyThe value must be a valid decimal number between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 .
NcharThe value must be a string of characters of a specified length. For example, ISO country codes like "USA" and "CAN".
NVarCharThe 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 be limited or infinite ( extremely large).
SmallDateTimeThe value must be a date with a time, without seconds, in the yyyy.MM.dd HH:mm.
SmallIntThe value must be an integer between -32,768 and 32,767.
SmallMoneyThe value must be a valid decimal number between - 214,748.3648 and 214,748.3647.
TimeThe value must be a time in the HH:MM:SS format.
TinyIntThe value must be an integer between 0 and 255.
If you created a profiling table, you must add a foreign key field: If you have created a lookup table, you do not need to add a foreign key. However, you could still add one to link this table to another custom table.
Add other fields you need in your table and, when you are done, click on Build:
Once it is built, the table is not yet ready to use. To start using the custom table, click on Publish:Once published, the table displays a green checkmark. In this state, the table cannot be archived. To archive a table, you must first unpublish it.
Additional configuration settings, all optional, let you specify how data from the tables should be integrated into the application. Once the options are selected, do not forget to click on Save:

Next Step

You can now import data in your custom table

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.