Relationship Table

Relationships are the essential Building Block that enable complex linkages across diverse sets of data. Relationships are essential to enabling complex, interwoven content.

• Relationships refers to the conceptual Building Block, one of the 5 fundamental building blocks
• Relationship Table refers to a set of relationships defined by the builder. An example is the “employee-employer” relationship table that links some “Employees” to some “Companies”
• Relationship refers to a specific instance of a relationship table, usually created by a user. An example, Tom Smith is an “employee” of Acme Company, and Acme Company is the “employer” of Tom Smith.

“Creating a Relationship Table” refers to the act of creating the database entries to allow for two or more tables to be related. Relationships themselves are typically created by the user during the normal course of using the application.

Relationships are created in context during the normal course of building your application. For example, while creating in the Query Builder, or while adding a selection field to a field grid. Specifically, there are four contextual methods for creating relationship tables:

Method 1: Using the Query Builder Method 2: From within the list wizard Method 3: From within the selection wizard Method 4: From within the Query Builder tool, specifically, when using the “Add Table” Wizard

Methods 1 through 3 above will launch the “Add Relationship” wizard. Method 4 above will create a relationship table automatically.

The “Add Relationship Table” wizard needs to know 4 things. However, it often already knows at least 2 of the answers, and doesn't prompt for these. The questions are:

1. What table is on the “left” side of the relationship
2. What table is on the “right” side of the relationship
3. What is the label for the “left” side of the relationship
4. What is the label for the “right” side of the relationship

As an example, lets assume that we want to create a new Relationship Type that connects Companies to Employees as employer-to-employee. Our answer to question 1 is “Company”, 2 is “Employee”, 3 is “employer”, and 4 is “employee”.

In many cases, however, there is already significant context for the relationship table, and as a result, the answers to questions 1 and 2 may already be assumed. For example, if you are adding a list to a page about a Company, the wizard knows that at least one side of your relationship is probably Company, and answers question 1 for you. It may present a very simple select box to let you choose the answer to question 2.

In the Query Builder the answers to questions 1 and 2 are both known, and the wizard asks you for the answers to questions 3 and 4. It always assumes that these answers are simply the name of the tables and pre-populates those responses.

In a rare case (How to Create: Method 4 above) the wizard assumes all four answers.

NOTE: The terms “left” side and “right” side are meaningless in WorkXpress, they are just a means of establishing an order for answering the four questions, and were required for choosing a direction to display the Relationship out of context. Within the application, however, the relationship will always have context. This means the relationship could display as “employer to employee” on a Company page, but as “employee to employer” on a Employee page. The original sides lose all importance when in context.

Creating a new relationship table typically does not involve any automation in and of itself. Depending on the context, other Blocks may create automations around the relationship. For example, when creating a selection field, the field itself will automate the creation of some actions to properly manage the creation and deletion of relationships of this type.

Let's say you are a payroll company that deals with many companies.

You have a Company table and an Employee table. On the Company table, you can have a multi-select field (Employees) to select all the employees that work for that company. On the Employee table, you have a single select field to select the company the Employee works for (Company).

Both of these fields manage the same relationship, Employee to Company.

However let's say you also want to know which employee is the owner. To do this, you create a single field on the Company table called Owner and have this manage the same relationship (Employee to Company) as the other field (Employees).

Or, you can create a new relationship called Company to Owner that will be managed by the Owner field on the Company table. So you have two relationships between the same two tables, but the relationships are handling different information.

Relationships are used primarily within the Query Builder. They are most commonly a tool to define connections between tables, thereby allowing the identification of exactly the right records for a given purpose.

To edit the settings of a relationship table, you must use the WorkXpress Handbook.

WorkXpress relationships are able to function in a one-to-one, one-to-many, or many-to-many role without additional configuration. It all depends on usage. You can use selection field relationship management to force a relationship to function as one-to-one or one-to-many. A single selection field managing a relationship will force the relationship to function as one-to-many. A single selection field on each side of the relationship would force the relationship to function as one-to-one. A multi-selection field managing the relationship will aid in relationship creation, but will not change the usage of the relationship.

WorkXpress relationships also have a unique usage. Like tables, they are a place with which a field can be associated, and as a result, on which data can be stored. Storing data on relationships from a technical standpoint is the equivalent of creating a “many-to-many” data table in a relational database. However, it does not require the intermediary tables commonly associated with that type of relationship. Using a relationship as a data storage location, rather then creating an intermediary table, greatly simplifies the architecture of many common functions, such as attaching Products to an Invoice via a Line Item. In WorkXpress, you simply store fields like “Quantity” and “Extended Price” on the relationship itself!

There are two methods to create a field that is associated with, or “about”, a relationship.

* Using the Block Creator, click to create a field. For the table to attach the field to, choose the relationship you want the data to be stored on. • Using the Block Editor, edit an existing field, go to the block association tab, and attach the field to the desired relationship.

Referential integrity is required when you wish to enforce the existence of both sides of a relationship at all times. Traditional concepts of referential integrity can easily be enforced with simple actions. In WorkXpress referential integrity represents more of a validation process using actions than a strict data process.

However, WorkXpress does not allow a relationship to exist with only one side. Recycling or deleting an item necessitates the recycling or deletion of any relationships to it:

• When a record is recycled, its relationships are also recycled. Restoring that record also restores its relationships.
• When an record is deleted, its relationships are also deleted. This deletion is permanent and cannot be undone.