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:

relationship graph

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:

Summary Field dialog

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.

Summary Fields on Standard Subsummary Report

Summary Fields on Standard Subsummary Report

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:

Aggregate Functions in FileMaker 12

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

Define Summary Fields

Define Summary Fields

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.

Unfiltered Sales Portal

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:

Portal Setup dialog

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!

Related Summary Fields on Layout

Related Summary Fields on Layout
(not in portal and in filtered portal)

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.
    Sort by Name

    Sort by Year_Month

    Same Layout, Different Sort

  • The fourth layout is the cross-tab report3 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.
    Filtered Portals on Cross-tab layout view
  • 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 function1 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 layout2! 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

15 thoughts on “Aggregates (Summary Fields) in Filtered Portals

  1. Miguel Fernández

    Hello,
    Thank you very much for your tip file.
    I’ve made a slight modification in order to filter by a given year-month value plus “All” so you can have wether all sales or the filtered.
    I’ve changed the field “year_month” to a calculation field “year_month_c” (stored and type text) with this formula:

    Year ( date ) & “_” & Right ( “00” & Month ( date ) ; 2 ) & “¶” & “All”

    Note it’s right your very one plus carriage return plus the text All.

    Now the value list shows all year-month values plus All at the end.

    Then put the following formula in portal filter:

    PatternCount ( “¶” & sales::year_month_c & “¶” ; “¶” & salesperson::filter_date & “¶” )

    That’s all. Now you need just one portal for both filtered and all.

    Kind regards,
    Miguel

    Reply
    1. Beverly Voth

      Thanks, Miguel! Part of the point of the filtered portal is to NOT have additional calculations or relationships, if possible.
      There are many states of the filtered portal.
      1. if the “filter” is empty, you can choose to show no related records (as I had):
      sales::year_month = salesperson::filter_date
      2. if the “filter” is empty, you can choose to show ALL related records (no filtering at all). So, if we change the Filter to this:
      If ( IsEmpty ( salesperson::filter_date ) ; not IsEmpty ( sales::salesID ) ;
      sales::year_month = salesperson::filter_date )

      we get ALL records with no additional changes, no additional fields created, no additional relationships!
      I used the two separate portals in the demo for comparison sake. If I put one portal, I may allow for three different states. There would be a flag field “no_filter” (just a number) and the filter would change to:
      Case ( salesperson::no_filter ; not IsEmpty ( sales::salesID ) // all related
      ; not IsEmpty ( salesperson::filter_date ) ; sales::year_month = salesperson::filter_date // filter by year_month
      ; "" ) // no related

      (and yes, this required another field, but not a calculated field!)

      Reply
  2. Bruce Robertson

    Really excellent example!

    Do you have a version which simplifies setup for the cross tab?

    Right now, every time you want to change the report period, you need to edit the filter on 12 different portals, for the two layouts (totals, average).

    Reply
    1. Beverly Voth

      Your example of this was helpful in making it more dynamic, Bruce. I believe you used merge variables and/or conditional formatting to make the reporting period change. Would you like me to post it with this article? :)

      Reply
  3. malcolm

    Hi Bev,

    Re: An interesting aside that I discovered when testing for GTRR

    All actions take place within a given context. In general the context is the current record but in portals the context is the portal row.

    When you put the GTRR button into the portal row you are taking advantage of the portal’s context ( think of it as an implicit GTRR). To obtain the equivalent in a button from the current record the script steps would be:

    GTRR [child]
    Go to Record [n]
    GTRR[grandchild]

    Reply
    1. Beverly Voth

      Thanks, Malcolm. I had not ever heard anyone able to GTRR for filtered portals unless the button calling GTRR is within the same filter (one or more portal rows). There is no “child” outside of the filtered portal, meaning you can’t script the GTRR and have it pick up the filter, too, without being IN the portal somehow. However I have used the GTRR on multi-row portals for showing details on any given relationship. There you get all children of the portal, but the button appears on every portal row. I guess it would be nice to have a “header” & “footer” to the portal which might contain context for the entire relationship (filtered or not) instead of duplicating the portal and making it a single row for the kinds of aggregates and GTRR I was demonstrating. (sigh… we can wish!)

      Reply
  4. malcolm

    The filter is an extension of the base relationship. It’s easy to imagine performing GTRR then performing constraining finds to obtain the same filtered set. Much more work, to be sure, but I always think of relationships as a hardcoded find.

    I also like the idea of a summary header/footer. Could it have leading/trailing grand summaries too?

    Reply
  5. Annaka

    I love this! Especially the filter “drop down” menu. Unfortunately, I can’t figure out where I’m going wrong. When I set it up like this my portal will only display one record at a time. Any ideas what I might be doing incorrectly?

    Reply
    1. beverlyvoth Post author

      I don’t know what you have set up. The drop-down is set up to show a value list. The file is unlocked, so go ahead and check the value lists and fields. And you also need to look at the portal in layout mode to see what FILTER is applied to the portal(s).

      -Beverly

      Reply
  6. Franky

    Wonderful example. I’m very new to filemaker and helped me a lot !!! Thank you very much !!!!
    I have one question. Is it possible to filter the value list in Filter Date (drop down list) to show only the year_month that is available for each record (salespersonID) ?
    Because if a year_month is empty of records (date, amount) nothing is showed and you must try to choose another year_month until you find a record…….
    Thanks a lot

    Reply
    1. beverlyvoth Post author

      Absolutely! this is just a standard value-list. You can make it a “related” value-list based on the record/person you are currently look at. There are several articles in this blog about value-lists.

      For changes to the example file in this blog: Look at the dialog for the value-list for that field. It has “Include all values” selected. If you want “related”, then select the “Include only related values starting from:”. In the drop-down next to this radio button, select “salesperson”. That is what makes the values tied to ONLY related year_month sales for each salesperson’s record.

      beverly

      Reply
  7. jkluchnik

    This is a great technique. I’m running into a bit of a problem though. For each line, I need to get a percentage of the total. When I use a summary field for fraction of total, I will get either 100% if the line is only summarizing one record, or will get, for example, 33% when the two records being summarized are 5,000 and 10,000 (which means it is looking at the first record and giving the fraction of the total of 15,000. But what I need is the fraction of the total of ALL the records, AND I need the % to be for the records summarized, not for the first record only. Example, if the total of all records was 100,000, then I need 15% (15,000/100,000) and not 5% (5,000/100,000). Anybody have ideas?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s