Data Injection

Data Injection is an advanced feature for integrating with non-Digital-Clay applications. It provides several techniques with which to add or update data records from an external program or web site into Digital-Clay.

External programs may read data directly from the Digital-Clay database, but they are not allowed to write into the database since this would bypass the many integrity checks, automation, auditing and synchronization features of ClayCentral. Data Injection provides write functionality when bi-directional data integration is needed.

No matter which method is used to inject data (Mail, TCP or File), the data must be sent in a specific but simple XML structure (see below).

Data Injection is configured in ClayStudio in Administration/ClayCentral Settings/Data Injection.

• Only when there is control over how the data is to be sent and saved. Since the data must be converted to a Clay XML structure, code must be written to apply this conversion. If the data to be imported is simply stored in a file or database, the Integrator should be used instead.

• For injecting or updating individual, or less than a hundred records at a time. With a larger number of records, the Integrator is recommended instead for performance reasons, as well as for its Preview feature in case of importing errors.

• When real-time updates are absolutely necessary.

Description: XML files are saved inside a specific directory. ClayCentral checks for new XML files in this location periodically and deletes the files as soon as they are read.

Usage:

• A local server database that serves external applications can be programmed using triggers that dump all changes to specific records into an XML file.
• External applications running on the server machine, or having write access to a server folder over the network (not recommended).

Details:

• ClayCentral is configured to check a specific local directory at a configurable interval.
• ClayCentral will automatically detect non-Unicode, UTF8 and UTF16 formats as long as the file headers are set correctly. These formats are necessary for multilingual data.
• The files MUST have an .XML extension - all other files are ignored.

Description: ClayCentral listens at a specific TCP port for incoming XML data. Connections may be opened and closed as soon as the data is sent, or connections can be kept open for streaming data.

Usage:

• Applications that need to stream many records and that can open a connection to ClayCentral.
• External applications running on the local network, or having connectivity to ClayCentral over the internet (not recommended).
• This is the fastest method and is therefore most appropriate for real-time.

Details:

• ClayCentral is configured to listen at a specific port for incoming connections and data.
• If the data is sent in Unicode format for multilingual data, check the Unicode checkbox and select either UTF8 or UTF16.

Description: The XML data is included inside an e-mail. ClayCentral checks the POP account periodically for new e-mails. All E-Mails are deleted as soon as they are retrieved, whether they contain XML data or not.

Usage:

• Web forms (standard formmail Perl or CGI scripts that submit forms to an e-mail account).
• External remote applications without direct access to the ClayCentral.

Details:

• ClayCentral Data Injection is configured with a POP account which it checks at the configured interval.
• ClayCentral will automatically detect either UTF-8 or non-unicode formatted e-mails.

Sample Clay XML block (for adding/inserting a new Lead):

<ClayData Username='administrator' Password='' TableID='8' SkipErrors='0' AddNewConnected='22' DuplicateHandling='2' UniqueFields='2,9'>
  <Field ID='2'>John Galt</Field>
  <Field ID='22' InnerID='2'>Newspaper</Field>
  <Field ID='9'>02-5866666</Field>
</ClayData>

• A single record is contained inside a single <ClayData> block.
• If ClayCentral finds irrelevant data before or after the XML block, it ignores/discards this data only and processes the inner blocks that it identifies.
• Multiple XML blocks can be sent in one file, e-mail or TCP connection.
• All incoming data is copied into either the configurable Failed folder (default: C:\DataInjection\Failed\) if there were errors while parsing or saving the data, or in the configurable Succeeded folder.
• Note that all fields and tables used in the XML block go according to the internal table or field ID number. To find these IDs, simply load the Field Properties administration tool and lookup the specific table or field in the list.

• Username (mandatory): The username to use for inserting data. The username will be used for checking security, field properties, etc. just as if the user himself had logged in and entered the data.

• Password: With HTML forms it is recommended that whenever possible, the user enter the username and password inside the form, otherwise the password would have to be embedded inside the HTML file, or added by the formmail PHP/CGI. Note also that when security is a concern, a special user with minimum permissions should be created for this purpose.

• TableID (mandatory): The table into which the data should be injected. See Field Properties for table IDs.

• SkipErrors (1/0): Whether to skip field conversion errors and inject the record regardless of the sent values, or whether to fail the injection when an error occurs (e.g. an unparseable date value). Default is 1(true).

• DuplicateHandling (0-3): This is similar to the ClayIntegrator duplicate handling options and allows setting what to do when a duplicate item is found: 0=Add (default, always add and don't check for duplicates), 1=Merge (add if it doesn't exist and merge if it does), 2=MergeOnly (don't add new records, merge only with existing ones if they exist), 3=Skip (add new records only, skip if they exist). Note that to update records, this setting must be set to Merge or MergeOnly, and the correct UniqueFields must be set as well for values 1-3.

• UniqueFields: A comma separated list of fields that identify this record as unique. For example, a name field and a phone number field. This is only needed when using DuplicateHandling>0. The default fields used are the name fields.

• AddNewConnected: A comma separated list of linked fields that should be created on-the-fly. E.g. If one of the fields is Contact:Customer, the Customer field will only be set if the Customer already exists. To add a new Customer when it doesn't exist and link to it inside the Contact, the Customer field must be added to this parameter. Default is empty, i.e. no new linked records are added.

• ReturnResult: Applicable to 9.1.77+ only and only for TCP injection. If ReturnResult=1 is added as a parameter, ClayCentral will respond to the injection on the same socket connection after the injection has been handled. It will return either “0” for success, or “1 [Error Message]” if there was a problem or a time-out. The socket must be kept open for this to work. The response will be in the same format corresponding to the Injection Unicode setting in ClayCentral Settings.

• SkipMergeMult (9.1.81+): If merging records, replace many-many-to-many fields rather than merge their values.

For each field that is being filled, a field tag must be added with the ID as a parameter (see Field Properties for field IDs), and the field value as the tag value. All values will be converted to the best of ClayCentral's ability. This includes dates and numbers.

It is not possible to inject images or attachments.

A Linked Field is set by defining the values in unique identifying fields in the linked record. For example, if a Contact is being added with a link to a Customer, the Customer may be identified by its name, or any other combination of fields like phone number, e-mail and URL.

A Linked Field may be set just like any other field, e.g.:

  <Field ID='2'>Newspaper</Field>

By default the unique identifying field is the Name field of the linked record. If you wish to specify which field to use as the identifying field, then add an InnerID attribute which specifies a field ID in the linked record:

  <Field ID='2' InnerID='1'>IBM</Field>

To use a combination of multiple identifying fields, add multiple InnerID values:

  <Field ID='2' InnerID='1'>IBM</Field>
  <Field ID='2' InnerID='9'>888-123-4567</Field>

If the linked record is not found, the Linked Field value will be cleared, unless you have specified that missing linked records be added using the AddNewConnected attribute (see above).

Note that Data Injection is designed for adding a single record at a time per XML block. Although linked records may be added on the fly while adding the main record, only the fields you set as the identifying fields may be set in the linked record, which may leave many fields empty. It is therefore recommended that you add the linked records separately first using an additional XML block before adding links to these records.

For setting many-to-many linked fields, the following options are available:

To set a single value in the many-to-many field, simply set it just like regular linked fields.

To set multiple values using any unique identifying fields in the linked record, send multiple XML blocks. For example, to set two Contacts in a Task record:

 <ClayData Password='' Username='administrator' TableID='14' SkipErrors='0' AddNewConnected='18' DuplicateHandling='1' >
  <Field ID='2'>Task123</Field>
  <Field ID='18' InnerID='7'>Smith</Field>
 </ClayData>"
 <ClayData Password='' Username='administrator' TableID='14' SkipErrors='0' AddNewConnected='18' DuplicateHandling='1' >
  <Field ID='2'>Task123</Field>
  <Field ID='18' InnerID='7'>Gates</Field>
 </ClayData>"

The above data (which is sent in a single connection, file or e-mail), will add a single task with two Contacts, one with the Last Name of 'Smith' and another called 'Gates'. If either of these Contacts do not exist, they will be created.

An alternative method of sending a single XML block may be used, but only if the internal IDs of the linked records are known in advance, as follows:

 <ClayData Password='' Username='administrator' TableID='14' SkipErrors='0' AddNewConnected='18' DuplicateHandling='1' >
  <Field ID='2'>Task1234</Field>
  <Field ID='18' InnerID='0'>-712969534;-675865816</Field>
 </ClayData>"

This will add two Contacts to the Task:Contacts field having the above IDs.

HTML forms, or web sites, are applications like any other and may use any of the above methods for injecting data into Digital-Clay. A standard method is to submit the HTML form to a formmail CGI that sends the data to a specified e-mail.

The only difficult part in the above scenario is to convert the form values into a Clay XML block. This can be done at the client-side using Javascript, or server-side using PHP/CGI that converts the data before sending the e-mail.

A sample PHP script can be downloaded here. Details:

• The PHP is configured using the default clayMap.XML file.
• A different mapping file may be used for different HTML forms by sending a 'map=<filename>' parameter.
• All ClayData parameters are set using this XML, including the username/password and the e-mail to send to.
• The 'sender' parameter is important for most SMTP servers which usually don't accept e-mails from unsecure domains.
• The fields on the HTML form are mapped to Clay Field IDs. This way, existing forms don't need to be changed.

In addition, a tool for creating HTML forms with client-side conversion is provided in ClayStudio (Data menu):

• This tool is only meant to provide an easy starting point - a basic HTML file with all the fields, parameters and conversion code.
• Typically, after the form is generated, it should be edited and formatted using a standard HTML editor.
• Note that this tool can only create new forms, not edit old ones.
• IMPORTANT: If a specific username/password is embedded inside the form instead of asking the user for this information, this is a security risk and the Clay User must be setup carefully with this in mind. See the previous section for more details.
• The Form Submit URL is the address of the CGI/PHP that that the form should be submitted to.
• The Other Form Parameters is for fields like recipient (email address) or subject which the formmail script may be expecting. Raw HTML code is entered here.
• A field can be set to hidden in order to fill a field with a default value that the user doesn't see or enter.

Each approach has its advantages and disadvantages:

• When an HTML form already exists, the PHP solution is recommended as it can map existing form fields into Clay XML fields.
• When no HTML form exists, a new one can be created using the ClayStudio within minutes instead of hours.
• If HTML forms need to be updated with new fields, field drop-down values or parameters then the choices are:
  1. Use the PHP script and change its configuration. This is the easiest method for changes.
  2. Use ClayStudio to create a new form and copy/paste the relevant HTML code into the old HTML file.
  3. Edit it manually.
• The PHP has an advantage in that the username/password parameters are hidden from the user.
• If PHP is not available or the formmail CGI cannot be controlled with server-side code, the client-side solution is the only choice.