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 tables reports, or export it from the detail table.
To create a summary field:
In order to create a summary field, you must have an existing relationship, or create a relationship between two tables.
In the table bar on any application page, click one table in the relationship. Within the menu that appears, select Customize --tablename-- table > Relationships.
Locate the relationship you want to change and click its Edit link.
Under the Master Table settings on the left side of the screen, click Create a Summary field. The Add Summary Field screen displays.
Design your summary field. QuickBase offers two options for your summary field:
QuickBase can count the number of detail records related to each master record. In other words, if your master table contains salespeople and your detail table contains sales orders, QuickBase would total the number of orders for each salesperson and display it within each salesperson's record. If this is what you want your summary field to do, select the first radio button. The label of this button differs, depending on what you call records in your tables. Following the example just mentioned, the label would be something like: The number of Orders related to that Salesperson.
QuickBase can summarize a specific field of your choosing. To do so, select the second radio button, A summary of a specific field. QuickBase displays a dropdown list of summary field types from which you can choose:
Total. Use this option to add up values from specific field. For example, say you want to sum each salesperson's sales in dollars. In that case, you'd want to total the "Sale Amount" field.
Average. Select this option to average values from a numeric field.
Maximum. This summary field type returns the largest (for numeric fields) or latest (for date fields) value.
Minimum. This summary field type returns the smallest (for numeric fields) or earliest (for date fields) value.
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.
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.
Save your summary field.
Click OK. Within the dialog box that appears,
type in a name for the summary field and click OK.
If you want your new summary field to appear on any forms or reports, you must add it yourself. (Read how to edit: forms | reports)