Imagine that you run an art gallery and you want to use a QuickBase application to keep track of all your art works. You create a table called Works and begin to enter information on your pieces. You soon find that you have multiple works by the same artist and are forced to type the same name over and over. To make matters worse, every artist has contact information that you must have on hand. Are you really going to clutter up your Works records with the artist's address and telephone number too? There must be a better way! In fact, there is. The solution is to create a special table to hold information on all your artists and create a relationship between the Artists and Works tables. Doing so makes life much easier. This approach lets you create a lookup list on your Works form, which displays all records in the Artists table. Instead of typing the same information over and over, just select an artist from the list each time.
A relationship is a link between two tables. When you create a relationship, you’re telling QuickBase to connect a single record in one table to one or many records in another table. (You'll read about the nuts and bolts that hold them together in minute.) What a relationship does is save space and effort. You don't need to enter extra info about the artist in the Works table, because Works can reference the relevant artist record in the Artists table. That way, each table concentrates on what it does best.
Relationships are a great way to work smarter. As in the gallery scenario outlined above, a relationship means more efficient data entry because you're not entering the same information over and over again. There's also less chance of error when you're entering information in one place. The icing on the cake is that relationships also let you make sure that no one enters an invalid entry in the field. For example, someone entering an art work can only choose one of the artists in the gallery's stable.
You can create a relationship between any two tables. Most often these tables reside within the same application, but you can create a relationship with a table in a separate application too.
Following our Gallery example, the relationship between Works and Artists would function like this:
|
|
When you're adding a new Work, QuickBase lets you select the related artist from a dropdown list. |
Each Artist record features an Add Work button that creates a new work by that artist. QuickBase also creates a hyperlink called Works. Click it to display a list of related works. Or, if you prefer, you can insert a list of these records instead of a hyperlink. (Read the highlighted TIP that follows.) |
TIP: That artist record can be much more informative. Instead of including a hyperlink to all works created by the artist whose record you're viewing, you can actually embed a list of the related works (see illustration). Read how.
When you create a relationship between two tables, QuickBase asks you where each table stands in a one-to-many relationship. A quick analysis of the data you have in each table should provide you with the answer to this question.
For example, return to the art gallery scenario presented at the beginning of this topic. The gallery QuickBase application contains two tables: the Artists table and the Works table. Does one work have many artists? Not usually. Instead, one artist creates many works. Another way to think of this is: What information do you want to be able to look up as a result of this relationship? In the gallery, when someone is creating a new record for a work, he or she wants to be able to select an artist from the dropdown. This one-to-many relationship between tables is also referred to as a relationship between a Master Table (the one) and the Details Table (the many). The master supplies the dropdown selection to the details table.
These two tables are related. In the details table (Works), QuickBase created a field called Related Artist. This field connects to the key field (usually Record ID) in the master table (Artists). Match up the numbers and you'll know who created each artwork. Because this field refers to master table values, it's called the Reference Field. But how do your users know that Matisse is number 6? They don't. To help them out, add a lookup field to provide more information about master records (see next section). Or better yet, create a reference proxy field and display that instead of the reference field.
Tip: Learn how to create a relationship and see how it
works ----> 
Fields that Comprise a Relationship
The heart of a relationship is the Reference Field. QuickBase creates this field in the details table. It contains the value of the related master record. To keep everything straight, QuickBase uses the master table's key field to populate the detail table's reference field. Why? Because the key field always contains a unique value.
Tip: Because the reference field contains the master record's key field, it usually displays as a number. While this number means a lot to QuickBase, it probably doesn't say too much to your users. For instance, instead of "Company Name" they'd see the company's Record ID#. Help your users out, by designating a reference proxy field and using that field in reports and forms instead.
When you create a relationship between two tables, QuickBase creates (or lets you create) additional fields to enhance the relationship, including:
Lookup fields. These appear in the details table and provide more information about a linked record in the master table. For example, say you want to show additional information on an artist within the works details table, like first name and birth and death dates. (Learn how to create a lookup field.)
Report Link fields appear in the master table. These display as hyperlinks. When a viewer clicks on one, QuickBase displays related record(s) from the details table. Report links help your users move between tables quickly to get more information. For example, if you were to click on an artist's Works link in Figure 2, below, QuickBase would display a list of works by that artist.
URL (formula) fields appear in the master table as buttons. These buttons let a user add a new record to the details table from within the master table. The record will be related to the master record in which you click the button. For instance, if you clicked the Add Work button in Figure 2's first row, QuickBase would display an add record form with Alexander Calder already selected as the artist.
Summary fields appear in the master table and display data from the details table. Most often, summary fields calculate totals. For instance, the Artists master table in Figure 2 features a summary table called # of Works. This field calculates the number of works in the Works details table that are linked with each artist. Alexander Calder has two related works in the details table. Summary fields can do other tricks too, like return the record with earliest start date, for example. (Learn how to create a summary field.)
These field types are important features of any relationship and can greatly enhance your QuickBase application.
Note: Be sure to add both the Report Link and URL (formula) fields to all custom forms on which you want them to appear. (Read how.)
© 1999-2010 Intuit Inc. All rights reserved.
