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:
- Human_Testing_fkey = ?
- SampleType = 'Serum'
- 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:
- If TestResult = "POS", then SerumIgM = "Positive"
- If TestResult = "Equivocal", then SerumIgM = "Positive"
- If TestResult = 'NEG', then SerumIgM = "Negative"
- 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'.