Very good way to do that is to use the function ExecuteSQL( )
You have a very good example database to learn how to work with that function here
You can do all sorts of summary, count, search and more with ExecuteSQL ( )
You could use a calculation field that counts how many times a string appears in a field.
ExecuteSQL ( "
SELECT COUNT ( \"__ID\" )
WHERE YourField LIKE ?
"; "" ; "" ; "%Your String%" )
PatternCount can do this. But a related table where each such entry is in a different related record would make more sense. Then Johan's method would be one way to get your count. (There are also other methods that you can use. )
Don't create the field in YourTable, otherwise it is calculated as many times as number of records in the table.
That's a good point.
Out of curiosity though: if the field was in the same table with the records you are counting, set to recalculate when needed; but only shown on one particular layout - would it affect the speed of the table in layouts where the calculated field isn't displayed or referred to?
I assumed FMP wouldn't calculate the field unless it needed to, so wouldn't matter?
If you dont create it in that table, how do you go about doing this ?
1 of 1 people found this helpful
The useful thing about the ExecuteSQL function is that it doesn't rely on table relationships, you can state them within the function if you need to, but it's entirely independent to the relationship diagram.
So it could go in any table, maybe one just for SQL calculations?