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.
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.
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 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:
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 links. When a viewer clicks on one, Quick Base 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 an artist's Works link in the image below, Quick Base would display a list of works by that artist.
URL (formula) fields appear in the master table, and are displayed as buttons on forms and reports. 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 the first row of the image below, Quick Base 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 the image below 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. Data from these fields is stored within Quick Base, and therefore can be exported and used in formulas. (While summary and table reports calculate totals and averages for you, these values do not actually exist in a field within Quick Base. If you export the report data, the total/average data is not exported.) (Learn how to create a summary field.)
These field types are important features of any relationship and can greatly enhance your Quick Base application.
© 1999-2018 QuickBase, Inc. All rights reserved. Legal Notices.