3 Replies Latest reply on Jul 9, 2014 2:05 PM by philmodjunk

    Counting number of records containing a specific field within a script

    JustinHesser

      Title

      Counting number of records containing a specific field within a script

      Post

           I am working on a script and within it, I want to be able to count the total number of records within a table "contacts" that have the field "profileid" equal to 37. How would I go about doing this? 

        • 1. Re: Counting number of records containing a specific field within a script
          philmodjunk

               Option 1:

               Perform a find on a layout based on that table with "37" specified as your search criterion. Get ( FoundCount ) then returns the count of matching records.

               Option 2:

               Use a relationship that matches to all records in the table that have 37 in that field. You can set up a relationship like this:

               Table1::const37 = Table2:profileid

               const37 can be a calculation field that always returns 37 or you can make it a number field and set it to 37. Either way, you can define:

               Count ( Table2:::Profileid ) in Table 1 to count all of the records in Table2 with that value in the ProfileID field. (Any record that is never empty from the related table can be specified as the field to be "counted" by this count function.)

               Option 3:

               If you are using FileMaker 12 or newer, ExecuteSQL() can be used to count all records with the specified value:

               ExecuteSQL ( "SELECT Count ( ProfileID ) FROM YourTable WHERE ProfileID = ?" ; "" ; "" ; 37 )

               Note that a field in your table or a variable can be put in place of 37 in this expression in order to count records with different values in ProfileID.

          • 2. Re: Counting number of records containing a specific field within a script
            JustinHesser

                 Thanks Phil!

                 I have used the ExecuteSQL and it works, but I am still having an issue:

                 Basically, once I defined the new field (I called it Count) and placed it in my table, it gave me the correct number of times 37 was used as profileid in contact, but when I added a new record and made its profileid equal to 37, my Count field, did not change (It should have increased by 1). 

                 Please let me know what I am missing.

                  

                  

            • 3. Re: Counting number of records containing a specific field within a script
              philmodjunk

                   Open up the specify calculation dialog for this field. Click Storage Options. Select the "Do not store" storage option.