Defining summary fields

Use summary fields to calculate values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May in a report.

Summary fields are associated with groups of records. The value in a summary field can change depending on where you place the field on a layout, how many records are in the found set, and whether the records are sorted.

Data in a summary field reflects records currently being browsed; either all the records or a group of found records. If you change a value in one of the fields on which the summary is based, or if you change the found set, FileMaker Pro recalculates the result in a summary field.

Only non-blank values are included in summary calculations.

To define summary fields:

  1. Choose File menu > Manage > Database.

  2. Click the Fields tab.

  3. If your database contains more than one table, select the appropriate table from the Table list.

  4. For Field Name, type a name for the field.

    See About naming fields.

  5. For Type, select Summary.

  6. Click Create.

  7. In the Options for Summary Field dialog box, choose a summary calculation type, then choose the field to group data by.

    Choose

    To summarize non-blank values in a field in the found set of records by

    Total of

    Calculating the total of values in the field.

    Average of

    Calculating the average of values in the field.

    Count of

    Counting the number of records that contain a value for the field. For example, if a field contains 100 values (one value for each record), the result of the count is 100.

    Minimum

    Finding the lowest number, or earliest date, time, or timestamp for a field.

    Maximum

    Finding the highest number, or latest date, time, or timestamp for a field.

    Standard Deviation of

    Finding how widely the values in a field differ from each other. This option calculates the standard deviation from the mean of the values in a field.

    Fraction of Total of

    Calculating the ratio of the value in the field to the total of all the values in that field. (For example, find what fraction of total sales can be attributed to each salesperson.)

    List of

    Creating a return-delimited list of values in a field.

    Note  Fields with invalid field types are dimmed in the list based on the summary calculation you selected.

  8. Select a summary option, if applicable, for the summary type.

    For

    Select

    To

    Total of

    Running total

    Show the cumulative total for the current and all previous records. To restart the running total for each sorted group, also select Restart summary for each sorted group and select the field upon which the sort will be restarted from the field list.

    Average of

    Weighted average. In the field list that appears, select the field that contains the weight factor.

    Determine the average in one field based on a value in another field that is used as a weight factor.

    Count of

    Running count

    Show the cumulative count of the current and all previous records. To restart the running count for each sorted group, also select Restart summary for each sorted group and select the field upon which the sort will be restarted from the field list.

    Standard Deviation of

    By population

    Calculate population standard deviation.

    Fraction of Total of

    Subtotaled. In the field list that appears, select the field to group by.

    Calculate a fraction of the total based only on a group of records.

  9. If the field you are summarizing is a repeating field, for Summarize repetitions, choose:

    • All together to calculate a single summary value for all repetitions in the field

    • Individually to calculate a summary value for each repetition

    Note  You must format the summary field as a repeating field to display individual summary values. See Setting up the display of repeating fields.

  10. Click OK, then click OK.

Notes 

  • Use the New Layout/Report assistant to quickly create a layout that summarizes data. See Creating a layout.

  • If you choose Fraction of Total of, you can specify a group field for Subtotaled. When you return to Browse mode, you must sort by the group field to calculate the value correctly.

  • The standard deviation formula is n-1 weighted, following the normal standard deviation.

  • You can't change data in a summary field manually, but you can copy it. You can also perform calculations with summary fields using the GetSummary function.

  • In hosted files, summary fields are processed on the host instead of the client when:

    • the file is hosted and open

    • the summary fields do not summarize fields in tables from an external ODBC data source

    • the summary field is not an unstored calculation requiring client computation (such as using external functions provided by a plug-in)

    • the summary field is not a global field

    • a sort operation is not in progress

    • the found set to summarize is larger than 25

    • the host CPU usage is not higher than 25% when summarization begins

Related topics