Create a Lookup Field

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

Creating a lookup field from within the Relationships list

In order to create a lookup field, you must have an existing relationship, or create a relationship between two tables.

To create a lookup field from within the Relationships list:
  1. Open the application which contains the relationship you want to edit.

    If you've created a relationship between tables in two different applications, open the application that contains the details table. (Don't know what this is? Read about relationships.)

  2. Open table relationships.

    On the Table bar, click the name of one of the tables in the relationship. Click SETTINGS in the Page bar, then click Table-to-table relationships.

    Note: Unless you've created a relationship across separate applications, you can select either the master or the details table to add a lookup field to the relationship.

  3. Click the relationship that you want to edit.

    QuickBase displays the Relationship properties page, which lists fields that make up the relationship. The left side of the page shows fields in the Master table and the right side of the page shows fields in the Details table.

  4. Under the Details Table field list, click the Add Lookup Fields button.

  5. On the New Lookup Fields page, select up to three lookup fields from the Master table that you want to display in the Details table.

  6. Click Create to save your changes.

  7. Click Done to return to the Relationships list.

Note: When you add a lookup field from within the Relationships list, it doesn't automatically appear on reports or forms. You'll need to add it yourself by editing reports or customizing forms.

Creating a lookup field from within a report

Often you've displayed a report 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):
  1. Click the button in the heading of the reference field that connects the details table to the master table.

  2. Within the menu that displays select Add a column.

  3. Click a table related to Tablename and then choose the table from the dropdown. QuickBase displays a list of fields from the table you selected.

  4. Select one or more fields and click OK. When you do so, QuickBase creates the lookup fields and adds them to the report.

  5. To save the report, click Save at the top of the screen. If you click Revert instead, the report changes are not saved, but the lookup fields have still been created behind the scenes.

Note: When you add a lookup field to a report, it doesn't automatically appear on forms. You'll need to add it yourself by customizing forms.

Creating a lookup field from the Report Builder

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:
  1. Within the Report Builder, under the Columns section, select Custom columns.

  2. Within the Available list that appears on the left, select <Columns from a related table>.

    QuickBase displays a dialog box listing related tables and fields.

  3. Select the table and field(s) you want to display in your report and click OK. QuickBase creates a lookup field and includes it in the report you're building.

    Note: If the field you choose is part of multiple relationships, QuickBase first 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 displays a message telling you that the Task Creator, Assigned To and Project Lead fields all reference the Company field. The question you need to answer is: 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.

Note: When you add a lookup field to a report, it doesn't automatically appear on custom forms. You'll need to add it yourself by customizing forms.

 

Related Topics:

 

Return to top   

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