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.
Quick Base automatically totals all the orders in your Sales Orders table when you use a summary or chart report. But if you want Quick Base 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, Quick Base 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 Quick Base, 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 Quick Base. 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 details table using a lookup field if you want to use it in details table formulas, include it in details table reports, or export it from the details table.
To create a summary field, you must have an existing relationship, or create a relationship between two tables.
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.)
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.
Click the relationship that you want to edit.
Quick Base 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.
Under the Master Table settings on the left side of the screen, click Add Summary Field.
Select one of the following types of summary fields:
The number of detail_records related to that master_record – Counts the number of detail records related to each master record.
For example, if your master table contains salespeople and your details table contains sales orders, Quick Base 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.
Select the type of summary that you want for a particular field from the list:
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.
After you select the type of summary that you want, then select the field you want Quick Base to summarize.
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.
Type a name for the summary field, then click OK.
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.
© 1999-2018 QuickBase, Inc. All rights reserved. Legal Notices.