I'd definitely try using \"ClassCode\" in the above query and see what happens.
Hmmm. That didn't work. For the record, this was what I adjusted it to, which had no effect:
select ClassID+' '+\"ClassCode\"+' '+EffectiveMonth+' '+EffectiveYr
where zcClassStatusActive1 = '1'
ORDER BY ClassID ASC
; ""; "" )
Your concatenation syntax is something that I haven't seen before.
What is the calculation result type for the calculation field? could it be number instead of text?
It also seems odd that you are specifying both a sort order (implying that SELECT will return more than one record) and yet did not specify a record separator to separate the records.
The calculation result is text. I'm an SQL newby. I found the concatenation syntax online, but I'm open to suggestion.
It may help you to know that the resulting values need to be used in a value list for a popover button. I'm sure there are better ways of doing this, besides using SQL. Basically I have hundreds of Classes in this database, most of which are INACTIVE. So all I need is a value list of ACTIVE classes. I tried this by filtering the relationship with TOs, using a boolean calc that set to "1" if the class was ACTIVE and set the relationship filter with a self-join from the the boolean field to a field with a constant 1... I've done this a hundred times before, but for some reason it wasn't working for me, so I decided to try SQL.
Just noting that it appears you need a ¶ where you currently have "" so that each such value is separated by a return.
What you describe I call a "hardwired conditional value list". It's a value list based on a category that is predetermined (active only) rather than allowing the user to specify it. Adventures in FileMaking #1 - Conditional Value Lists demonstrates two different ways to set up such a "hardwired" CVL. (as well as 6 other kinds of conditional value lists.)
For your existing query, you might define a calculation field in the table to concatenate your values and then your SQL Query can just SELECT for that calculation field.
Thanks PhilModJunk! Sorry to make you suggest something that should be common sense. I just concatenated everything in the Class table with the good old ampersand and then used that one field in the SQL query, which works perfectly. Thanks for the links!
PS - I used the pilcro for the row separator, but it seemed to default to that when I used "" anyway.