2 Replies Latest reply on Jan 18, 2012 8:36 AM by jimscott77

    Summary Layout calculations

    jimscott77

      Title

      Summary Layout calculations

      Post

       I would like to create a layout that shows the number of records in the same table that meet various criteria, for example

      # of records that have not been processed (process date is null)

      # of records that need immediate follow-up (follow-up date <= current date)

      # of records that 'George' has handled (find 'George' in the [Comment log], a text field)

      In MS Access this is a dcount() function that is entered into a datafield.  Is there an equivelent in FileMaker?

        • 1. Re: Summary Layout calculations
          philmodjunk

          There are several approaches possible. Which is best depends on how your data is structured and how you need to organize your records for the report.

          1. Summary fields with sub summary parts can report a count of all records in the group produced by this sorting.You would use Finds and sorting to control what sub totals are calculated.
          2. A relationship can be defined that matches only to specific sets of records--the set you want to count. Then a summary field from the related table occurrence (you can relate a table to itself if needed) will report the count of records for that set.
          3. Using the same relationship, the count function can be set up as Count ( RelatedtableOccurrence::Somefieldthat'sneverempty ) to count the records. With large data sets and list of such records in list view or table view, this option may update more slowly than 2 above.
          4. A summary table can be set up with relational links to subsets of your data for purposes of computing sub totals. This is really just a variation of 2 and 3 above--but can be used to display a 'recap' of your totals in a header, footer or grand summary part to put a list of such counts at the beginning or end of your report.
          5. If all else fails (and you almost never really need such an option given 1 - 4 above...), you can use a script to pull up the set of records capture the count with Get (foundcount) and put that count in a field or variable to display the count in your report.
          • 2. Re: Summary Layout calculations
            jimscott77

             Thanks, #5 was easiest for me to implement given the "random" nature of the various conditions.  It is also most like what I would do in Access/VB with the formulas available there.  As my filemaker skills improve I will explore the other options.