Create a Relationship

You can create a relationship between two tables within an application, or even between two tables in separate applications (though these applications must reside within the same billing account). If you're not quite sure what a relationship is or why you might want to create one, read about relationships to find out.

Creating a relationship across applications

If you're creating a relationship between two tables in different applications, you first need to open communication between them. To do this, you'll open one application and give the other application permission to create a relationship with it. After that, follow the steps for creating a relationship in the next section. To give permission:

1. Open the application that contains the master table that you want to relate. (Don't know what a master table is? Read about relationships.)

2. In the menu bar, click Customize > Application. On the left side of the Settings tab, click Cross-Application Relationships.

3. Click the Add Application button.

4. From the list of applications that appears, select the application with which you want to create a relationship and click OK.

   •  

   QuickBase lists your application in the Cross-application Relationships section.

5. Select a role.
   
   If you need to limit access to certain records in the master application, this is an important step. Your choices here depend upon what roles you've set up for this (master) application. The role you choose affects what information is available to the detail application. For example, say your HR department maintains an application to track information on all your employees. Your sales department has a separate marketing application and they would like to create a relationship with the HR application to populate some of their fields. However, you don't want the sales application to be able to see confidential employee information. The answer is to create a role called "sales" in the HR application that limits the report to only those fields you want salespeople to see, like name and telephone extension. Then, grant the sales application the ability to create a relationship with the HR application, specifying that it can only access the HR application in the "sales" role.

   Note: Creating a relationship with a master table does NOT give users of the detail application automatic access to the master application, nor is it necessary to do so. For example, users of the Sales application just discussed could see only HR information that the relationship draws into the Sales (detail) application. Additionally, they can only see it from within the sales (detail) application. QuickBase would prohibit Sales users from directly accessing records in the HR application unless the manager of the HR application had explicitly shared the application with those users.

6. Click Save.

7. Follow the steps in the next section to create the relationship.

To create a relationship:            

1. Open the application that contains the tables you want to relate. (If you're creating a relationship between two applications and you followed the instructions in the preceding section, open the other application—the one you didn't set permissions on. This application should contain the details table)

2. In the table bar on any application page, click the name of one table you want to relate. Within the menu that appears, select Customize --tablename-- table > Relationships.

3. On the upper right of the Relationships tab that displays, click the New Relationship button.

4. From the drop-down lists, select the two tables that you want to relate.

   • If you want to select a table from a different application, choose <Select another table> from the dropdown on the left side of the dialog box. Then select the application in which the table resides and click OK. Next, select the table and click OK. Your selection appears in the dropdown.

   • Click Next after you've selected both tables.

5. Select the option that describes the type of relationship you want to create, and click Next.
   
   Within the Create Relationship - Step 2 (of 3) screen, you're telling QuickBase which table should be the detail table and which should be the master table. The detail table draws and displays values from the master table in what's called a one-to-many relationship. One record in the master table could apply to many records in the detail table. For example, one Project may have many Tasks associated with it. (For help with this choice, read about relationships.)

6. Set relationship properties.
   
   The Create Relationship - Step 3 (of 3) screen shows you how QuickBase will set up your relationship. It provides some basic information and lets you make a few choices.

7. • Master Table. The left side of the screen shows you relationship components that live in the master table (the "one" in "one-to-many").

     • The Key field is always what QuickBase uses to track related records. Here the program tells you what your master table's key field is.

     • A Formula URL field is a feature QuickBase adds to your master table. This is a shortcut that lets users click a button to add a detail record that relates to whatever master record they happen to click from. For example, a project record might feature a button that says "Add a Task."

     • The Report Link field is a hyperlink within a master record that opens a report of related detail records (like all tasks related to the project). You can even display this field as the actual list of detail records, if you want. (Learn how.)

       • To add Formula URL and Report Link fields to your default display form, turn on the Automatically add the Formula URL and the Report Link fields to the display form checkbox. (Read more about forms.)

   • Details Table. On the right side of the screen, you can set features to display in your details table (the "many" in one-to-many")

     • The reference field is the linchpin tying your relationship together. This 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 task), the reference field offers a dropdown choice of master records. When you create a relationship, QuickBase creates this field for you and that's the option that automatically appears in this dropdown. If an existing field would make an appropriate reference, QuickBase offers it in the dropdown as well, though usually, you'll want the program to create a new field for you.

     • Lookup fields enhance the relationship by providing additional information about records in the master table. For example, task record related to the project, you may want to include the Project Description field along with the Project Name field. You can add lookup fields by selecting them from the dropdowns, but you don't need to make lookup field decisions right now. You can add lookup fields at any time.

   Once you've made your choices on the Step 3 (of 3) screen, click Save.

   •  

8. Tell QuickBase if you want this relationship's fields to appear on forms.

   QuickBase displays a prompt that asks you if you want features of this relationship to show up on data-entry and display forms. Master and Detail forms offer different options:

   • Master forms can feature an "add detail record" button or link as well as an embedded report of related detail records.

   • Detail forms can feature reference fields (which let you choose a related master record) and lookup fields (which tell you more about the related master (see Step 6 for more details).

   Turn on the checkbox for each form you want to feature relationship fields and click OK. QuickBase displays the Relationships tab.

9. To see the details of the relationship you just created, click its Edit button.

   • QuickBase displays the Relationship Properties page, where you can do the following:

   • View the details of a specific field by clicking the plus sign (+) next to the field name.

   • View the properties of a specific field by clicking the field name.

   • If it doesn't already exist, add a view link to your master table by clicking the Create Report Link button.

   • If it doesn't already exist, add a URL (formula) field, which is a button in your Master table. A User can click this button and create new record in the details table directly from within the master table. Click Create "Add record" (this button is named after your details table. It might be called Create "Add Task" or Create "Add PurchaseOrder" depending on what your detail table is called).

   • Add a summary field to the master table for this relationship by clicking Add Summary Field.
     
     Tip: If you want to add more Summary fields, use the plus sign icon that appears when you hover your mouse over an existing summary field. You can also use the minus sign icon to delete any summary fields you've already entered.

   • For details on these fields and more, see About Relationships.

   Note: If you've created a relationship across applications, the relationship won't display in the Relationships box. To access its properties, open the application containing the Details table. Next, open the reference field's Properties page. Then click the Edit the Relationship link which appears on the Properties page.

10. If you want, designate a reference field proxy.

    • When your reference field appears on reports and forms, 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 "Company Name" they'd see the company's Record ID#. Help your users out, by designating a proxy field to stand in for the reference field. When you do so, QuickBase displays another, more descriptive field to users to help them choose a master record.

11. Configure the relationship's reference field.

    • When you create a relationship, QuickBase automatically creates a reference field. This field appears in a detail record and lets users select the master record that the detail is related to. You can have this reference field appear as a dropdown list, or you can have it initiate the QuickBase Record picker. See illustration below to understand the difference.
      
      

    Dropdown List - The Project Name Reference field displays as a dropdown list.	Record Picker - When you click the Reference field and select Browse Choices, a Record Picker dialogue box appears.

    QuickBase reference fields automatically show a dropdown list. If you'd prefer the Record Picker, edit the reference field's properties page (on a form, right-click the name of the field and select Edit the field properties for this field). When the properties page displays, scroll to the Reference Field Options section and turn on the On add/edit forms use the Record Picker rather than a drop-down menu checkbox.

    Note: Once items in the master table number more than 1000, QuickBase automatically switches to the record picker. 1001 is just too many items to display in a dropdown field.

Related Topics

• About relationships

• Creating a relationship

• Designating a reference field proxy

• Creating a many to many relationship

• Adding a Table

• Adding a Lookup Field

Return to top

© 1999-2010 Intuit Inc. All rights reserved.