Forgive me if this is a foolish question, but if I want to perform this count for data arranged in a summary layout, where do i create the ExecuteSQL expression?
I tried to make it a field in my raw data table (59K records). See the image below, I have a layout containing fields from higher level tables (beginning "::") summarising raw data (PointID, Species). The count value of 463 (for example) includes multiple instances of "PointID", whereas i want only one. I tried to make a field using the expression in a new calculation field referencing "PointID".
> ExecuteSQL ("SELECT COUNT ( DISTINCT PointID ) FROM SpeciesData2013" ; "" ; "" )
I presumed i would then add that field to my summary. However, when i exited the Manager window, FM froze merely trying to evaluate the expression so I imagine i have done something wrong.
Where should that ExecuteSQL expression go in my case?
Same question - where does the recommended ExecuteSQL expression go, or how do I use it?
The ExecuteSQL statement that PhilModJunk provided will give you a count of all unique values for the named field (for the entire database). To use it, you just create a calculated field and paste the ExecuteSQL statement into the calculated field's formula window. (Caution: make sure the field's "calculation result" option is set to number).
Don't know if I'm messing something up, but below is my calculation statement in the field def - result is a number, no repeats.
The name of the field for unique values to be counted is "PROJECT NUMBER" and the table is "ALL PROJECTS".
But the field returns a "?"
Also, can this field be used in a report to return the count for only the records shown in the report? (My real reason for needing the field.)
ExecuteSQL ("SELECT COUNT ( DISTINCT PROJECT NUMBER ) FROM ALL PROJECTS" ; "" ; "" )
Try this and see if it helps:
ExecuteSQL ("SELECT COUNT ( DISTINCT '' + PROJECT NUMBER ) FROM ALL PROJECTS" ; "" ; "" )
Note that '' (two single quotes, NOT a single doublequote) is an empty text constant: adding it to the project number field may force the data type of the project number to be text instead of numeric.