Skill Level: Any
This interface is used for programmatically creating or updating a Relational Table in Watson Campaign Automation. This operation requires a mapping file and source file stored on the FTP server related to the Watson Campaign Automation account used to Login. Once you upload the Relational Table source and mapping files to the FTP server, you can make an ImportTable API call to launch the job.
Note:¬†This operation does not associate the relational table to a database. See “Associate Relational Data to Contacts in a Database” for creating the association.
Syntax of the request and response SOAP for ImportTable
Operation¬† <ImportTable>¬† ¬† Elements¬† MAP_FILE¬† The name of the Mapping file in the upload directory on the FTP server to use for the
¬† SOURCE_FILE The name of the file containing the Relational Table data to use in the import. This file must reside in the upload directory on the FTP server.¬† Example¬† <soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”
¬† Response¬† <RESULT>¬† ¬† Elements¬† SUCCESS¬† True if successful.¬† ¬† JOB_ID¬† Identifies the Watson Campaign Automation Background Job created and scheduled for this import.¬† Example¬† <envelope:Envelope xmlns=”SilverpopApi:EngageService.ListMgmt.UserActions”xmlns:envelope=”http://schemas.xmlsoap.org/soap/envelope/”>
Relational Table Definition and Column Mapping Format
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, as well as the action to perform on this table.
- COLUMNS ‚Äď consists of one or more COLUMN elements. Each COLUMN element contains child elements to define the attributes for that column (for example, name, type, and key column).
- MAPPING ‚Äď also consists of COLUMN elements. Each COLUMN element contains child elements to define the attributes for mapping that column (for example, name or index).
Note:¬†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.
The table below describes each section to include with a LIST_IMPORT element:
Section¬† <TABLE_INFO> The TABLE_INFO section defines information about the table you are creating or updating, as well as the action to perform on this table.¬† ¬† Elements¬† ACTION
Defines the type of Relational Table import you are executing. Valid values (and the
actions they perform) are:
CREATE ‚Äď create a new Relational Table.
ADD_ONLY ‚Äď only add new records to the table. Watson Campaign Automation ignores existing (duplicate) records in the source file.
UPDATE_ONLY ‚Äď only update existing records in the table. Watson Campaign Automation ignores records found
in the source file but not in the Relational Table.
ADD_AND_UPDATE ‚Äď process all records in the source file. If the item already exists in
the Relational Table, update specified field values. If the item does not exist, create a new record in the Relational Table.
REPLACE ‚Äď deletes all records from the Relational Table and adds new records to the
¬† ¬† TABLE_NAME¬† Defines the name of the new Relational Table if the ACTION is CREATE (table names must be unique).¬† ¬† ¬† TABLE_VISIBILITY¬†
Defines the visibility of the table. If not included, Watson Campaign Automation will set to Private.
0 ‚Äď Private
1 ‚Äď Shared
¬† ¬† PARENT_FOLDER_PATH¬†
Used with the CREATE ACTION to specify the folder within which to place the new table.
Use the VISIBILITY element to specify Private or Shared.¬†
¬† ¬† FILE_TYPE¬†
Defines the formatting of the source file. Supported values are:
0 ‚Äď CSV file
1 ‚Äď Tab‚Äźseparated file
2 ‚Äď Pipe‚Äźseparated file
¬† ¬† HASHEADERS¬† The HASHEADERS element is set to True if the first line in the source file contains column definitions.¬† ¬† Section¬† <COLUMNS>¬† The HASHEADERS element is set to True if the first line in the source file contains column definitions.¬† ¬† Elements¬† COLUMN¬† ¬† ¬† ¬† Child Elements¬† NAME¬† Defines the name of the column.¬† ¬† ¬† TYPE¬†
Defines what type of column to create. The valid values are:
0 ‚Äď Text column
1 ‚Äď Yes/No column
2 ‚Äď Numeric column
3 ‚Äď Date column
4 ‚Äď Time column
5 ‚Äď Country column
6 ‚Äď Select One
7 ‚Äď Email
17 ‚Äď Date/Time
19 ‚Äď Sync ID
¬† ¬† IS_REQUIRED¬†
Defines whether to create the column as Required when contacts are providing information through a form. Valid values are True and False.
Note: The import process does use this to validate the data.¬†
¬† ¬† KEY_COLUMN¬† Added to field definition and set to True to define a field as a 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 used by the Send process if the record does not contain a value for this column.¬† Section¬† <MAPPING>¬† The MAPPING section tells the Relational Table Import process which columns in the source file map to which columns in the Watson Campaign Automation Relational Table. Define each table column in its own COLUMN element.¬† ¬† Elements¬† COLUMN¬† ¬† ¬† ¬† Child Elements¬† INDEX¬† The INDEX element defines the order within which to define this
column in the source file.¬†
¬† ¬† NAME¬† The name of the TABLE column to which to map the column in the source file.¬† ¬† ¬† INCLUDE¬† Defines if this element is included in the table import. You do not need to import all columns from the source file. This element allows you to direct the Relational Table Import process to skip the columns
you don’t want to include. Valid values are True and False.¬†
Example Mapping File
Example¬† This example shows a mapping file for creating a new Relational Table related to an existing list. Customer ID and Purchase Date make up the key for the new table:¬† ¬† <TABLE_IMPORT>