Skip to main content
Italiano
Help Desk

How to create and populate a data table

Updated

Create a data table

Create a data table to extend the data model in magnews and collect information linked to contacts. 

Go to Settings > Developer > Data Tables and create a new data table with the New data Table button.

In the Name field of the new table, enter a name for your table.

The table name must start with a letter, accept only letters and numbers, and must be entirely lowercase.

Once the name is entered, click the Add a primary key field button to create the primary key column. This field will be used as the logical key for the contact extension, if enabled, and by web services and APIs.

  • Column name: assign a name to the column that will serve as the primary key.
    The column name must start with a letter, contain only letters and numbers, and be entirely lowercase.
  • Data type: choose the data type for the column.
    You can choose Integer (4 bytes) or text. To relate data from tables with contacts, we recommend choosing the Integer type.
  • Autoincrement: automatically inserts values when creating records.
  • Ascending order: sorts the records based on the value of the primary key (from the smallest to the largest)

Click the Save button.

Create field

To add all the other order-related information, click the New Field button for each column you want to add. You will need to enter the following information:

  • Name: name of the field you are creating;
  • Data type: choose which type of data to insert in the field:
    • Text:
      used for names or small-sized text;
    • Integer (4 Byte):
      used for very large numbers (up to billions in value);
    • Integer (1 Byte)
      for checkbox values or very small numbers;
    • Decimal number:
      used for decimal numbers (for example, for prices);
    • Long text:
      it is possible to store very long texts, but it is recommended to use it only if strictly necessary as it has a high impact on table performance.
    • Date:
      allows you to enter date type values;
    • Date and time field:
      like the date field but with the possibility of specifying the time as well.
  • Unique constraint:
    by selecting this option, the field values will be considered unique, in other words, there cannot be two rows with the same value in the same column.
    This setting is usually used for identifying values or for email.
  • Maximum character length:
    the maximum number of characters that can be entered in this column. The maximum allowed length is 512 characters.
  • Allows null values:
    with this option, it is possible to insert the value Null that is no value in the column.
  • Default value:
    automatically associates the value entered in the field to the records for which none is defined during insertion.

In our example, we create the fields for the order date, the order total and the buyer's email.

 

To create an automatic link between the Data Table and the database, it is necessary to enter the database primary key (in our case, the email address) for each record in the table (in our case, orders).

 

Populate a data table

You can populate a data table in two ways:

  • Manually, with a one-time import as described below
  • Automatically, through a scheduled task

Scheduled tasks are the right choice when data loading happens repeatedly over time or when you want to avoid manual work. You can run them on demand, schedule them at regular intervals (monthly, weekly, daily, hourly), or trigger them whenever a file is detected in the FTP folder defined in the data source.

If you’re not sure how to do this, learn more in the article that explains how to create scheduled tasks.

To populate a data table manually, go to Settings > Developer > Data tables and click Import elements from the contextual menu on the right.

Before starting:

  • The file to be imported must be in .csv format.
  • The first row must contain the names of the fields in the table as column names and they must correspond.
  • The format of the fields in the file you are importing must be compatible with the table fields.
  • The format of date, datetime, and decimal number fields depends on your international settings.
For each row, the system checks if the record is already present in the table: if the record is already present, it is updated with the values from the file, otehrwise it will be added to the table. If the file contains multiple rows with the same primary key, the record will be updated multiple times.

Before executing the import process, there are several options to set:

  • File to import:
    choose the file you want to import;
  • Encoding:
    choose the character encoding type of the file you are about to import;
  • Line separator:
    set the character that will be used as the line separator in the file you will import;
  • Field separator:
    this is the character used to separate different fields;
  • Text qualifier:
    the character used to delimit text values within the field;
  • Remove unnecessary spaces at the beginning and end of file texts:
    if you activate this option, all unnecessary spaces at the beginning and end of non-empty text fields will be automatically removed.

Once all the import parameters are configured, click Run at the top right to proceed with the import. After importing the data into the table, you will receive a report of the completed operation.

If you need to manage a large number of fields, to avoid having a single complex and redundant data table, you can create multiple data tables and link them together.

 

Next article

Contact extension: link a data table to contacts >>