Aggregates (Summary Fields) in Filtered Portals

The question on one or more of the FileMaker forums was asked and answered. Q: How do you get the Count() of the related records in a filtered portal? A: (paraphrased) duplicate the filtered portal and make it one row in height. Place the related summary field, “Count of…”, in the single row filtered portal. Voila! your count is now filter-specific. (The instructions for creating the filter for a portal and summary field are below.)

This recent topic led me to consider what other aggregate fields could be used with the filtered portal. And what about that Go To Related Record script step? Does it only show the related FILTERED records or all related records? The demo (bvoth_aggregates_in_portals.fmp12) and article have been created to answer these questions.

I started using filtered portals in cross-tab reports shortly after they were introduced. These are generally ways to show something very specific in each portal with sorts and filters and usually one row only. I had not explored the use of aggregates in these cross-tab reports until now.

Kudos to those before me that may have discovered these answers and tricks, too.

Let’s begin

We are using a simple database with one relationship – the “heavy lifting” will be done with summary fields and filtered portals:

table: salesperson, with primary key
table: sales, with foreign key and summary fields
ONE relationship:

Aggregates, what are they?

There are aggregate fields of type Summary. You can choose one of the values in the following dialog after creating the field and clicking “Options” in the Manage Database, Fields menu:

The summary fields are used in standard sorted report layouts. They can appear in any number of sorted subsummary parts. The values in these fields on the report will change for each subsummary sorted part. The same field in a Leading or Trailing Grand summary will show values for the entire report. There is a standard summary report in this demo.

The filtered portals can  use these related aggregate fields to show specific values (as if they were sorted in the summary report).

Aggregates are also functions that can be used in Calculation type fields. The following table from p. 16 of the FileMaker 12 Functions Reference PDF describes these functions:

You can get the same values with calculations that you achieve with related summary fields and the use of filtered portals. So why not use just the calculations and skip the filtered portals?

• you already know how to create calculations, but you must use other means (ExecuteSQL, GetSummary, etc.) to get all the values you can otherwise with related summary fields in filtered portals.
• the calculated fields must be unstored to retrigger if you change the filter.
• additional fields need to be created for calculations, where the filtered portals can be changed without the need for new fields or new relationships on the graph.
• some of the calculations use the ExecuteSQL() function to work and only with FMP12. You need to understand the new function to make the same query that the relationships and filtered portals use – a little more complex!

There are a few examples of these calculated aggregate fields in the demo database, although our primary focus in the article is the related summary fields and filtered portals.

Create the Summary Fields in the sales Table

We want to get the count of related sales records. Since the salesID is an auto-enter serial, we know it will have a value and make an accurate count of all related records. If we wanted to count the number of records that are non-empty, we could count the amount field or date field. Only non-zero records will be counted.
Amount can be totaled and we may want to know the minimum and maximum sales amounts. Average amount is also found in many reports and uses the non-blank values. Let’s create the minimum and maximum dates, too, if we need to know the range of the filtered portal.
See the Manage Database, fields in the sales table to discover how they were created. Remember that these summary fields can be used on a summary report and in the portals (including filtered portals).

Create the Filtered Portals

Draw a portal on the salesperson layout as “unfiltered” using the one relationship to sales that we created on the graph. This portal  will show all of the sales related to each salesperson, so show the vertical scroll bar to see more past the number of specified portal rows. I’ve chosen to sort the unfiltered portal by date. After you click OK, add the sales fields for date and amount (formatting as needed in the Inspector). Select the “Record Number Symbol” under the Insert menu in layout mode. This inserted text shows us the portal row number without the need for another field definition! The text will appear as “{{RecordNumber}}” in the portal row.

Duplicate the unfiltered portal and after moving around on the layout, double click it to bring up the Portal Setup dialog again to add the filter:

Check the “Filter portal records” and enter this into the Specify Calculation dialog:

sales::year_month = salesperson::filter_date

Now duplicate this new filtered portal and change the number of rows to 1, remove the fields and inserted text. No need to show the scroll bar, of course! Click OK and  resize the portal on the layout as needed. Place the related summary fields into this single-row portal.

Place all the related summary fields on the layout, too, but not in any portal. These will show the summaries for each salesperson’s sales. You will then have different values in these two sets of fields, even though they are identical. Only the filtered portal will limit the values to match the chosen filter!

An interesting aside that I discovered when testing for GTRR (go to related record, the single script step to go to the related sales records on a button). If the button is just placed on the layout, it shows all related sales for a single salesperson record. If you place the same button INSIDE the filtered single-row portal, it will show only those related filtered sales records.

The Demo

The layouts have some notes and a flow to step you through various ideas. Get the demo (fmp12)

• The first layout is the salespersons detail showing their sales data. The filtered portals and the related summary fields as shown inside and outside the filtered portal providing different information. Test the GTRR buttons to see what records are found.
• The next layout is a copy of the first layout, but shows the calculated aggregate fields and calculated fields using ExecuteSQL(). Compare these values with the related summary fields. Look at Manage Database, Fields menu to see how these are defined in the salesperson table. Compare this calculation to the relationship formula and the filtered portal formula (they are both used in the ExecuteSQL calculation!):

  Unstored, = Let ( 
   [ $query = "SELECT SUM ( amount ) FROM sales 
     WHERE sales.year_month = ? AND sales.salespersonID = ? " 
   ; $result = ExecuteSQL( $query ; ""; "" 
     ; salesperson::filter_date 
     ; salesperson::salespersonID ) 
   ] ; $result )

• The third layout is the summary report – mostly to show you that you cannot use these types of layouts to make “cross-tab” style reports with summary fields alone. Play around with the sorts and hold a modifier key down to see the detail rows.
  

  

• The fourth layout is the cross-tab report³ showing the totals and counts per month for each salesperson. This is a standard list view with individual filtered portals in the record/row. Go to Layout mode to click each one to see the Portal Setup dialog. The fields are the same, but the Filters are different.
  
• Finally, there’s a duplicate of the totals cross-tab report, but the total fields in each filtered portal has been changed to the average field to show you a different cross-tab report.

A few other tricks in this demo

1. There is a Calculated type field called “year_month” and auto-enters calculated text based on a standard Date type field. I use this in many solutions to help sort and group records by Month and Year, especially with graphs. It’s a text field, but sorts correctly. This demo uses the same field in a value-list and to filter the sales portal.

       = Year(date) & "_" & Right("00" & Month(date);2)

2. There is a script trigger on the drop-down to refresh the filtered portal. It simply commits the record and refreshes the window. You may or may not need it.
3. I used a custom function¹ by Six Fried Rice, Geoff Coffey, to create the “sales” records. I just wanted random dates and amounts, but within a specified range.
4. There are two layout tricks on the summary report. If you change the sort order, you get a different report with the same layout²! Ray Cologon, Nightwing, suggests adding the record unique id as a subsummary sort and simulate the “body” part that would be shown, but only if sorted by the id of the record.
   This demo also tests for any modifier key (shift, control, etc.) If the key is held down while performing the script, it will show the “body” details! Warning: there is no trap for caps lock, so even it will cause the report to display these rows. You could change the button to pass a script parameter to the script instead of using a modifier key and control whether to show the detail rows or not.
5. The text “{{RecordNumber}}” is used in the demo. Explore the “Insert” menu “Other Symbols” on your layout. You can insert almost all of the “Get()” function values. Try “{{RecordNumber}} of {{FoundCount}}” on a list view footer, for example. These symbols may behave differently inside and outside of filtered portals, just as the aggregates do.

footnote links:
1. Six Fried Rice Creating Random Numbers in FileMaker
2. Ray Cologon Dynamic Layout Technique
3. other Cross-tab reporting by Kevin Frank (a series):
• February 20, 2012, User-Friendly Excel Exports, part 1
through
• March 30, 2012, User-Friendly Excel Exports, part 5

download the demo (fmp12) here