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 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.

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 with the relationship you want to edit.

    If you 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 one of the tables in the relationship, click SETTINGS, then click Table-to-table relationships.

    Note: If you are working with a cross-app relationship, you must select the master table in this step to add a summary field to the relationship. In cross-app relationships, the permissions that a user has been granted in each app still determine what data they can see. If a user does not have the appropriate permissions to see data from the details table, the summary fields in the master table will appear blank to that user.

  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 Add Summary Field.

  5. Select one of the following types of summary fields:

  6. Under Matching Criteria, select a field from the list to set matching criteria.
    You may want to summarize only specific records in your table.

    Use the Matching Criteria 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. Click OK.

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

  9. Click Done to return to the Relationships page.

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:

 

Return to top   

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