Knowledge Base

Summary and Lookup Fields | BEGINNER

BACKGROUND

Summary and Lookup Fields are created from Table-to-Table Relationships. Table-to-Table Relationships are the backbone of Quick Base; they allow users to relate tables to one another to organize data, reduce data entry, reduce data entry errors, and summarize data from one table to another. Lookup fields exist in the child table; summary fields exist in the parent table. A lookup field in each child record will look-up to the parent record for its value; the summary field for the parent record will summarize the child records.

A. To create Summary and Lookup Fields, click on the table in the Table Bar

B. Click ‘Settings

C. Then click ‘Table-to-table relationships’

D. Select the relationship you want to add Summary and/or Lookup Fields

E. Click ‘Add Summary Field’ (E) or ‘Add Lookup Field’ (F) button at the bottom of the parent and child tables, respectively.

EXAMPLE: PROJECT MANAGEMENT

In a simple project management app in Quick Base, there could be two tables: Projects and Tasks. Each Project has many Tasks, and each Task belongs to a Project. Without a relationship, there is no way to relate tasks to projects; the project’s data must be entered for each task. And there is no way to know anything about a project’s tasks while viewing the project record. Creating a relationship and adding lookup and summary fields can solve these issues.

LOOKUP FIELDS

When a relationship is created, Quick Base defaults to creating the relationship based on the record ID of the parent table – the first lookup field (Related Project in the example above). This information is numerical and is not useful to the user. To make the relationship more user-friendly, a proxy field should be added as a second lookup field, so that when a task is related to a project a user knows which project they are choosing. When adding a task, Project Name is a field that would help the user choose the correct project, so it can be added as a second lookup field and be made the proxy field for Related Project.

  1. Click ‘Add Lookup Fields’ (F above)
  2. Select ‘Project Name’ from the dropdown

  1. Click ‘Create’

Once you return to the relationship page (automatically occurs after step 3 above),

  1. Click ‘Related Project’
  2. Scroll to the ‘Reference field options’ section
  3. In the ‘Proxy field’ dropdown, choose ‘Project Name’

Now, when a task is added, the user can relate the task to a project based on the name of the project rather than its record ID. Continue to add other lookup fields that would be useful to the user while viewing the child record. In the example, lookup fields should be created for Project Due Date, Project Manager, etc. (Repeat steps 1-3). By adding these lookup fields, it eliminates data entry and data entry errors when creating tasks because the data is only entered once at the parent record level. If anything about the Project changes, it will only have to be changed once on the Project form and the change will be reflected in related Tasks. Additionally, it allows a user to see all pertinent information about a Project while viewing a task.

SUMMARY FIELDS

When a relationship is created, Quick Base automatically adds a report link and URL (formula) fields as summary fields on the parent table. The report link field displays the child records of the parent record; the URL (formula) field is a button on the parent record to create a new child record. In the example above, a report for all tasks and a button to create a new, related task can be added to the Project form. Other summary fields can be added to the Project form to summarize related Task records. When viewing a Project record, it is useful to know the number of Tasks, completed tasks, and incomplete tasks. This information can be obtained by creating three summary fields: # of Tasks, # of Completed Tasks, and # of Incomplete Tasks. To create the # of Tasks summary field:

  1. Click ‘Add Summary field’ (E above)
  2. Select the first Radio button

  1. Click ‘Create’

To add # of Completed Tasks:

  1. Follow steps 1 and 2
  2. In the ‘Matching Criteria’ section, select ‘Task Status is equal to the value Complete’

![](In the ‘Matching Criteria’ section, select ‘Task Status is equal to the value Complete’)

  1. Click ‘Create’

To add # of Incomplete Tasks:

  1. Follow steps 1 and 2
  2. In the ‘Matching Criteria’ section, select ‘Task Status is equal to the value Incomplete’

  1. Click ‘Create’

Summary fields can do more than just count the appropriate number of child records. By selecting the second radio button,

You can choose to return the Total (sum of child records), Average, Minimum, Maximum, Standard Deviation, or Combined Text of a specific field on the child records that meet the ‘Matching Criteria’.

If you want to learn more ways to customize Quick Base tools, check out the article, User-Friendly Forms.


Copyright ©2020 - Quandary Consulting Group