I suggest you rename your field; there is already a Case() function, and that may get confusing … for now, let me call it <yourField>.
Do you actually need to capture the resulting values, or do you just want to display them?
For display in a report that is based on that field, use a layout in List view with a sub-summary part sorted by <yourField>; create a summary field that counts the primary key (or your <yourField>); put that field into the sub-summary part, if necessary, remove the body part, and sort by <yourField>.
If you really need the result in a bunch of variables, let us know …
Yes you are right, case was a mismatch......
I need to store the different results in order to display them ih a particular layout of a report.
Otherwise with a normal layout I will not be able.
OK; so I'm not sure that a bunch of $$vars is really what you need, but you can create these variables quite easily, with a script like this one (assuming you have at least FM12):
Set Variable [ $allValues; Value:
ExecuteSQL ( "
SELECT DISTINCT ( testField )
WHERE testField IS NOT NULL
" ; "" ; ""
Set Variable [ $CountAllValues; Value:ValueCount ( $allValues ) ]
Exit Loop If [ Let ( $i = $i + 1 ; $i > $countAllValues ) ]
Set Variable [ $dummy; Value:
Let ( [
curValue = GetValue ( $allValues ; $i ) ;
ExecuteSQL ( "
SELECT COUNT (*) FROM Tasks
WHERE testField = ?
" ; "" ; "" ; curValue
Evaluate ( "Let ($$" & curValue & " = " & res & "; \"\" ) " )
I will try the script as soon as I get to my computer.
If i get stuck can i disturb you again for another hint?