You can create a database view that contains information stored in more than one database. You do this using a special type of field called a database relation, which allows you to join two databases together. Once the databases are joined, you can create views, in either database, that use fields from both databases.
When you design a database, you need to decide whether information should be stored all together in one database or stored separately in two or more databases and then displayed in one view using a database relation. When you create such a view, you are joining the two databases together. The two most common reasons to use database relations are:
One to many relationships. If the same information needs to be repeated many times in each record of a database, it often makes sense to place that information in its own database and use a database relation to display that information in a view. For example, an inventory database includes the name, address and phone number of the part supplier. Part suppliers supply multiple parts in the inventory. Therefore, it's better to store supplier information in a separate database and use a database relation to display supplier information in the inventory database. For more information, see this detailed example.
Data used in many databases. If the same information is used in more than one database, store the information in a separate database. Then, using database relations, access that information from any database that needs it. For example, a parts database, containing part numbers and descriptions, could be used by both an inventory database and a purchasing database.
A database can have as many database relation fields as you would like, up to the 64-field limit for a database. However, each database relation field you add will slow down the performance of the database. The practical limit to ensure good performance is 5 to 6 fields.
Before you create a database relation, it's helpful to understand two key terms:
Source database. The source database is the database that is the source of the data you wish to include. It can be any database other than the one in which you are creating the database relation. In SQL (Structured Query Language) terminology, the source database is called the foreign table.
Destination database. The destination database is the database in which the data will appear. It is the database in which you are creating the database relation. In the example below, the Inventory database is the destination database. In SQL terminology, the destination database is called the master table.
Let's explore the case of an inventory database. One use of an inventory database is to help you notice when items need to be reordered. If you need to reorder an item, it's useful to be able to see the supplier's name, address, and phone number.
You could put all of these fields in the same database, like this:
If you do so, database users will run into two problems. First, they have to type in the same supplier information multiple times. Second, if the supplier information ever changes -- for example if the supplier gets a new area code -- you'll have to make that change to every record that contains an item from that supplier. In the picture above, Pete's Office Goods is listed with two different area codes. Clearly, it would be better to enter the supplier information only once; then you have to change it only in one place. You can do this by creating a database relation.
First, create a supplier database that contains information about all of your suppliers.
Next, join the inventory database to the supplier database using a Database Relation field.
Finally, create a view in the inventory database that shows only items that are out of stock, who supplies the out-of-stock items, and how to contact them.
These steps show how to create a database relation between an Inventory database and a Supplier database like the ones created in the previous example. In this case, the Inventory database is the destination database and the Supplier database is the source database (read more). These procedures assume you have already created the two databases.
Go to your destination database and click Manage in the Command Bar.
In the above example, you would go to the Inventory database.
Click Fields, and then click New in the Command Bar.
In the Field Name text box, type a descriptive name, like Supplier.
Choose Database Relation from the Type list.
Set the other general field properties, as appropriate for your database.
In the Database
Relation Properties section, select the name of the Source
Database from the list.
In the above example, you would choose the Suppliers database.
In the Field
dropdown, choose the field that joins the two databases. The field you
choose here appears as a dropdown list in the destination database.
In the above example, you would choose the Company field.
Note: Not all fields will display in the Field
dropdown, because some field types cannot be joined. These include:
Some field Types that are designated during field creation: Address (although Address cannot be joined, sub-components such as City, State, Street, Zip, and Country can be), Checkbox, Contact List, Database Relation, Email Address, File Attachment, Group List, Image, List, Member List, Text Multi-Line, and Web Link. Learn more about field Types.
Individual components of compound fields. Compound fields can be joined, but not subsets of those fields. For instance, "Name" can be joined with another field, but not "Name.Last Name".
The Created and Edited date fields. These are system-generated fields that are "built in" to your database. They are configured on the Modify Fields page.
Calculated Fields. These field types exist in several templates.
If you would like this field to appear as a clickable link to the Source Database, select Yes beside the Link to Record option. If you select No, the field displays as plain text.
Click Save in the Command Bar.
Now that you've created the database relation, it's available for you to use in views.
Once you've created a database relation between two databases, you can create a view that uses fields from both databases. You create this view in the same way you create a normal view, except the Choose Columns list now includes fields from both databases.
These steps show how to create a view in an Inventory database that uses fields from a Supplier database, like the databases created in the previous example.
Click Views on the Manage Database page.
Click New, or edit an existing view by clicking its name.
In the View Name text box, type a descriptive name, like "Out of Stock Items".
Scroll through the list of fields in the Choose Columns list. Note that fields
from both the destination (Inventory) and source (Supplier) databases
are available. Since you are creating a view in the destination database,
those fields are listed first, followed by the fields from the source
database.
The fields from the source database are displayed using the field name
in the destination database, followed by two colons "::", and
then followed by the field name in the source database. In some cases,
you may create more than one database relation between the same two databases.
If those cases, be careful to pick the correct field for the view based
on both the destination and source field names.
Add columns to the view by choosing fields from
the Choose Columns list and pressing
the Add to View button. Note that
as you add each field, the field appears in the Order
Columns bar directly below. Be sure to add at least one column
from the supplier database. This example adds a Phone
field from the Supplier database.
Continue creating the view. You can use fields from either database in the Sorting, Filtering and Totaling sections of the View Properties page.
With database relations, you can display data from two databases at the same time. However, these two databases might have different access levels to them. User access to data is controlled by the destination database, not the source database. Database managers need to exercise care in creating database relations, so as to avoid giving users access to data they would not normally have access to.
For example, you can create a Salary database and give a member called Foley No Access to that database. You can then create a Performance Review database that Foley has Read access to. If you add a database relation field to the Performance Review database whose source is the Salary database, then Foley will be able to read any Salary information that appears in the views in the Performance Review database.
Database managers cannot create database relations to databases to which they have No Access. In the Salary example above, if Foley were a database manager for the Performance Review database, he would not be able to create a database relation to the Salary database.
Important: Database managers need to exercise great care when using database relations to access secure information.