Associate a contact field with a data table

After importing the data into the data table and activating the contact extension, the next step is to create the database field to which the table will be linked, so that the data can be displayed directly in the contact profile.

The association between a contact and a data table can be of two types:

• Association 1-to-1: links each contact to a single element of the data table connected through the contact extension (e.g., a contact belongs to only one company).

• Association 1-to-many: links each contact to multiple elements of the data table connected through the contact extension (e.g., a contact can place multiple orders).

Example with a 1-to-many association

Let’s see how to link the “orders” data table to the contact profile, in order to display which and how many orders the contacts have placed. The data table contains the following columns:

• order_id
• price
• purchase_date
• mail

1. Go to the database where you want to create the field by following the path:
   Profile Studio > Database Name > Settings > Contact Fields
2. Click the Create Field button
3. Define the category and the label
   
4. Go to the Type of data tab and select Association 1-to-many as the field type
   
5. Fill in the following fields and create the field
   • Associate 1 contact to several elements of
     Select the data table you want to associate with contacts. In our case, the table name is orders.
   • If you want to see a dedicated tab for the linked information, enable the option Use a data table of association
     • Table of association
       If the data to be associated is contained in an additional data table, select it here; otherwise, repeat the same selection made in the previous field.
       The association table must contain one field referencing the contact and one field referencing the chosen extension. In our example, we select orders again.
     • Name of field containing contact ID
       This must be filled in only if the contact ID is stored in the data table. In that case, enter the name of the data table column that contains the ID.
       In our example, the contact ID is the email address, and the column containing it in the data table is mail.
     • The contact ID field contains the contact key instead of the ID
       Enable this option if the field you specified in the data table contains the contact key.
       In our example, the database key is the email, and the contact ID in the table is also the email, so the option must be enabled.
     • Name of the field containing the reference to the initial table
       The association table you selected must include a field that contains the reference ID to the table chosen in "Associate 1 contact to several elements of". Enter the name of this field here.
       In our example, we want to know how many and which orders the contact has placed, so the ID to specify is the order ID, whose column is named "order_id".

Once the field is created (in our case, Orders), it will be populated for all contacts appearing in the data table.

Example with a 1-to-1 association

Let’s see how to link the "companies" data table to the contact profile, so you can display which company the contact belongs to and view the related company information. The data table contains the following columns:

• id_company
• company_name
• num_employees

1. Go to the database where you want to create the field by following the path:
   Profile Studio > Database Name > Settings > Contact Fields
2. Click the Create Field button
3. Define the category and the label
   
4. Go to the Data Type tab and select Association 1-to-1 as the field type
5. Fill in the following fields and create the field
   • Associate 1 contact to 1 item of
     Select the data table you want to associate with contacts. In our case, the table name is companies
   • Show all fields of the associated extension
     Enable this option if you want to view all the data of the linked company, and not just the connected field

Once the field is created (in our case, Company), it will be populated for all contacts appearing in the data table.
 

Filters and dynamic audiences with the new associated field

Once the association is complete, you can create filters or dynamic audiences by combining the information in your database with the data contained in the linked data table.

For example: to filter all those who have placed at least one order, you can apply the following filter: Orders > Value > Status populated

Next article

Table relationship >>