2 Replies Latest reply on Feb 12, 2011 2:04 PM by JoeBrillhart

    Summarizing data from only the related records in a found set

    JoeBrillhart

      Title

      Summarizing data from only the related records in a found set

      Post

      I have two tables that are related.  The first table is Personnel Data.  The second table is Parking Violations.  The Personnel Data table is related to the Parking Violations table by the LicensePlate field.  For each Personnel Data record there could be many parking violations (this is a service business in New York City).  I have a third table with only one record that is called Main, where I display YTD data based on the total number of violations, average cost, etc.  Main is related to the other two tables by a field called BranchNumber. On the layout for Main, I also have a section where the user can enter two dates and search for violations that occur between that date range.  I do that through a button script and it works, except that I want to display the found set from the perspective of the Personnel Data so that in table view, the Personnel Records will show one line, and summarize the charges, number of violations, etc for each person that occurred during the date range of the search.  I have been unsuccessful in doing this.  I have no problem doing it for the entire set of records in the database, but when I perform a search and only want the summary data for a slice in time, I still get the summary of ALL the related records instead of the few records in Parking Violations that are related to each Personnel Data record that occur as a result of the search.

      For example:  Jim Doe has a total of 25 violations year to date.  When I display all the data sorted by number of violations, Jim's 25 violations show up perfectly as do the total charges for his violations.  When I perform a find on the violations based on a specific date range, Jim only has 3 violations.  In the table view layout I am using (based on Personnel Data table), I need to display Jim Doe, with another field showing 3 violations, another field showing the cost summary of his three violations.

      I am probably suffering from a brain freeze and missing a simple elegant solution.  Can anyone please help?

      Thank you,

      Joe

        • 1. Re: Summarizing data from only the related records in a found set
          philmodjunk

          Since the date is in the violations table, it appears there is nothing in your relationship between Personnel and Parking Violations that will filter by specified date range. It is possible to set up such a relationship and separately from the one you have currently defined, but there's a simpler way to do this from a layout based on the Parking Violations table.

          If you haven't already, define summary fields in Parking Violations to compute any totals you need such as Jim Doe's total number of volations. (This can be a count of summary field). These summary fields can be used both to compute a sub total for each person or vehicle as well as a grand total.

          Now create a layout based on Parking Violations. In layout mode, double-click the label for the body layout part and change it into a sub summary part. To get totals for each vehicle, you can specify "when sorted by" your table's license plate field. If you want a total for the person--who may drive more than one vehicle--you'd need to specify a "sorted by" field from personnel that uniquely identifies that person, such as a serial number field defined in that table. (Please don't try to use a name here as you can have two or more people with the same name and you really don't want to combine parking violation data in that way!)

          Then place your summary fields in this layout part. By placing the fields in this sub summary part, you'll get a sub total for that person or vehicle. If you place the same fields in a footer or grand summary part, you'll get a grand total.

          Now perform a find for the parking violation records you want and then sort your records by the same "sorted by" field you specified for you sub summary part.

          • 2. Re: Summarizing data from only the related records in a found set
            JoeBrillhart

            Thank You.  The one area of Filemaker that I never spend much time in is reports so it took me a couple days to get up to speed on your solution.  I couldn't get it to work for the longest time because of some basic misunderstandings I had with the way the various parts of a layout work. I appreciate the time you took to answer me.

            Joe