Relations

Relations are connections between two tables in a Manifold project that allow one table to show columns from another table. A relation between tables uses a key field with unique values common to both tables to connect the two tables. The same field type must be used in the columns being related. This allows data that appears in one table to also appear in another table without having to physically duplicate the data.

 

To form a relation between two tables

 

1. Open the table that is to display the additional fields and click on Table - Relations.

2. images\btn_new_thing.gif Click on the New Relation button. This launches the Add Relation dialog.

3. In the Add Relation dialog, choose another table from the list box.

4. In the Add Relation dialog, click on one field for each table that will be used to match records and press OK.

5. Back in the Relations dialog, check the desired columns from the other table. Press OK.

 

Note: The New Relation button will not be enabled unless there is at least one other table in the project. To form a relation with a table using an external data source, first link a table in the project from that external data source. This creates a new, linked table in the project. We can then form a relation with that new table.

 

Columns that are "borrowed" from another table will appear in the table with yellow background color to indicate they are imported. They may be used like any other column, for example, for sorting, filtering, within formulas, within rank columns or for thematic formatting. Tables may have more than one relation with more than one other table.

 

Columns that are included from other tables may not be used as a key field to form a new relation. Columns can only be linked through one relation. They cannot be passed on in turn through yet another relation. For example, if table A has a relation with table B so that columns B1 and B2 appear in table A, table C cannot form a relation with table A and "borrow" columns B1 and B2 from table A. To include columns B1 and B2 in table C, table C must form a relation directly with table B.

 

Relations Dialog Commands

 

images\btn_new_thing.gif

New Relation - Add a new relation.

images\btn_delete_thing.gif

Delete - Delete highlighted relation.

images\btn_relations_includeall.gif

Include All - Show all columns from the related table in this table. Check all checkboxes.

images\btn_relations_includenone.gif

Include None - Do not show any columns from the related table in this table. Uncheck all checkboxes.

images\btn_relations_inverse.gif

Include Inverse - Show all columns from the related table that were hidden and vice versa. Toggle all checkboxes.

images\btn_relations_properties.gif

Properties - Edit the highlighted relation by calling the Add Relation dialog.

 

Example

 

Relations are used to add columns to tables from other tables.

 

images\tbl_relations_eg01.gif

Suppose we have a Customers table with a CustomerID field and a ContactName field.

 

images\tbl_relations_eg02.gif

Suppose also we have an Orders table with OrderID, CustomerID and an OrderDate field.

 

We would like to create a relation that shows in the Customers table the OrderID and OrderDate columns so that for each customer we can see all orders for that customer as well as the date of order. To do this, we open the Customers table and choose Table - Relations.

 

images\btn_new_thing.gif In the Relations dialog we click the New Relation button to add a relation. This opens the Add Relation dialog.

 

images\dlg_relations_eg01.gif

In this dialog we choose the Orders table in the list box and then highlight CustomerID as the linking field in both the Customers table (the left pane) as well as in the Orders table (the right pane, under the list box showing the Orders table). Note that we could have used any two fields that contain matching values even if they are named differently. However, as a matter of sensible database organization it makes sense to give the same name to the field used to save customer identification in all tables. We press OK.

 

images\dlg_relations_eg02.gif

The result back in the Relations pane is that we have a new relation listed as CustomerID: Orders.CustomerID. This is a shorthand way of saying that this relation is determined by matching values in the CustomerID field in this table to the CustomerID field in the Orders table.

 

We check the fields (columns) we would like to include from the Orders table and press OK.

 

images\tbl_relations_eg03.gif

 

The result in the Customers table window is that two new columns appear, the contents of which are taken from the Orders table. These contents are shown in yellow, which is a generic background color used to indicate values that are computed or otherwise derived.

 

Converting to Regular Columns

 

Right click on a relation's column head and choose Flatten to convert the column to a regular data column. The current value of each cell will be used to populate the regular column and the link to the other table will no longer exist.

 

Notes

 

A table may have more than one relation. For example Table A might include two columns, B1 and B2, by way of a relation with Table B and Table A might also simultaneously include one more column, C1, by way of a relation with Table C.

 

Relations can exist between any tables that exist in the project. In particular, a drawing's table can have a relation with another table.

 

Relations can exist between tables that are imported into a project and tables that are linked into a project. If we only have one table in a project we cannot form any relations because there is no other table with which to form a relation.

 

When including fields from tables that are linked into a project, keep in mind that tables linked into a project may be provided by files or OLE DB providers that might be participating in multi-user sessions with other programs. That's usually the objective of including such external tables, of course, but one should not be surprised if data in columns that are included from external tables appears to change without user intervention.