About Relationships

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.

What is a relationship?

A relationship is a link between two tables. When you create a relationship, you’re telling QuickBase to connect a single record in one master table to records in a detail 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:

 

 

 

 

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 how.


 

 When you're adding a new Work, QuickBase lets you select the related artist from a dropdown list.

One-to-many relationships

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 (one) and the Details Table (many). The master table supplies the dropdown selection to the details table.

artistworkrelationship.png

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.

The Fields Involved in a Relationship

The heart of every QuickBase relationship is the Reference Field. QuickBase creates this field in the details table. It identifies 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 "Project Name" they'd see the company's Project ID#. Help your users out by designating a reference proxy field and using that field in reports and forms.

masterdetail.png

When you create a relationship between two tables, QuickBase creates (or lets you create) additional fields to enhance the relationship, including:

detailstablelookupfield.png

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.)

 

This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.

 

Return to top   

© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.