AnsweredAssumed Answered

CASE function embedded within a SQL query (field type = "calculation")

Question asked by evanscl on Mar 25, 2014
Latest reply on Apr 1, 2014 by evanscl


CASE function embedded within a SQL query (field type = "calculation")


     I have a field called SerumIgM that can have three possible values: Positive, Negative, and Not Done.

     I made the SerumIgM field a 'calculation' field. Within this calcualtion field, I created a SQL Query that selects child records with the following criteria:

  1.           Human_Testing_fkey = ?
  3.           SampleType = 'Serum'
  5.           TestPerformed = 'IgM', which is selected by using the RightWords function to select the last word in the field (examples: WNV IgG, WNV IgM, EEE IgG, EEE IgM, etc.)

     Once the above criteria are selected, I want to use the CASE function so that the calculated value in the SerumIgM field depends on the value in the TestResult field:

  1.           If TestResult = "POS", then SerumIgM = "Positive"
  3.           If TestResult = "Equivocal", then SerumIgM = "Positive"
  5.           If TestResult = 'NEG', then SerumIgM = "Negative"
  7.           If TestResult = something else, then SerumIgM = "Not Done" (the default value).

     I wrote the following Query, and it works well the first time I run it. However, if I change a value in the child record, the Query will not update itself. I wrote a script in DateCollected, SampleType, TestPerformed, and TestResult that refreshes the field (or "object") named 'Serum1IgM'.

SerumIgM =
If ( not IsEmpty ( ExecuteSQL ( "SELECT DateCollected FROM \"Human_Testing\"
WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND (RightWords ( TestPerformed ; 1 ) = 'IgM')" ; "" ; "" ; Human_PatientInfo_pKey )) ; 
Case (
Human_Testing::TestResult = "POS" ; "Positive" ; 
Human_Testing::TestResult = "Equivocal"; "Positive" ; 
Human_Testing::TestResult = "NEG" ; "Negative" ; 
"Not Done" ))
     How do I get the Query to update itself if a change has been made in the child record or if additional child records are added? Is the script above written correctly; if not, how should it be written?
     Thank you.