3 Replies Latest reply on Mar 1, 2017 9:46 AM by philmodjunk

    count of related records by date


      let me start with I am mostly self taught so if I get some terminology wrong sorry. I did do a little searching and didn't find anything similar to what I am trying to do.


      so I have a main page with multiple portals, each portal has a dropdown list of names from a related table. In the table of names I have a count field showing how many times each name has been used in each portal.


      what I want to do is be able to only show counts for a specific date or year. this needs to be easily modifiable by the user.


      I am currently using filemaker pro 13.0v1




        • 1. Re: count of related records by date

          There are multiple approaches that can be used. Each has strengths and weaknesses. Please note that I am just working from your posted description as I have not looked at your file.


          In your very title, you say that you want to count related records. If you set up a relationship that includes a field--this field can be global in the names table, for specifying a date, range of dates or a month, week, year... your relationship can match by name and by date information. You can use two date fields and inequalities to match to records from date1 to date2. Calculation fields in the portal tables can return a date such as the date for the first day of the month or just the year to match records by year. A list of dates separated by returns can match to any record matching any one of those listed dates even. Once you have a relationship matching to the correct sub set of records, Either the Count function (defined in the name table) or a summary field (in the portal table) can return a count of those records.


          Another option is to use a summary field in the portal's table combined with setting up a one row portal using a relationship that matches only by name. The portal filter expression can use the data in some date fields to filter the records to just those of a given name within a given date range.


          Summary reports can simply group records by name and provide a count for each. You first perform a find for the records in your specified date range, or year or month/year before sorting them by name.


          ExecuteSQL can also be used to produce such counts.

          • 2. Re: count of related records by date



            I couldn't get a range of dates to work with the single global field but with 2 fields it works great.

            • 3. Re: count of related records by date

              The only way to get a range of dates fo work from a single field would be to put a list of dates from date 1 to date 2 separated by returns into a text field, or to use a repeating date field with the same dates entered one date to a repetition.