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.
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.)
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.
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.
Open up the specify calculation dialog for this field. Click Storage Options. Select the "Do not store" storage option.