To configure relationships, go to the Tables area of your app, click Relationships at the top of the screen, and create or edit a relationship to open the New/Edit Relationship Settings dialog box.

The following sections describe the more advanced options for database relationships.

Display Values

The Display Value dropdown is only available for One-to-Many relationships. It allows you to choose a field from the parent table and display it in the associated field of the child table. When the child table is viewed in Datasheet or used in a DataPage, a dropdown of Display Values is shown instead of the ID values.

relationshipsetting011

You can extend the use of Display Values to all areas of DataPages by selecting the Use display value on DataPages checkbox. This ensures that the Display Value is used in all editable or non-editable fields in DataPages by default.

Join Type

The following join types are available in Caspio Table Relationships:

join_concept1

  • Inner Join – Includes all records when there is at least one match in both tables.
  • Left Outer Join – Includes all records from the left table and only the matched records from the right table.
  • Right Outer Join – Includes all records from the right table and only the matched records from the left table.

The default join type for all relationships is Inner Join. You can change this type by clicking Edit.

relationshipsetting021

Note: Full Outer Joins are not applicable to relationships, but are possible through views. To learn more about the different types of joins, see Creating a View to Join Tables.

Referential Integrity

Select the Enforce referential integrity checkbox to ensure that every parent record referenced in the child table exists in the parent table. When enabled, the system ensures that no child can refer to a parent record that does not exist. In other words, the ID of the parent record in the child table must be present in the parent table. Likewise, the parent record cannot be deleted or changed as long as it is being referenced in a child table.

relationshipsetting031

Cascade Update and Delete

For relationships in which referential integrity is enforced, you can enable cascade-update and cascade-delete options. When enabled, deleting a parent record or changing its ID value causes related child records to also get deleted or updated respectively in order to preserve referential integrity.

For example, if a customer is deleted and cascade-delete is enabled, all orders of the customer are also deleted. Or, if you change a customer’s ID in the Customers table and cascade-update is enabled, the Customer_ID field in the Orders table is automatically updated for that customer’s to ensure the relationship is preserved.