Create a Summary Field

Imagine that you have a table containing Sales Orders. To track various aspects of your business, you'd like to total your sales orders in various ways. For example, you'd like to total by salesperson in order to see who's contributing to the bottom line and who's dead weight. You'd also like to total by region to find out if it's worth maintaining a presence in Nevada.

QuickBase automatically totals all the orders in your Sales Orders table when you use a summary or chart report. But if you want QuickBase to total orders based on specific criteria within the table or you want to do any math on these totals (e.g. 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. Once you create a second table and relate it to the Sales Orders table, it has the ability to extract groups of records, like those belonging to a certain salesperson or region, and total or count them for you. The tool you use to make this happen is called a summary field. 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 relationship. Summary fields always live in the Master table and summarize fields from related records in the Details table. (What's the difference?) For example, say that your Sales Orders table is related to another table called Sales Team. In their relationship, the Sales Team table is the master table (one salesperson can have many orders). If you wanted to add a summary field to determine how many sales each salesperson closed or the total revenue each sales rep brought in, QuickBase would only let you add this field to the Sales Team (master) 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.

But Summary fields do more than just total. You can also use a summary field to calculate other amounts. For example, a summary field could average values instead of summing them. Or, you can use a summary field to calculate a minimum or maximum number or even a date. In the case of dates, "minimum" means earliest and "maximum" translates to latest.

Another benefit of summary fields is that the data from these fields is stored within QuickBase, and therefore can be exported and used in formulas. (While summary and table reports calculate totals and averages for you, these values do not actually exist in fields within QuickBase.  If you export the report data, the total/average data is not exported.)  You also can pass the value of summary fields down to the detail table using a lookup field if you want to use it in detail table formulas, include it in detail table reports, or export it from the detail table.

In order 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 application which contains the relationship you want to edit.

    If you've created a relationship between tables in two different applications, open the application that contains the master table. (Don't know what this is? Read about relationships.)

  2. Open table relationships in one of the following ways:

    Note: Unless you've created a relationship across separate applications, you can select either the master or the details table to add a summary field to the relationship.

  3. Click the relationship that you want to edit.

    QuickBase displays the Relationship Properties page, which lists fields that comprise the relationship. The left side of the page shows fields in the Master table and the right side of the page shows fields in the Details table.

  4. Under the Master Table settings on the left side of the screen, click Create a Summary field. The Add Summary Field screen displays.

  5. Design your summary field. QuickBase offers two options for your summary field:

    Once you've selected a type of summary field, click the dropdown to the right of your last selection and choose the field you want QuickBase to summarize.

  6. Set Matching Criteria. You may want to summarize only specific records in your table. If so, use the Matching Criteria section of the Add Summary Field screen to tell QuickBase which records you want the field to summarize. For instance, choose to summarize only tasks that are overdue or only sales figures above a certain amount.

  7. Click OK to save your summary field. Within the dialog box that appears, type in a name for the summary field and click OK.

  8. Click Done to return to the Relationships tab.

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

Related Topics

 

This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.

 

Return to top   

© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.