Import Relational Data (Individual or Small Batch Save)

The Import Relational Data Save command allows you to add and update a Contact's data in a Relational Data table individually. It requires the following arguments: Command and Data.

cmd=relationalsave&data=email,first,mytable.uniqueID,mytable.myNumber^john.doe@domain.com,John,1232,11

Within the data argument, contact data fields are required to identify the Contact to associate the Relational Data. To identify the Contact, Email and First Name must be defined, or Customer Key in a customer key enabled Realm.

The command is ‘relationalsave' and data will be stored in the table named 'myTable', fields named 'uniqueID' and 'myNumber' for the contact record with the email 'john.doe@domain.com' and first name 'John'.

NOTE: Do not include non-relational data commands in the batch process. Relational Data management must be in a separate data file. Other commands, such as Subscribe and Launch, should be run in a separate batch file.

Example

In order to manage Relational Data, the FTP API file Header must include the following arguments: Relational set to true (1) and Allow Duplicates set to false (0).

Clear Data may also be set, but must be supported by the Relational Table defined in the commands. If Clear Data is defined in the header, then every table within the FTP API command file will be cleared of All data if the table allows Clear Data. Please be very careful using this option, as it cannot be undone once complete.

Using all commands and arguments (please note the command is wrapped for display purposes only):

realm=realm
password=password
confirm_email=myemail@domain.com
relational=1
allow_duplicates=0
clear_data=1

cmd=relationalsave&data=email,first,interests.id,interests.category,interests.interest_type^jane@domain.com,Jane,1,boating,null

In the example above, the header indicates that the batch process is for Relational Data and that the data in all tables defined in the file will be removed before processing new data. The command imports Relational Data for the fields named id, category, interest_type in the table interests for a single contact.

Multiple contacts

To update Relational Data for multiple contacts with a single command, separate each contact record using the carat (^) character. Each contact must include the same data fields defined in the command. For example, the command below imports Relational Data for the fields named petid, name, species, and age in the table named 'pets' for three separate contacts.

cmd=relationalsave&data=email,first,pets.petid,pets.name,pets.species,pets.age^jane@domain.com,Jane,2,Meow,cat,5^john@domain.com,John,3,Deohgee,dog,9^jack@domain.com,Jack,4,Sally,dog,2

Customer Key

Some systems have the ability to define a unique Customer Key per contact. If your Realm has Customer Key enabled, then you must include the customer_key in the data argument. email is not required. For example:

cmd=relationalsave&data=customer_key,pets.petid,pets.name,pets.species,pets.age^12453,Jane,2,Meow,cat,5

If the Customer Key is not included, the command will fail.

Non-contact Table Foreign Key

Prior to v12.18.4, Relational Save only supported tables in which a Foreign Key to the Contacts table exists. As of v12.18.4, you can also import data into Flat tables that do not use Contact ID as the Foreign Key. To do this, use the arguments Ignore Contact and Table Type. For example, to import a row into the Vets flat table:

cmd=relationalsave&ignore_subscriber=1&table_type=flat&data=vets.vetid,vets.clinic_name^234,North%20Creek%20Vet

If the table does not have Contacts associated as the Foreign Key, you must ignore the Contacts table, and set the Table Type. The Table Type is required if Ignore Contact is enabled.

Results

Success

The Relational Data FTP API delivers two emails to the Confirmation Email address(es) defined in the Header. The messages are generated when the Data (.dat) file is picked up, and then again when the process is complete. The messages include the Realm ID, name of data file, number of rows in the file, added or updated, and the number of errors.

Errors

A Import Relational Data command will fail if a contact record for the Email plus First Name does not exist, table or data fields do not exist, or do not allow data update. Failures will result in an email message sent to the Confirmation Email defined in the header. The message contains information related to the failure.

Find more details about errors in the TASKS > TASK HISTORY page.

Arguments

The Import Relational Data command supports the following arguments:

Name	Req?	Description	Argument
Header Relational	Yes	When working with Relational Tables, the Relational flag must be set. By default, Relational is not enabled.	relational=[0,1] DEFAULT==0
Header Allow Duplicates	Yes	Relational Data tables do not support the allow duplicates flag, so it must either be set to off (0), or not included in the Header. See Conventions for more information.	allow_duplicates=[0,1] DEFAULT==0
Header Clear Data		To remove all data in a table before importing new data, set Clear Data to 1. A table must be created allowing the use of Clear Data, otherwise the flag is ignored. See Data Management Options for more information.	clear_data=[0,1] DEFAULT==0
Command	Yes	Defines the command to use.	cmd=relationalsave
Realm		Name of the realm to run the command. The realm is required in the Header, but optional in the command line to manipulate data for a child realm.	realm=[realm_name]
Data	Yes	Contact and Table related data. To identify the Contact to which the data is related, the Contact Email and First Name are always required or Customer Key for Realms in which Customer Key is enabled. Relational Table Unique ID is required, and additional fields are optional. The Relational Data fields must be identified using the table name and the field name in the format: tableName.fieldname	data=[email,first,tableName.fieldname(s)] data=[customer_key,tableName.fieldname(s)]
Ignore Contact		If importing data into a table that does not use Contacts as the Foreign Key, then set the argument to Ignore Contacts. If enabled, you must also set the Table Type. **Added in v12.18.4	ignore_subscriber=[0,1] DEFAULT==0
Table Type	*	If importing data into a table that does not use Contacts contact as the Foreign Key, then set the Table Type to indicate which type of table the data is to be inserted: Flat Regular (coming soon) Join (coming soon) **Added in v12.18.4	table_type=[flat]