Creating summary fields

Quickbase automatically totals data in your table when you use a summary or chart report. But if you want Quickbase to total orders based on specific criteria within a table, or you want to do any math on these totals (such as, you want to calculate what percent each sales representative has brought in over the last 30 days), you'll need the assistance of another table and summary fields. Any time you want to collect data from multiple records and perform calculations on those values, you'll need to use this kind of field.

A summary field is part of a table-to-table relationship. Summary fields are stored in the parent table of a relationship, and summarize fields from related records in the child table. For example, you may have a Sales Orders table that is related to another table called Sales Team listing your sales people. In this relationship, the Sales Team table is the parent table, meaning one salesperson can have many orders. If you wanted to determine how many sales each salesperson closed or the total revenue each sales rep brought in, you add a summary field to the Sales Team table. Within each salesperson's record, the summary field then calculates the total number of orders (from the Sales Orders table) related to the record.

The following types of summary fields are available:

  • The number of child_records related to that parent_record – Counts the number of child records related to each parent record.

    For example, if your parent table contains salespeople and your child table contains sales orders, Quickbase totals the number of orders for each salesperson and displays the total number of orders in each salesperson's record. The label for this option depends on what you call the records in a table. For this example, the label will be The number of Orders related to that Salesperson.

  • A summary of a specific field – Summarizes a specific field of your choice.

    • Total – Adds values from specific fields.

      For example, if you want to total each salesperson's sales in dollars, select this option and the "Sale Amount" field.

    • Average – Averages values from a numeric field.

    • Maximum – Returns the largest (for numeric fields) or latest (for date fields) value.

    • Minimum – Returns the smallest (for numeric fields) or earliest (for date fields) value.

    • Standard Deviation – Returns the standard deviation for a particular field.

    • Combined Text - Combines text values from child records. You can use this option to display lists of values from child records, such as listing all the details of an order item along with the order. This option summarizes unique values from text fields only. Duplicate entries are automatically removed. The aggregated list in this field follows summary field rules such as excluding records that you don’t have access to. The field is stored as multi-select text.

    • Distinct count - Provides a count of all the unique values in a field in the child table. For example, you could show many different products were ordered in a given month. The distinct count summary field supports all field types except the following: File Attachment, Predecessor, iCalendar, vCard, Report Link, List User, and Multi-select Text.

Another benefit of summary fields is that the data from these fields can be exported and used in formulas. You also can pass the value of summary fields down to the child table using a lookup field if you want to use summaries in child table formulas or reports, or export data from the child table.

To create a summary field, you must have an existing relationship, or create a relationship between two tables.

To create a summary field:
  1. Open the app that contains the relationship you want to enhance with a summary field.

    If you created a relationship between tables in two different apps (a cross-app relationship), open the app that contains the child table.

  2. Open the table.

  3. Select the relationship that you want to edit. The relationship properties appear:

  4. Under the Parent Table settings on the left side of the screen, click Add Summary Field. A New Summary Field page appears.

  5. Select the type of summary field you want, then select the field.

  6. Under Matching Criteria, select and add fields to set matching criteria. You may want to summarize only specific records in your table.

    Use the Matching Criteria to select the records that you want the summary field to calculate. For example, you may choose to only summarize the tasks that are overdue or only sales figures above a certain amount.

  7. Select Create in the upper right of the page. A Choose name dialog appears.

  8. Type a name for the summary field, then click OK.

Note: When you add a summary field, it doesn't automatically appear on reports or forms. You need to add it yourself by editing reports or customizing forms.

Related topics: