Understanding & creating Custom Tables

Understanding & creating Custom Tables

What is a custom table?

A custom table is a data structure that organizes information into rows and columns. A table can be used to both store and display data in a structured format. In terms of your Delivra account, your Contact records and their associated data is stored to a standard table within our database. Further,
  1. A table has a set number of columns, but can have virtually unlimited rows.
  2. Each row in a table is called a record and each record has a unique identifier. 
  3. That identifier is called a key. A key can be a single field or combination of fields. 
  4. A key is used to link records to data stored in other tables.
  5. When records in one table are associated with records from another, a data relationship is created.
Sometimes, our customers need to use tables to serve as extended storage for Contact information, or to store multiple records of information per email address, or to connect data not stored on a contact record directly. For these purposes, Delivra offers custom tables.

Availability of custom tables will depend on your license. Please visit https://www.delivra.com/pricing or email our Client Success team for more information on licenses.

How are custom tables used?

Custom tables can solve the following common data needs: 

1.  More Contact fields
A custom table can be utilized when a customer needs more contact fields, or more of a specific field type, like additional date fields that are used to trigger emails.

2.  Storing multiple records per email address
A custom table can be utilized when a customer needs to store multiple records of data relating to orders, events, tickets, and more. In this instance, one email address needs the ability to store multiple records.

3.  Sharing multiple records between more than one email address
A custom table can be utilized when a customer needs to store and share multiple records of data between multiple contacts relating to events, properties, and more. In this instance, an email address not only needs to store multiple records, but share them as well with other email addresses.

4.  Connecting relational data to Contact records
The most complex example of a custom table is when a customer needs to create a relationship between tables that do not have a direct correlation in order to connect data to a Contact email address. For instance, restaurant operating hours need to be merged into patron emails but that information is not stored on contact records.

What table relationships are supported in Delivra?

We offer a variety of table relationships because there is no one-size-fits-all table structure that can match all data needs.  The following relationships are available in Delivra:

One-to-One
In a one-to-one relationship, one record in table A is associated with one and only one record in table B. Example: At a university, a student email account is linked to one and only one unique student ID.
The key for this data relationship is email address.
An example could be that a customer has used all numeric fields on the default member table. Adding a custom table to their account can extend contact data storage.

One-to-Many or Many-to-One
In one-to-many relationships, one record in table A is associated with multiple records in table B (and vice-versa for many-to-one). Example: At a veterinary clinic, a client account can have multiple pet records associated.
The key for this relationship is never an email address. Some other identifier like an account #, order #, etc. that uniquely identifies records should be used.
Keys are used to determine when a new row should be created versus an update to an existing row.
A table may be setup using more than one key.
An example could be that one credit union member email address is linked to multiple products like checking, home equity line of credit and an auto loan.

Many-to-Many
A many-to-many relationship occurs when multiple records in table A are associated with multiple records in table B. Example: In terms of retail, an order may contain many products and a product may appear in many orders.
The key for this relationship is also never an email address. Some other identifier like account #, property ID, parcel #, etc. that uniquely identifies records should be used.
An example could be that multiple contact email addresses are linked to multiple property IDs to receive tax information for each property they own together.

Many-to-many relational tables

Above you learned that a relationship between tables is formed when records from one table are associated with records in another table. However, a direct correlation between tables may not always exist which is the case with many-to-many relationships. When this occurs, another table is needed to bridge the data. This table is called a junction, or joining table. 

A junction table contains the primary key columns from the tables that need to be related.
The purpose of the junction is to build an ID structure that creates the relationship between two (or more) otherwise unrelated tables. 
The result is a “relational” table that contains data from both of the original tables, like the customer purchase table we see on the right.

In the below example, the keys from the customer table and the product table are used to create a mapping in the junction table and establish a separate customer purchase table. The “Customer Product Mapping” table is the junction and “Customer_Purchase_Table” is the resulting “relational” table that is created.

1.  Relational tables are generally used in many-to-many table relationships. This functionality offers a way for our customers to connect table data that does not have a direct correlation to contact records.

2.  Once relational tables exist in a customer account, CSV files can be uploaded to populate data in many-to-many tables. This data can be used in segmentation, automation, content merges and more.  We’re doing the work on the back-end to make the connections between tables.

3. Segment clauses are particularly important-- All segments will require a clause that connects the tables through use of a common piece of data.

4.  The junction table is not visible in a customer account. They will only see the resulting related tables. 

5.  Customers cannot set up relational tables on their own, it will require initial setup by our Engineering team.

6.  Availability of Relational tables depend on your license. Please contact our Client Success team if you are interested in Relational tables. 


How-To

If you are a Site Administrator, you can create custom tables in the Contacts dashboard of your account. 
  1. Navigate to the Contacts Dashboard.
  2. Choose "Configuration" from the left menu.
  3. Select "Custom Tables"
  4. Click the "Create Custom Table" button (top-right).
  5. Choose if you want to use a Standard table, or create your own Custom table. We offer two Standard tables at this time to store commerce data or meeting data.
  6. If you choose Custom Table, then you will provide a name your table.
  7. Choose the table relationship: one-to-one, one-to-many, many-to-one, or many-to-many.
  8. Click "Next" (top right).
  9. Start creating fields for the table.
      • Manually enter the field name, select the field type. Click the (+) button to add the next field.
      • Or, upload a CSV file with column headers.
  10. The "Primary Key" checkbox should remain unchecked for most fields. The Primary Key uniquely identifies each record in a table. Primary keys must contain unique values, and cannot contain NULL values.
    For relational data like tickets or orders where there may be more than one record per email address, you will want to choose a primary key that would be unique to each record. In the example below I chose Order Number as the primary key for the table because the order number is likely to be the unique value amongst these fields. 

  11. Click the "Save" button when done creating fields. You cannot return to edit any fields after saving, but you may add more fields.

Notes

  1. We recommend a consultation with your Client Success Manager before attempting to setup a custom table on your own.
  2. All site administrators can access Custom Tables however the permission is not included with any default account administrator Roles. A new Role would need to be created for any account administrators that need access to creating Custom Tables. To create a new role, please see this article on the How-To: How do I create or edit a Role?


Importing data to custom tables

When it comes to populating data to custom tables, you have options. Most often custom tables are populated via manual CSV imports, through an integration with another application, or via API. In this article we will cover the steps to import a CSV file to a custom table.

1. Navigate to the Contacts dashboard of your account.

2. Click the "Import Data" button found in the top right of the page.

3. Click the "Select" button to upload a CSV file from computer. Double left-click on the located file on your computer to choose it.

4. Click the "Next" button found in the top right of the page.

5. Match each row from the left column to an appropriate field from the right column.
Notes: 1) The rows seen in the left column are the column headers from the uploaded CSV file. 2) Custom table fields will be located at the bottom of the menu. 3) Know your setup. It's possible that some fields in your CSV map to standard table fields and others map to custom table fields.

6. Click the "Next" button.

7. In the last step you will define a few settings for processing the CSV file. These include:

Import Action: Most often left as the default "Import quietly as regular contacts". This means that no external notification will be sent when emails are imported like a welcome or a confirmation. If you have these types of campaigns configured in your account, you may change the Import Action.
Report Held and Unsubscribed Addresses: Leave checked to receive details about any imported contacts that already exist in your account in the held or unsubscribed status.
Create new category: Check the box to "Create a new category" if needed. This will create a group of the emails being imported now for use in a campaign later.
Assign categories: Check box(es) to import new contacts into any existing categories in the account.
8. Click the "Import" button.

Import notes
1. Manual imports are not always available for every custom table. Typically integrations with other applications or the use of our standard commerce and meeting tables will not allow for manual import of data. 

FAQ

Can I delete all data from my custom table?

Currently, there is no way for Administrators to delete Custom Table records within the UI.  Instead, we recommend that records be overwritten with additional data via an import or an automation step. 
Please Note: It is possible to have Custom Table records be deleted by Support. However, this could result in additional fees. 

Why does preview merge the first record in a custom table and not the expected record?

The Preview step of the email template or campaign creation process is intended to preview design. At a general level the Preview page can verify that a merge tag works, but it is not intended to verify that the merge tag accurately represents records from a table. The Preview page does not contain logic that can evaluate tables with multiple rows.

For this reason, in the Preview step, the system will always pull the first custom table record.

The best way to verify that merges from a table are working properly is to use the Test step of a campaign. This is the only time that the system can accurately evaluate the segment and contact records to determine which row matches criteria.

Can I test an email template that contains merge tags from a custom table?

The short answer is yes. Templates, like campaigns, may contain merge tags that are pulling in information from custom tables. 

The system will allow you to send a test of a template as long as one contact record exists in the table from which the data is being merged. 

If no contact records exist in the table you will receive an "Alert test mailing failed error". 

Can a field name in a custom table be deleted?

We would not recommend deleting fields from your custom table(s) but instead would recommend that the field is edited to be hidden. To do this, follow the steps below:

1. Navigate to the Contacts dashboard.
2. Select "Configuration" from the left menu.
3. Choose the "Custom Tables" tile.
4. Hover over the table name listed that contains the field to display the "Edit" button. Click the "Edit" button.
5. Hover over the field listed to display the "Edit" button. Click the "Edit" button.
6. Check the box to hide the field.
7. Click the "Save" button. 

Note
Hiding the field will hide it everywhere in the UI other than when viewing the custom table itself in Configuration. You will not be able to see it on the contact profiles or when adding merge tags in your campaigns.

What does this error, "The following custom tables used in content must also be used in each segment:" mean?

If you're merging data from a custom table into your email or subject line, you may have seen this error when setting up a campaign:
[replace image]

The error means that a custom table is in-use in the campaign but the segment assigned to the campaign does not include records from that table.

Customers typically experience this error when in the testing phase of campaign building where the final segment has not yet been assigned. The draft campaign in many cases is assigned a default category or an internal test segment.

An easy way to fix the error for testing purposes is to edit your category or segment to add the table to it.

1. Exit out of the create-a-campaign workflow.
2. Navigate to the Contacts dashboard.
3. Search for the category or segment that is being used. When located, edit it.
4. Click open the Advanced Options menu.
5. Check any appropriate tables that are being used then click Save and Test. 

How do I set a default value for a field in my custom table?

When using merge tags in campaigns, it is important to consider the possibility that not all recipients may have a value stored in the field being merged. You can set default values for fields to account for the lack of data.  Follow the steps below to save a default value to a field in a custom table.

How-To
Navigate to Contacts > Configuration > Custom Tables.
Hover over listed custom table name in the library and click the "Edit" button that appears to the right.
Hover over the listed field name in the listing of fields and click the "Edit" button that appears to the right.
In Merge Options box, enter the value. Remember, this should be a generic data value that would be applicable to anyone who does not have a value stored in the field. It will be seen if this field is merged into a campaign.
Click "Save".

Can I use fields from a custom table in my subscribe form?

Use fields from your custom table(s) in subscribe forms and preference centers to capture data about your subscribers that can be used in segmentation, automation, personalization, and more. For example, include fields to ask subscribers to provide additional information about their pets, hobbies, purchase preferences, membership preferences and more.

If you have custom tables setup in your account, you will see selection menus when editing a form to choose to view fields from a table: [replace image]

Notes
At this time, forms can support fields from tables setup as a 1:1 relationship only. We're working to provide additional support for 1:Many & Many:1 table relationships in the near future.

Why can't I choose custom table fields in my new subscribe form?

You're building a new subscribe form and you discover that the fields in your custom table are not available for use in the form. Why?

The first part of this answer is that custom tables are not universally available for data inputs from form submissions. Only tables created in a specific set of circumstances will be available to forms.

If any of the following situations are applicable to your account, you will not be able to utilize those table fields in your form(s):
  1. Your table is not a 1:1 relationship. Forms currently support 1:1 table relationships, therefore, if your table is 1:Many, Many:1, or some other relationship, it will not be available.
  2. Your table was created using a standard table template. Only tables that were custom created in the UI, not from a template, are supported.
  3. Your table is part of either a standard integration like Salesforce or Eventbrite, or part of a custom data integration.
In these cases, the table will not be available. Please contact Support with questions or help determining why your custom table fields are not available to forms.

How do I add fields from a custom table to my subscribe form?

Follow the steps below to add fields to your subscribe or preference center that will submit data to fields in a custom table(s).

How-To
Navigate to the Assets dashboard. Locate and click "Forms" from the left-hand menu. 
Click "Create Form" to start a new build, or locate and edit an existing form in your library.
Add a new layout row to the form. Once added, you will need to assign it as a Field. Locate Fields on the right in the drag-and-drop form designer. Notice a new drop-down menu to display Email vs Custom Table fields. Choose "Custom Tables".
Another drop-down will display to choose the table (multiple tables may be in use). Choose the appropriate table.
Drag-and-drop the field(s) to the new layout row(s) on the left.

Notes
Supported table relationship for forms is 1:1 currently.
This functionality is available for tables that were custom created in the UI, they are not available to tables that were created using a standard table template.

This functionality is not available for tables that are used in integrations with Salesforce, Eventbrite, or custom integration tables.
Data collected in a custom table from a subscribe form can be sent back to your CRM via API.  See our API Guide for endpoint information.

Can subscribe forms submit data to custom tables?

Yes, you can include fields from your custom tables when designing Subscribe Forms and Preference Centers. This gives you the flexibility to capture additional data about your contacts and use this information in segmentation, automation, lead scoring and more. 

How do I setup a picklist for a custom table field?

A picklist is a list of data values saved to a demographic field.  Creating a picklist for demographic fields allows you to ensure data integrity.  If ever an import is made with non-matching values, you will be notified to correct the data. Follow the steps below to create a picklist for a data field in your custom table. 

How-To
Click on 'Contacts' in the top navigation.
Click on 'Configuration' on the left of your screen.
Click on 'Custom Tables' tile.
Hover over listed table name and click 'Edit' on the right.
Hover over your demographic field and click 'Edit'. This will open the Edit Field page.
Locate the desired field from the listing. Hover over and click 'Edit'.
You will see the 'Picklist Options' section on the right side. Manually add in as many values as you would like into the list by clicking the 'Add' button, or import a list of values.
Click 'Save' when done.

How do I rename fields in my custom table?

Easily access the fields in your custom table for updates. Follow the steps in this article to rename a field in a custom table in your account.

How-To
Navigate to Contacts > Configuration > Custom Tables.
Hover over listed custom table name in the library and click the "Edit" button that appears to the right.
Hover over the listed field name in the listing of fields and click the "Edit" button that appears to the right.
In the Field Alias box, enter a new name for the field.
Click "Save".
 
Notes
The "Edit" option for custom table fields allow renaming of fields, hiding fields from account administrators, saving a default merge value, and the ability to save a picklist of values to the field. Edit DOES NOT include the ability to change the format of a field. If the field was created as a Number field, it will stay in that format. You may choose to create a brand new field for the custom table as needed.

I'm receiving an error reading "There were problems with your mailing: Sorry but you have custom table tags in your content but those tables do not all exist in each of your segments."

You received  this error when trying to move past Step 3: Contacts in the Create a Campaign Workflow, but what does it mean?
In plain language, you have a merge tag in your email design that pulls data from one of your custom tables (ex. the Salesforce integration table). The segment(s) you are attempting to send to does not pull from that same table. 

For example, this email is attempting to include the Account Name field from a Custom Table named "Customer Information":

The segment "London Leads" does NOT pull data from that segment. We cannot guarantee that subscribers included in the London Leads segment would have data in the Customer Information table.

This error prevents you from potentially sending an email that looks like the above rather than pulling in the information you intended. 

There are two ways to fix this so that you do not encounter the error anymore and can move on to previewing and scheduling:

1. Remove the merge tag from your design. This is not the ideal solution, so we recommend the next option.
2. Update your segment to pull an item from the custom table in question. This means either checking the box under "Advanced" when creating/editing the segment to include the table the merge tag pulls from:
OR using the table some way in the segment itself.
For example, I can add the clause "CustomerInformation.AccountName is NOT NULL". This means that data exists in the Account Name field and my email will not send with the raw merge tag showing.

Why can't I access my custom table?

There may be a few reasons why you do not see / do not have access to Custom Tables in the Contacts dashboard of your account. (To find tables, navigate to Contacts > Configuration > Custom Tables). 

Possibilities could include:
You do not have the permissions set for your administrator record to access this feature. In this case, you should contact your Delivra site administrator.
Your account is not on the appropriate license to access this feature. Contact our Client Success team to confirm your license & features.
Your custom table was created prior to July 16, 2018 or your custom table required development work and therefore is not accessible in the user interface. In this case, you will need to submit changes needed to your Client Success manager. 

Can I use the API with my custom table?

The custom tables feature can support CSV data import, FTP file transfer, or REST API.  The SOAP API does not support custom tables.
See API documentation here.

How do I add a field to my custom table?

To add a new field to an existing table:
Click on Contacts from top navigation.
Click on the Configuration tab from the left-side menu.
Choose "Custom Tables".
Hover over listed table name then click the "Edit" button
Click on the "Add Field" button to create a new field.
Type or paste in a name for the field in the Field Alias box. The Database Name field will populate automatically based on the Alias Name provided.
Then choose the Field type: Date/Time, Decimal, Integer, Text.
Optionally, save a default value for the filed if it may be merged into emails and/or create a picklist of values that are allowed to be stored in the field.
Click "Save".

How do I add dates from my custom table into my email design?

If needed, its possible to add a merge tag to include a date from your extended table(s). This can be useful when setting up triggered mailings that will be used for multiple events with different dates and times or transactional mailings.

HOW-TO:

To add these timestamps, insert this code into a text field in the Drag and Drop editor:

%%clock format [clock scan [merge table_name.table_field]] -format {%A}%%

The bold section will determine where the date is pulled from and how it is displayed.

table_name - name of the table where the date field is located
table_field - name of the field containing the date information

Example of date formats:
%%clock format [clock scan [merge table_name.table_field]] -format {%A, %B %d, %Y at %H:%M %p}%% shows Monday, June 26, 2017 at 7:05 PM
There are several different ways to format this merge tag based on how you want the date and time to show. All are outlined in this related article: How Do I Add the Current Date and Time to My Email Design?

How can I search data in my Custom Table?

If you're using an integration for Contacts, like eCommerce or Eventbrite, you can run searches on the data stored. Many times these integrations create what is called an extended table (aka custom table), which is separate from the main contact table. The main table and the integrated table are joined based on the email address.

 Coming Soon to Air

How-To
Contacts dashboard

Go to Contacts
Click 'Find Contacts' icon (spyglass at the top right of the contacts table)
Use the drop down menu on left to scroll to bottom and find extended table
Click on (+) to display fields in that table
Check the box to select the field you want from your extended table
Enter a value in the text box
Click 'Search'

Custom report builder (Analytics)
Custom report builder availability depends on your license. Contact Client Success for questions.

Navigate to the Analytics Dashboard.
Choose Custom Reports from the left-hand navigation.
Click Create Custom Report at the top-right.
Choose "Contact Report".
Name your report and give it a description (description is optional).
Open the "Select Field" drop-down and search for the name of the extended table field you are looking for (I chose to look for object type in Salesforce to pull all Leads in the system).
Click Run Report once you are done creating clauses.
Use the drop-down on Run Report to choose additional fields to include in your report (I chose Lead Status and Lifetime Engagement).
Once you are satisfied with your report you can save it in the top-right and export using the three dots at the top-right of the report grid.

    • Related Articles

    • Understanding Table Relationships in Delivra

      Custom tables in Delivra support different types of relationships based on data needs. Choosing the right table relationship is crucial for ensuring efficient data retrieval and management. Table Relationships One-to-One Relationship In a one-to-one ...

    • How to Create and Manage Custom Tables

      How to Create and Manage Custom Tables in Delivra Creating a custom table in Delivra allows users to extend their contact data and better manage structured information. This section provides a step-by-step guide to creating and managing custom tables ...

    • Understanding custom table relationships

      What are tables? A table is a data structure that organizes information into columns and rows to resemble a spreadsheet. A table can be used to both store and display data in a structured format. Further, 1. A table has a set number of columns, but ...

    • Populating your Custom Tables with data

      Importing data into custom tables is essential for keeping records up to date and integrating data from external sources. This section covers different import methods and best practices. Import Methods CSV Imports: This manual method allows users to ...

    • Introduction to Custom Tables in Delivra

      Custom tables in Delivra provide a flexible way to store and manage structured data beyond the default contact record. This article introduces the concept of custom tables, their benefits, and when they should be used. What is a Custom Table? A ...