Creating table-to-table relationships

You can create a relationship between two tables within an application, or between two tables in separate applications.

To create a relationship between tables in an app:
  1. Open the app.

  2. Open one of the tables you want to relate.

  3. Select Settings, then select Table-to-table relationships.

  4. Select the + New Relationship button. The Create a table-to-table relationship dialog appears:


  5. The dialog steps you through all the elements of creating a relationship, and provides guidance for each step.

    • Tables: Select the tables to connect, and choose which table is the parent and which table is the child.

    • How to relate child records to a parent: This step involves choosing a reference field for the relationship. This step may not appear depending on how the key field in the parent table is set.

      Key field: The key field ensures that records have a unique identifier. Quick Base creates Record ID# as the key field, but you can specify a different key field in table settings.

      Reference field: When adding a child record, the reference field provides a list of parent records. For most relationships, using the default reference field is sufficient. You might select a different reference field if you have data that matches between parent and child, such as project number or customer ID, and want the relationship to automatically link existing records. If you use the default reference field, you can add a lookup field as a reference proxy, which acts as the reference field, but contains user-friendly information.

    • Add lookup fields: Choose fields from the parent table to appear as lookup fields in child records. When you create a new child record and choose a parent, lookup fields populate automatically with info from the parent record. If you don’t need lookup fields right now, choose Create Relationship to finish. You can add more fields later by editing this table-to-table relationship.

      Lookup field as reference proxy: Quick Base automatically sets your first lookup field as a reference proxy, which acts as the reference field, but contains user-friendly information that lets you relate child records to a parent. You can change the reference proxy at any time using table settings.

  6. After you create the relationship, it appears on the Table-to-table relationships page. You can edit the relationship from here if necessary, to view settings, change properties for the fields, or add fields.

Creating a cross-app relationship

You can create a child-to-parent relationship between tables in two different apps.

Note: This feature is not available to accounts on the Quick Base Essential plan.

The process for creating a cross-app relationship is similar to creating a relationship within one app, however, there are some key differences to consider.

By choosing a table in another app, you are limited to creating a child-to-parent relationship. The app containing the relationship details is always the child table. The app containing the parent table does not list the relationship in settings.

With roles and permissions, it is important to ensure you are securing data correctly. The parent table must be set to allow access to the child table, and include a role for the all users in the child table. If a user has access to both apps, the role in the parent table will take precedence over the user's role in the child table.

A cross-app relationship is best for:

  • Connecting simple, small to medium-sized apps.

  • Limited edits in the child table.

  • Immediate data availability in the child for viewing on forms and reports.

Additional differences are explained in the steps below.

To create a cross-app relationship:
  1. Open the app.

  2. Open the table you want to relate.

  3. Select Settings, then select Table-to-table relationships.

  4. Select the + New Relationship button. The Create a table-to-table relationship dialog appears.

  5. When choosing a table, pick Select a table in another app at the bottom of the drop-down.

    You may need to ensure the other app is set to allow access.

    • If you are an administrator of the other app, you'll see the message "Your app needs to allow cross-application relationships" along with steps on opening the App Settings for the other app.

      You add access and assign a role in the Cross-app Relationships section, then select Save to save the App Settings.



      Important:

      • Select the appropriate role to limit access to records in the parent app.

      • Your choices here depend upon what roles you've set up for the parent app.

      • The role you choose affects what information is available to the child app.

      • Create permissions in the parent table must be granted to the individual users, not to a group.

      For example, a sales department has a Marketing Leads app, and would like to create a cross-app relationship with an Employee Information app to populate fields. You need to restrict access to certain employee information. Create a role in the Employee Information app that only allows access to fields needed for the Marketing Leads app, such as name and telephone extension. Then, grant the Marketing Leads app the ability to create a relationship with the HR application, specifying that it can only access the Employee Information app using the role you created.

      Once you add access for the app containing the child table, you can resume with creating the relationship.

    • If you are not an admin of the other app, you'll see the message "You need permission to create a relationship." Select the Request permission button to send an email to the app owner. Once the app admin allows access for your app, you can resume with creating the relationship.

  6. Select the table to connect to, which will become the parent table.

    Note: Unlike with a regular table-to-table relationship inside one app, child-creation (URL formula) buttons and reports lists are not automatically added to the parent table. These fields can be added to the parent table manually, by editing the relationship (in the child table) and adding fields.

  7. After you create the relationship, it appears on the Table-to-table relationships page for the child table only. You can edit the relationship from here if necessary, to view settings, change properties for the fields, or add fields.
Related Topics:

 

Go back      |       

© 1999-2019  QuickBase, Inc.  All rights reserved.  Legal Notices.