Table Relationships
Once you have created a Table for each focus area in your database application, you can define relationships between your Tables. The Relationships window provides a drag-and-drop interface to create relationships simply by dragging fields from one table to another. Then you can then easily create Views, Forms, and Reports that display information from several Tables at once.
Steps to create a new relationship:
1. Open the Relationships window from the Tables view or View menu.
2. Add the appropriate tables from the left-hand Tables sidebar.
Drag the relevant tables to the canvas or simply click their checkboxes. When you are done adding tables to the canvas, you can close the Tables panel to access a larger canvas work area.
3. Create a new relationship by dragging and dropping fields.
There are several ways to create relationships between fields in different tables. The most efficient way to associate two fields is to simply drag a field from one table to a field in another table. You can also double-click on a field or select the New Relationship button from the toolbar.
4. Set the relationship settings.
Once you drag your associated field, the Relationship Settings dialog will immediately pop up for you to set your Parent and related tables, associated fields, and relationship settings. The left Parent Table is the primary source of data and the right Related Table is the associated information often referred to as "child table".
Caspio auto-detects which fields you can associate based on matching Data Types and displays the available fields the Parent and Related dropdowns. You can only create a One-to-Many relationship when one field is unique, and you can only join Autonumber fields with Integer Number 
fields. If required, you may have to update your field data types, which you can access from the Relationships window by clicking the Design 
button in the top menu or right mouse-clicking on the table.
Caspio supports both One-to-One and One-to-Many relationships and auto-detects the appropriate Relationship Type when you select your fields. When creating one-to-many relationships, you have the option of displaying values from the parent field in the related table’s dropdown. This dropdown becomes a default form element in any related DataPage, and is also displayed in Datasheet view, as shown below.
Understanding Relationship Types:
One-to-many relationships are automatically set when one of the related fields is unique. For example, if a customer (a Customer ID Autonumber in the "Customer" parent table) can place multiple orders (records in the "Orders" related table), then the relationship is one-to-many as shown below.
To create a one-to-many relationship, take the parent field from the unique "one" side and add it as an additional field to the "many" side (the child table). So using our example, drag the Customer ID field in Customers table to the corresponding customer field in the Orders table. Then, each order will always be associated to the right customer.
One-to-one relationships are automatically set when neither of the related fields are unique or both fields are unique. In other words, each record can only have one matching related record. When you identify this type of relationship, both tables must share a common field.
Understanding Join Types:
Once you have selected the parent and related fields, they are "joined" to maintain this relationship as you design your applications and are automatically populated in Views and DataPage wizards. The data type of joining fields must match and the default join type is "Inner", but you can choose additional options by clicking the edit button.
- Inner: Includes only matching records which are present in both Tables.
- Left Outer: Includes all records in Left Table and only matching records in Right Table.
- Right Outer: Includes all records from Right Table and only matching records in Left Table.
Please note that Full Outer Joins are not applicable to Relationships, but are possible in Views.
Understanding Referential Integrity:
Select the Enforce Referential Integrity checkbox to ensure that related data between these tables remains connected and orphan data is prevented. For our example above, this setting ensures that new records in the Orders table only contain the IDs of valid customers and that related data between these tables remains connected.
Please note that Referential Integrity requires certain conditions:
- Cannot change or delete a record in the Parent table if there is a matching record in a Related table. For example, you will not be allowed to delete or change the Customer_ID in your Customers table when there is a pre-existing shipment assigned to that customer.
- Cannot add a new record to the Related table if there is no matching record in the Parent table. For example, you will not be able to add a new shipment without matching it to a pre-existing customer.
- Cannot change Related Field's value to something that does not have matching record in the Parent Table. For example, you will not be able to change Customer_ID value in the Shipments table to a value that does not exist in the Customer table.
Modifying Relationships:
To edit a relationship once it is created, you can double-click or right-mouse click on the line between the tables. To delete a relationship, you can right-mouse click or select the line and press the Delete key on your keyboard.
Importing and Exporting Relationships
Table Relationships can be imported or exported between Caspio and Microsoft Access. Caspio's Import wizard and Scheduled Tasks automatically detect the relational associations, required data types, and any referential integrity restrictions so that your data always stays clean when importing additional data.
Relationship Effects on your Apps
Caspio Bridge manages and maintains your relationship settings throughout your application design and operation. You will see the effects during data entry and manipulation in Web Forms and Datasheet, table import/export, DataPage field configuration, and Scheduled Import Tasks.