3 Replies Latest reply on Dec 3, 2013 10:38 PM by philmodjunk

    Counting Matching Records



      Counting Matching Records


           I want to count and present (in a printable format) the number of records that match a found set. The values I am searching for are fixed. It should be auto-updating.


           1 record has "foo" in field x

           4 records have "bar" in field x.


           Building upon that I want to be able to display a count of records that match multiple fields.

           1 record has "foo" in field x, "bar" in field y.

           3 records have "bar" in field x and "foo" in field y


           I think I want to run a Find then a Get(FoundCount) but I cannot figure out exactly how to do that.

      I was wondering if a report might be a better way to go about this.

        • 1. Re: Counting Matching Records

               There are several different approaches that can be used and neither use get ( foundcount ) wink

               Option 1:

               You can set up a relationship that matches records by the value in fields of your found set. Either Count ( relatedTable::field ) or a "count of" summary field defined in the related table can return the count of matching records. So if a record on your layout has "foo" in field 1 and you have this relationship:

               Table1::Field1 = Table2::field1

               Then the count function and summary field will return the number of records in Table2 where Field1 = "foo".

               And Table2 can be the same table as Table1...

               Option 2:

               You can sort your records by Field1 in your layout. Add a sub summary layout part "when sorted by field1" to your layout and place a count of  summary field in the sub summary layout part to show the count of records with that value. You can produce a report with one row for each unique value in Field 1 if you remove the Body layout part and just keep the sub summary part.

               Option 3:

               You can use the ExecuteSQL function in FileMaker 12 to produce a multi-line field where each line shows the count of records in each group.

               And all of these methods can be adapted to work with matching on multiple fields.

          • 2. Re: Counting Matching Records
                 First: thanks so much for the quick response. i've just now been able to sit down and look at this.
                 I went with sub summaries. How would you go about expanding this to two fields?
                 ie: of the 10 fields out of 100 that have "foo" in Field1 how many have "bar" in Field2
            • 3. Re: Counting Matching Records

                   sort your records by Field 1 and also by field 2. set up a sub summary "when sorted by" Field 1 and also a sub summary "when sorted by Field2". The same summary field can be placed in both sub summary parts to show two different sub totals.