Using Aggregate Functions with Related Data

[davidnickerson]

I'm wondering if there is a way to achieve the following using relationships instead of additional aggregate function fields.

I have a TNC Driver's database (Uber, Lyft, etc.) with a parent table (Shifts) and child table (Rides).  One Shift record is the equivalent of one day's driving.  A Ride record is each occurrence of picking up and dropping off a passenger.  Each Ride record has fields for Service ("Uber" or "Lyft"), Fare amount, Date, etc.

I have various calcs in the Shift table [ Sum (Shifts_Rides::Fares), Count (Shifts__Rides::_kp_RideID, etc ] that aggregate Ride data for each shift, such as the Sum of all fares or the Count of all rides.

I also have 2 additional relationships, Shift_Rides_UBER and Shift_Rides_LYFT, that essentially filter the rides records for each shift by whether or not they were Uber passengers or Lyft passengers.

What I'd like to be able to do is somehow use these relationships, or create additional 'filtered' relationships, that would allow me to use the existing aggregate function fields to provide Count and Sum data for Uber rides and Lyft rides, respectively.

The only way I know how to generate the aggregate data I need is to create additional aggregate fields for EVERY relationship.  Eg., Count (Shifts__Rides_UBER::_kp_RideID, Count (Shifts__Rides_LYFT::_kp_RideID, etc.).  Since I'm ultimately looking to create dozens of these filtered aggregates (counts, sums and averages by Uber/Lyft for 30 days, 6 months, by county, city, etc.) it would simplify things immensely to be able to use relationships in lieu of separate aggregate fields for every combination.

Thanks in advance for any suggestions!

[dwdata]

David,

For FMP12 and up, I suggest you get ultra familiar with this function:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

http://www.filemaker.com/help/12/fmp/html/func_ref3.33.6.html

and SQL, of course.  Having a good handle on these will allow you to establish queries based many conditions without having to rely on FMP relationship engine and all the setup it entails.

Hope this get you going in the right direction.

[comment]

The only way I know how to generate the aggregate data I need is to create additional aggregate fields for EVERY relationship.

Instead of piling up multiple calculation fields in the parent table, consider defining single summary fields in the child table. When you place a related summary field on a layout, it displays a value representing the summary of the related set.

Moreover: instead of piling up multiple relationships, consider producing reports directly from the child table, using find and sort.

[davidnickerson]

David,

For FMP12 and up, I suggest you get ultra familiar with this function:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

http://www.filemaker.com/help/12/fmp/html/func_ref3.33.6.html

and SQL, of course.  Having a good handle on these will allow you to establish queries based many conditions without having to rely on FMP relationship engine and all the setup it entails.

Hope this get you going in the right direction.

Thank you for the suggestion.  I'll get to work on my SQL skills...

 

Instead of piling up multiple calculation fields in the parent table, consider defining single summary fields in the child table. When you place a related summary field on a layout, it displays a value representing the summary of the related set.

Moreover: instead of piling up multiple relationships, consider producing reports directly from the child table, using find and sort.

Yes.  I'm certainly familiar with Summary fields and reports...that's exactly what I was trying to avoid.  The beauty of aggregate fields is that they display real time aggregated/summary data without the need for scripts, finds, sorts, etc. 

Edited October 11, 2015 by davidnickerson

[comment]

The beauty of aggregate fields is that they display real time aggregated/summary data without the need for scripts, finds, sorts, etc. 

For one thing, I believe you missed my first point. Summary fields used across relationships do not require "scripts, finds, sorts, etc."

The other thing is that the "beauty" you praise comes at a cost in terms of the amount of resources you need to add to your schema. Even if you replace your many aggregate calculation fields with a few summary fields, you will still need a large amount of relationships.

You may be able to reduce the amount of relationships by placing the summary fields inside filtered portals (yet one more advantage summary fields have over calculation fields). But eventually, if you're looking for speed and flexibility, you will be well advised to look at traditional reporting methods.

 

[Josh Ormond]

There is also the GetSummary() function that can be useful.

http://www.filemaker.com/help/12/fmp/html/func_ref3.33.47.html

[davidnickerson]

You may be able to reduce the amount of relationships by placing the summary fields inside filtered portals (yet one more advantage summary fields have over calculation fields).

Sorry for the delay.  Thanks for this!  Summary fields inside filtered portals does the trick.

 

But eventually, if you're looking for speed and flexibility, you will be well advised to look at traditional reporting methods.

Yes.  I wasn't thorough with my initial description of the database.  I already have traditional sub-summary layouts for reporting purposes.  My request (and your solution) was for displaying aggregate values outside of list or summary layouts.  Thanks again.