Say you need to create a report that includes information from two different tables. You create a relationship between the tables, of course, but then what?
To draw many values from different tables together, you just need to enhance your relationship with lookup fields.
When you create a relationship between two tables, one is the "master" and one is the "details" table. In other words, the tables have a one-to-many relationship. For example, if you have a table of authors you could relate it to a books table. ONE author can write MANY books. Therefore the Author table is the Master and Books is the Details table. Learn more about relationships.
The linchpin tying it all together is the reference field. The reference field links a record in your details table with a specific record in your master table (which it identifies by the unique value in the key field). When a user adds a detail record (like a book), the reference field usually offers a dropdown choice of values from a "master" field (like an author's name).
Tip: Often, QuickBase automatically populates a Reference field's dropdown list with values from the master table's key field. The key field is usually the Record ID field, which contains unique numbers that mean a lot to QuickBase, but little to your users. You can easily change the dropdown to display a field users recognize, like Author Name, by setting the record picker for the master table.
Sometimes the reference field isn't enough, and you want to show data from additional fields in your master table. For example, say you want your book record to show not just the Author's name, but also her email address and Web site. The email and Web site fields exist in the Master Authors table, and not in the Details "Book" table where you want them to display. Not to worry. Because you already have a relationship between the two tables, it's easy to draw these fields into the Details "Book" table. To do so, you'd create lookup fields in the Details "Book" table.
You can create a lookup field in any of the following three ways:
To create a lookup field from within
the fields tab:
In order to create a lookup field, you must have an existing relationship, or create a relationship between two tables.
In the table bar on any application page, click one table in the relationship. Within the menu that appears, select Customize --tablename-- table > Relationships.
Locate the relationship you want to change and click its Edit link.
Under the Details Table field list on the right, click the Add Lookup Field button.
Click the dropdown list that appears and select the lookup field from the Master table that you want to display in the Details table.
Repeat the steps above for each lookup field you want to add.
Note: Adding a lookup field from within the fields tab won't make it magically appear on reports or forms. You'll need to add it yourself by editing reports or customizing forms.
Often you've got a report up on your screen and you want to add a column of information to it. What if the field you want to add actually belongs to the master table? Do you need to leave the report, open the relationship and create the lookup field? Not with QuickBase.
To create a lookup field from within a report (and add it to the report in a single swoop), click the heading of the reference field that connects the details table to the master table. (For example, this might be the Author name field.) Within the menu that displays select Add a related column. QuickBase displays a list of fields from the master table. Select a field and click OK. When you do so, QuickBase creates the lookup field. To save it in the report, click Save at the top of the screen. If you don't save the report, but click Revert instead, the lookup field has still been created behind the scenes.
There may be times when you're in the middle of creating a report and you realize that you want to incorporate a field from a related master table. To create a lookup field at this point means extricating yourself from the Report Builder and navigating to the relationship properties page to create a lookup field and back again. QuickBase saves you the trip by letting you create lookup fields on the fly from within the Report Builder. (In order to do so, a relationship between the pertinent tables must already be in place.)
To create a lookup field from the report builder:
Within the Report Builder, under the Columns to Display section, select Custom columns.
Within the Available Columns list that appears on the left, select <Other Column>.
QuickBase displays a dialogue box listing related tables and fields.
Select the table and field you want to display in your report and click OK.
Note: If the field you choose is part of multiple relationships, QuickBase prompts you for more information. For example, say you're creating a report for the Tasks table and you choose the field Company from the Resources table. QuickBase may display a message like the one pictured below. Here, the program is telling you that the Task Creator, Assigned To and Project Lead fields all reference the Company field. In the case of the last selection, the path from Tasks to Resources goes through an intermediary table, Projects. QuickBase is asking you: Whose company do you want to see? The Task Creator's? The individual to whom the task is assigned? Or the Project Lead? Make a choice and click OK.
When you've made all your selections, QuickBase creates a lookup field and includes it in the report you're building.
Note: If, after adding a lookup field to a report, you also want the field to appear on a custom form, you must add it yourself. Read how to edit a form.