Creating a View to Join Tables
Views can be used to join multiple tables, where columns from tables are unified to display in a single table. Joined tables must be related to each other and have associated fields for the view to work. In Caspio Bridge, joining tables is useful when creating one-to-many relationships. In one-to-many relationships, one record from the primary table has many records in the child table. For example, a real estate agent (one) may have multiple property listings (many).
Steps to join multiple tables:
1. Make sure your tables are using key values
To join multiple tables these tables must contain key values. Key values are values that are used to match up records that are associated.
In this article, you create a view that joins two associated fields from two tables. The instructions below provide an example that shows how to display agents with active property listings in a view.
- In the Caspio Bridge homepage, select an app to which you are looking to add two new tables and a view. Click Open.
- First, make sure you have one table of agent profile information, and add a few unique agent accounts. The Agent_ID field will later be used as a key joining field in the view.
- Create a second table of property listings for agents listed from the first table. In Datasheet, add a few sample property listing records for more than one agent. Ensure the Agent_ID values matches the available Agent_ID values in the first table. The Agent_ID field will later be used as a key joining field in the view.
- Next, select Views on the sidebar menu and click New View.
- On Create a New View screen, name your view. Only letters, numbers, and underscores are accepted (no special characters, spaces, or punctuation). Next, to select a table to filter, move a table from the Available Tables list to the Selected Tables list. In this scenario, the two tables created are selected.
- On the View Properties screen, you need to associate key fields from the two selected tables. By default, view field names are created by combining the name of the table, a period, and the original field name. Choose your key fields to associate in the left and right dropdowns, and use the radio buttons under each dropdown to indicate which records to include from each table. Click the + button to add this relationship in the view.
Views support the following join types:
- Inner join – Include only matching records which are present in both Tables.
- Left Outer Join – Includes all records in Left Table and only matching records in the Right Table.
- Right Outer Join – Includes all records from the Right Table and only matching records in the Left Table.
- Full Outer Join – Includes all records in both tables, even if no matching records exist.
- Join types can also be set at the table-level by creating Relationships.
The Relationships window provides additional data integrity controls and so that field relationships and restrictions are automatically detected when creating Views and DataPages. You can also choose to make a table editable. This is only recommended if this view is for internal use because data deleted from views are also permanently removed from its base tables.
In this scenario, select the Agent_ID field in the dropdowns from each table. This is the associated field used to form a relationship between the two tables. Select Include only matching records for both dropdowns to create an inner join relationship. This means that the data returned by the view are matching records from both tables. If there is an agent in first table, but this agent does not have any property listing records in second table, then the view will not display any records for this agent at all.
8. On the Field Properties screen, you can edit the field properties for each field in the tables. You can remove any fields that do not pertain to your current task or choose to filter records. In this example, you can filter results to only include property listings that are active. Click Finish.
9. Click Open of your newly created view. Agents with active property listings records should only display in the view. You have the option to use this view as an Authentication data source for a password protected DataPage.
In this article, we’ve seen how views are used to join multiple tables with different join types. Views are used so that a single record can access all of the information included in multiple tables. The tables must be formatted using keys to associate the records of the two tables.