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.
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 below for creating a relationship.
To create
a relationship:
Open the application that contains the tables you want to relate.
(If you would like to create a relationship between two applications,
see the next section.)
Open table relationships in one of the following ways:
In the table bar
on any application page, click the name of one of the tables you
want to relate. Within the menu that appears, select Customize
--tablename-- Table >
Relationships.
In the menu bar on any application page, select Customize
> Tables. Within the list on the left side of the
page, select one of the tables you want to relate, and then click
the Relationships tab.
On Relationships
tab, click the New Relationship button.
Select the other table in the relationship from the dropdown.
To select a table from a different application, choose <Select
another table> from the dropdown. Then select the application
containing the table and click OK. Next, select the
table and click OK. Your selection appears in the
dropdown.
Click Next.
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 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.)
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.
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 display this field as the actual list of detail records,
for example on a form. (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 ties
your relationship together. This field links a record in the
details table with a specific record in the 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 the dropdown. If
an existing field would make an appropriate reference, QuickBase
also offers it in the dropdown as well.
Lookup
fields enhance the relationship by providing additional
information about records in the master table. For example,
you may want to include the Project Description
field along with the Project Name field in
task records related to the project. 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 Create.
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.
To see the details of the relationship you just created, click its name.
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 report
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.
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.
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 to which the detail is related. You
can have this reference field appear as a dropdown list, or you can
have it show 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
dialog box appears.
|
QuickBase reference fields automatically show a dropdown list. Once
items in the master table number more than 1000, QuickBase automatically
switches to the record picker. If you'd prefer the Record Picker to
always display, edit the reference field's properties. In the Reference
field options section, turn on the Use a record picker
instead of a dropdown menu checkbox.
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. Then click the Edit the Relationship
link.
To give permission to
create a cross-application relationship:
Open the application that contains the master
table that you want to relate. (Don't know what a master table is?
Read about relationships.)
In the menu bar, click Customize > Application.
On the Properties tab,
click Cross-Application Relationships to
expand the section.
Click the Add Application button.
From the list of applications that appears, select the application
with which you want to create a relationship and click OK.
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.
If you want Sales users to be able to add a new HR master record from
a Sales detail record, you also must give the Sales users create permissions
in the HR master table; the role you specify in this step does not
control the Sales users ability to create new master records from
within detail records.
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.
Click Save.
Open the other application—the one you didn't set permissions
on. This application should contain the details
table.
Follow the steps in the previous section to create
the relationship.
Related Topics
