About Relationships

Imagine that you run an art gallery and you want to use a Quick Base 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 Quick Base to connect a single record in one master table to records in a details 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 table.

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. Quick Base also creates a link called Works. Click Works to display a list of related works. Or, if you prefer, you can show a list of these records instead of a link. Read how.

One-to-many relationships

When you create a relationship between two tables, Quick Base 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 from the beginning of this topic. The gallery Quick Base 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.


These two tables are related. In the details table (Works), Quick Base 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. To help your users 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 Quick Base relationship is the Reference Field. Quick Base creates this field in the details table. It identifies the related master record. To keep everything straight, Quick Base uses the master table's key field to populate the details 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 Quick Base, 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.


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

These field types are important features of any relationship and can greatly enhance your Quick Base application.

Related Topics:


Go back      |       |   

© 1999-2018  QuickBase, Inc.  All rights reserved.  Legal Notices.