API reference
Help CenterCommunityDevelopersAcademyStatus

Relational table management

A database that can be perceived as a set of tables and manipulated in accordance with the relational model of data. A Relational Table database contains indices that create a one to many relationship between data in one file and data in many other files.

(Create a Relational Table) – used to create a Relational table in Acoustic Campaign.
(Delete Records from a Relational Table) – is used to delete records from an Acoustic Campaign Relational table.
(Delete a Relational Table) – used to delete Acoustic Campaign Relational table data.
(Export from a Relational Table) – used to export Acoustic Campaign Relational table data.
(Import to a Relational Table) – used to create or update an Acoustic Campaign Relational table.
(Insert and Update Records in a Relational Table) – used to insert or update Acoustic Campaign Relational table data.
(Associate Relational Data with Contacts in a database) – used to associate an Acoustic Campaign database with a Acoustic Campaign Relational table.
(Purge Data from a Relational Table) – used to programmatically delete Acoustic Campaign Relational table data.

Relational table definition and column mapping formatting

Mapping is the process of associating columns in the import file with the corresponding fields in the relational table. For example, Column 1 in the file corresponds to Purchase Date in the Purchases relational table.

The relational table mapping XML file is broken into three main sections:

TABLE_INFO – Defines the information about the table you are creating or updating, including the action to take on this table.

COLUMNS – Consists of one or more COLUMN elements. Each COLUMN element contains child elements to define the attributes for that column.

MAPPING – Consists of COLUMN elements. Each COLUMN element contains child elements to define the attributes for mapping that column. All column names are case-sensitive. The Mapping file and Relational Data file field names must exactly match the case and content in the existing relational table.

Syntax for the TABLE_IMPORT element

<TABLE_INFO>

The TABLE_INFO section defines information about the table you are creating or updating, including the action to take on this table.

ACTION - Defines the type of relational table import you are running. Valid values are:

  • CREATE – Creates a new relational table.
  • ADD_ONLY – Adds only new records to the table. Acoustic Campaign ignores existing/duplicate records in the source file.
  • UPDATE_ONLY – Only updates existing records in the table. Acoustic Campaign ignores records that are found in the source file but not in the relational table.
  • ADD_AND_UPDATE – Processes all records in the source file. If the item exists in the relational table, it updates specified field values. If the item does not exist, it creates a new record in the relational table.
  • REPLACE – Deletes all records from the relational table and adds new records to the table.

TABLE_NAME - Defines the name of the new relational table if the ACTION is CREATE (table names must be unique).

TABLE_ID - Defines the ID of the relational table for actions other than CREATE.

TABLE_VISIBILITY - Defines the visibility of the table: 0 (private) or 1 (shared). If not included, Acoustic Campaign defaults to private.

FILE_TYPE - Defines the formatting of the source file. Supported values are: 0 (CSV file), 1 (tab-separated file), or 2 – (pipe-separated file)

HASHEADERS - The HASHEADERS element is set to True if the first line in the source file contains column definitions.

<COLUMNS>

The COLUMNS section defines 0 or more columns to write in the relational table. Define each table column with its own COLUMN element.

COLUMN - XML node containing NAME, TYPE, KEY_COLUMN, and DEFAULT_VALUE as children.

NAME - Defines the name of the column.

TYPE - Defines what type of column to create. Valid values are:

  • 0 – Text column
  • 1 – YESNO column
  • 2 – Numeric column
  • 3 – Date column
  • 4 – Time column
  • 5 – Country column
  • 6 – Select One
  • 7 – Email
  • 16 – Phone Number
  • 17 – Date/Time
  • 19 – Sync ID

IS_REQUIRED - Defines whether to create the column as required when contacts are providing information through a form. The import process uses this to validate the data Valid values are True or False.

KEY_COLUMN - Added to field definition and set to True to define a field as an unique key for the
relational table. You can define more than one unique field for the table to create a composite key.

DEFAULT_VALUE - Defines the default value of the column that is used by the send process if the record does not contain a value for this column.

<MAPPING>

The MAPPING section tells the relational table import process the columns in the source file map to the columns in the Acoustic Campaign relational table. Define each table column in its own COLUMN element.

COLUMN - XML node containing INDEX, NAME, and INCLUDE as children.

INDEX - Defines the order to define this column in the source file.

NAME - The name of the TABLE column to map the column in the source file.

INCLUDE - Defines whether this element is included in the table import. You do not need to import all columns from the source file. Using this element, you can direct the relational table import process to skip the columns you don’t want to include. Valid values are True and False.

<TABLE_IMPORT>
  <TABLE_INFO>
    <ACTION>CREATE</ACTION>
    <TABLE_NAME>Purchases</TABLE_NAME>
    <FILE_TYPE>0</FILE_TYPE>
    <HASHEADERS>true</HASHEADERS>
  </TABLE_INFO>
  <COLUMNS>
    <COLUMN>
      <NAME>CustomerID</NAME>
      <TYPE>0</TYPE>
      <IS_REQUIRED>true</IS_REQUIRED>
      <KEY_COLUMN>true</KEY_COLUMN>
    </COLUMN>
    <COLUMN>
      <NAME>PurchaseDate</NAME>
      <TYPE>3</TYPE>
      <IS_REQUIRED>true</IS_REQUIRED>
      <KEY_COLUMN>true</KEY_COLUMN>
    </COLUMN>
    <COLUMN>
      <NAME>ProductID</NAME>
      <TYPE>0</TYPE>
      <IS_REQUIRED>true</IS_REQUIRED>
      <DEFAULT_VALUE/>
    </COLUMN>
  </COLUMNS>
  <MAPPING>
    <COLUMN>
      <INDEX>1</INDEX>
      <NAME>CustomerID</NAME>
      <INCLUDE>true</INCLUDE>
    </COLUMN>
    <COLUMN>
      <INDEX>2</INDEX>
      <NAME>PurchaseDate</NAME>
      <INCLUDE>true</INCLUDE>
    </COLUMN>
    <COLUMN>
      <INDEX>3</INDEX>
      <NAME>ProductID</NAME>
      <INCLUDE>true</INCLUDE>
    </COLUMN>
  </MAPPING>
</TABLE_IMPORT>