Jump to content

Displaying a count of related records within a parent record

This topic is 2966 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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



Link to comment
Share on other sites

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!

Link to comment
Share on other sites

  • 3 months later...

This topic is 2966 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.