Displaying a count of related records within a parent record

[davidnickerson]

FM 12 Advanced on Mac OS 10.7.5.

This is for a TNC Drivers (Uber, Lyft, etc.) database.  For the purposes of this question I'm dealing with 3 tables:  Shifts, Rides and Cities.  Rides (child table) are displayed in a portal on each Shift (parent) record.  So, for example, the shift record for 9/20/15 shows 25 rides, each with a destination City. I have a separate table for Cities that contains one record per City.

I'm trying to display, on each Shift record, an aggregate list of all the Cities ranked by Count of rides for that City.

For example, of the 25 rides, 12 were to San Jose, 7 to Santa Clara, 4 to Sunnyvale and 1 to Cupertino.  I've created relationships that can show the City Count total for ALL RIDE RECORDS in ALL SHIFTS, but not for just the related ride records for each shift.  I feel like I'm missing something obvious here.

I don't want to do this with lists and summaries, since that takes me out of the single shift record view.  I'm trying to use the Count function to display these values within each shift record.

Thanks for your help!

[comment]

I feel like I'm missing something obvious here.

No, you're not: this is neither obvious nor simple.

Here's one way you could look at it:

 

SummarizePortal.fp7

[Steve Martino]

@‌comment.  Thanks for the sample file.  Quick question if you don't mind:

What is the purpose of the global number field "-" in the Categories and Objects tables?  I cant seem to find it in use anywhere in relationships/script, nor does it appear to hold a value.

Thanks

Steve

[comment]

What is the purpose of the global number field "-" in the Categories and Objects tables? 

They are just separators. Anything under these fields is part of the demonstrated solution.

[Steve Martino]

Thank you

[davidnickerson]

No, you're not: this is neither obvious nor simple.

Here's one way you could look at it:

That's a fairly brilliant solution.  : )  You're right, though; in retrospect it was not obvious, nor would I have been able to arrive at that method on my own.

My particular database uses a multi-criteria relationship between Shifts and Rides (based on start/end times from existing Uber/Lyft online summaries, and not based on ID fields), so I had to do some tweaking in order to recreate what you had done in your sample file.  But it works nicely!

Thanks so much, sir/madam comment!

[comment]

@frankb3 You have - quite correctly - started a new thread for this. Please do not double-post.