This seems so straightforward, and I keep ending up in the same place.

I have 2 tables, "therapists" and "years". They are joined in the relationship graph by years::therapistid = therapists::zk_TherapistID

The years table has the numeric fields "year" and "expectedrefsind"

I have a database field in therapists::TotalExpectedRefs, it's defined by this calculation . . . 

ExecuteSQL ( "

   SELECT SUM(Y.expectedrefsind) from years Y
   JOIN therapists T
   WHERE Y.therapistid = ?
   AND year > ?

" ; "" ; "" ; "T.zk_TherapistID" ;  2014 )

 

Expecting this to return a Number instead of a ?.  

Any help would be really appreciated.

 

P.S. I just converted these to fmp12 files with Filemaker16. They were previously fmp7 (originally much older than that), and I'm pretty sure there has never been ExecuteSQL used anywhere in the file.

Edited December 2, 20178 yr by jimlongo

SQL has reserved words , and year is one of them. You should escape it, \"year\" .  I wouldn't use ESQL in a calc field actually. You could achieve this using a relationship. What does the data look like in the years table? Could this be a sub summary report?

Hi Jim,

I agree with Barbara's observations that "YEAR" is a reserved SQL word, and that ExecuteSQL isn't generally a good fit for a calculated field.

If you insist however, I think this would work as a calculated field in THERAPISTS -- and of course if you have FMP Advanced, you can test this via the Data Viewer

ExecuteSQL ( "

   SELECT SUM( expectedrefsind ) 
   FROM YEARS 
   WHERE therapistid  = ? 
      AND \"year\" > ?

" ; "" ; "" ; therapists::zk_TherapistID ;  2014 )

 

You don't need to use the JOIN operator because both the fields referenced in the WHERE clause live in the YEARS table.

Hope this helps,

Kevin

Barbara and Kevin are correct. Note that Kevin did NOT use a JOIN construct. If you have the value of

therapists::zk_TherapistID

as the field directly or set as a variable before calling the query, then you don't actually need the JOIN. However if you are passing a direct value in the parameter, it must have context. So presumably you are IN thrapists table (or layout based on a TO with therapists as the base table) to make the query, even though you ask for a sum from another table. Passing the parameter as a variable, however, you can be in any context and the query will work.

HTH,

Beverly

Yep... my response assumes the user is querying from the perspective of a particular therapist record.

Gee thanks everyone.  

I am in therapists context.

I may have tried what Kevin showed above earlier in my attempts, but as you all noted, not escaping \"year\" pretty much doomed all my ideas.

I'll see if a sub summary will also work.

 

Also, 

 "T.zk_TherapistID" 

is not correct. You would supply the value for the parameter as a typical field reference. See Kevin's code.