Working with Joins

Overview

From the Tables screen, you can add, edit, and view the table joins. A "join" is used to define a relationship between two tables in a database, thereby allowing the system to access fields in both tables. Joins are used throughout Messaging to give you access to fields beyond just what's in the source table for whatever asset you're creating. For example, you can access fields in a joined table when you define a Filter, or create Personalization within a message.

Note: This feature is not available for Load and Send Tables.

A common example of a join is connecting a Customer table (which contains contact information about an individual) with an Order table (which contains purchase order information). This type of join is called a "one-to-many" join because one customer can make multiple purchases. You could further join the Order table to an Order Item table that contains the details of the items contained in each purchase.

Messaging supports two different types of joins -- a "system join" (sometimes referred to as an "existing join") and a "property join."A "system join" is defined on the Table screen, and can be thought of as a more permanent join method, as these joins can't be deleted once defined. System joins are also faster than property joins. A property join is a "one-off" join method that's used in a specific context, such as creating Filter logic, and isn't saved. This topic focuses on how to create a system join.

Note: When creating a system join, it must be set up in the many-to-one direction. In the above example, you would select the Purchase table (the "many"), and create the join to the Customer table (the "one"), and not the other way around. Also, joins should be created only in one direction, not both.

Features

Create a New System Join

Click hereClick here

From the Table details screen of the "many" table, click Edit > Fields > New Join in the Tool Ribbon. The Join details screen is displayed.
In the "Display Name" field, enter a name for this new Join. This name is what will appear within the Messaging interface when you utilize this join to access fields on the joined table. The "Column Name" field is automatically populated based on what you enter in the "Display Name" field. The "Display Name" version of the join name is automatically converted to all lower-case, and spaces are replaced with underscores.
Optionally, enter a different name in the "Column Name" field. Only alphanumeric characters and underscores are valid entries in the Column Name (no spaces or symbols).
From the "Table to Join" drop-down menu, select the table to which you want to join.
By default, Messaging tables use a technique called "indexing" to resolve queries quickly. Indexing can be thought of like a table of contents or a glossary which allows for faster retrieval of data stored in the database. To turn off indexing, remove the check mark in the "Index this field for faster Filter performance" check box.
Click save.

View or Edit a System Join

Click hereClick here

Most of the information about a system join is "locked" and can't be modified once you save the new join. The only editing option available for a system join is its "display name."

To view or edit an existing system join:

Click "Joins" in the Functional Menu. The Workspace is refreshed to show all of the joins for this table
Click on the join that you want to view. The Workspace is refreshed to show the details of the selected join.
Optionally, to rename the join, enter a new name in the "Display Name" text box (you can't modify the Column Name or the joined table).
Optionally, you can toggle on or off indexing for this join.
To save your changes, click save.

Activate / Deactivate System Joins

Click hereClick here

Messaging doesn't allow you to permanently delete unwanted table joins; however, you can deactivate a join. An inactive joins still exists in the database, but you won't be able to select this join when working with an asset, such as a Filter. Inactive joins can also later be reactivated if you find that you need to begin using this join again.

To deactivate a join:

On the Table details screen, click "Joins" in the Functional Menu. The Joins screen is displayed, showing all of the active and inactive joins FROM this table to other tables. This screen also lists all the tables that are joined TO the currently selected table, but you can't modify those joins from here; you'd need to navigate to that joining table and modify it from there.
Locate the desired join name within the "Active Joins" section.
Click the change button ("X" icon) to the right of the join name. The system moves this join from the "Active Joins" section down to the "Inactive Joins" section.
In the Tool Ribbon, click Edit > Actions > Save.

To reactivate a join:

On the Table details screen, click "Joins" in the Functional Menu. The Joins screen is displayed, showing all of the active and inactive joins FROM this table to other tables. This screen also lists all the tables that are joined TO the currently selected table, but you can't modify those joins from here; you'd need to navigate to that joining table and modify it from there.
Locate the desired join name within the "Inactive Joins" section.
Click the change button ("X" icon) to the right of the join name. The system moves this join from the "Inactive Joins" section up to the "Active Joins" section.
In the Tool Ribbon, click Edit > Actions > Save.

Reorder System Joins

Click hereClick here

Messaging allows you to rearrange the table joins into a custom sequence. This sequence is only for presentation purposes, as it controls how the joins are displayed within the table "views" throughout the platform, such as in the Personalization Pane. You can customize the join sequence so that the most-commonly used joins are at the top in order to make it easier to find and select those joins. For example, let's say you often use the "Order to Customer" join when creating Filters. You could rank this join first, which would cause it to display at the top of the list of Existing Joins within the Personalization Pane on the Filter screen.

To reorder the joins:

On the Table details screen, click "Joins" in the Functional Menu. The Joins screen is displayed, showing all of the active and inactive joins FROM this table to other tables.
Located the desired join within the "Active Joins" section.
The system offers the following options for moving this join:
- Drag the join from its current position, and drop it into its new position.
- Using the Sequence Number field, enter a new position for this join. The system highlights this field in green to indicate that it's been changed.
Repeat step 3 as needed to rearrange additional joins.
In the Tool Ribbon, click Edit > Actions > Save.

Back to Tables