1 2 3 Previous Next 32 Replies Latest reply on Apr 1, 2014 10:58 AM by evanscl

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

    evanscl

      Title

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

      Post

           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 = ?
      2.      
      3.           SampleType = 'Serum'
      4.      
      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"
      2.      
      3.           If TestResult = "Equivocal", then SerumIgM = "Positive"
      4.      
      5.           If TestResult = 'NEG', then SerumIgM = "Negative"
      6.      
      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.
            

        • 1. Re: CASE function embedded within a SQL query (field type = "calculation")
          philmodjunk

               In this expression, the case function never evaluates unless the query returns a null (empty) result. Is that what you want to have happen here?

               And if you have defined this calculation in a different table than Human_Testing as is implied by the syntax used here, the Case function only references data from the first related record in Human_Testing. Data in any additional related records will be ignored.

          • 2. Re: CASE function embedded within a SQL query (field type = "calculation")
            evanscl
                 I don't necessarily want the query to return a null (empty) result. I was just basing the syntax from an earlier SQL query that you helped me to write; See Post titled Auto-Populate Fields in Parent Record by Evaluating Multiple Records from Related Children Records.
                  
                 The query is written into a field on the Parent page. I want the query to look through all of the child records (only related to the parent record) with the SampleType = 'Serum' and the TestPerformed = 'IgM'.  How do you suggest I write the query to look through all of the child records that are related to the parent record?
                  
            I did indeed define this calculation in a different table than Human_Testing. I defined the calculation in the parent table, Human_PatientInfo. The Human_Testing table is the child table. How do I write the query so that data in all of the child records are referenced, not just the first related record in Human_Testing?
                  
            • 3. Re: CASE function embedded within a SQL query (field type = "calculation")
              philmodjunk

                   My point in my last post was to simply describe what your calculation is designed to do as it did not appear designed to do what you wanted it to do.

                   It's what you want here that is not clear to me:

                   

                        I want the query to look through all of the child records (only related to the parent record) with the SampleType = 'Serum' and the TestPerformed = 'IgM'

                   In theory, (perhaps not in practice), you could have more than one related record where SampleType = 'Serum' and TestPerformed = 'IgM' and the value of TestResult could be different for each record.

                   What single result (Positive, negative) should then be returned?

                   If there is never more than one related record that meets this criteria, then I think that you need something similar to this:

                   let ( Result = ExecuteSQL (
                        "SELECT TestResult FROM \"Human_Testing\"
                        WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND ? = 'IgM')"
                    
                        ; "" ; "" ; Human_PatientInfo_pKey ; RightWords (TestPerformed ; 1 )
                        ) ; //ExecuteSQL
                    
                       Case (
                       Result = "POS" ; "Positive" ; 
                       Result = "Equivocal"; "Positive" ; 
                       Result = "NEG" ; "Negative" ; 
                       "Not Done"
                       )// Case
                    
                   ) // Let
              • 4. Re: CASE function embedded within a SQL query (field type = "calculation")
                evanscl

                     I will have more than one related record where SampleType = 'Serum' and TestPerformed = 'IgM' and the value of TestResult could be different for each record.

                     A single result for SerumIgM should be returned as “Positive”, “Negative”, or “Not Done”.

                     For example I may have the following Child Records:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    

                                         Parent pKey

                               
                                    

                                         Child fKey

                               
                                    

                                         SampleType

                               
                                    

                                         TestPerformed

                               
                                    

                                         TestResult

                               
                                    

                                         1

                               
                                    

                                         1

                               
                                    

                                         Serum

                               
                                    

                                         WNV IgG

                               
                                    

                                         POS

                               
                                    

                                         1

                               
                                    

                                         2

                               
                                    

                                         Serum

                               
                                    

                                         WNV IgM

                               
                                    

                                         POS

                               
                                    

                                         1

                               
                                    

                                         3

                               
                                    

                                         Serum

                               
                                    

                                         EEE IgG

                               
                                    

                                         NEG

                               
                                    

                                         1

                               
                                    

                                         4

                               
                                    

                                         Serum

                               
                                    

                                         EEE IgM

                               
                                    

                                         NEG

                               
                                    

                                         1

                               
                                    

                                         5

                               
                                    

                                         Serum

                               
                                    

                                         SLE IgG

                               
                                    

                                         Pending

                               
                                    

                                         1

                               
                                    

                                         6

                               
                                    

                                         Serum

                               
                                    

                                         SLE IgM

                               
                                    

                                         Pending

                               
                                    

                                         1

                               
                                    

                                         7

                               
                                    

                                         CSF

                               
                                    

                                         PRNT

                               
                                    

                                         Pending

                               
                                    

                                         1

                               
                                    

                                         8

                               
                                    

                                         CSF

                               
                                    

                                         PRNT

                               
                                    

                                         Pending

                               

                     In this example, the single result returned for SerumIgM is “Positive” since Child #2 has a positive IgM test.

                Note:  It doesn’t matter which IgM test is “Positive” or which IgM test is “Negative”. A “Positive” IgM result will always take precedence and bump any “Negative” IgM result.

                • 5. Re: CASE function embedded within a SQL query (field type = "calculation")
                  philmodjunk
                       let ( Result =
                            GetValue (

                                 ExecuteSQL (
                                 "SELECT TestResult FROM \"Human_Testing\"
                                 WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND ? = 'IgM'
                                 ORDER BY TestResult DESC"
                        
                                 ; "" ; ¶ ; Human_PatientInfo_pKey ; RightWords (TestPerformed ; 1 )
                                 ) ; //ExecuteSQL
                        
                           ; 1 ) // GetValue
                        
                           Case (
                           Result = "POS" ; "Positive" ; 
                           Result = "Equivocal"; "Positive" ; 
                           Result = "NEG" ; "Negative" ; 
                           "Not Done"
                           )// Case
                        
                       ) // Let
                        
                       In this version, the rows returned are sorted in descending order by TestResult, so any result of "POS" will sort first and thus be extracted by the GetValue function.
                  • 6. Re: CASE function embedded within a SQL query (field type = "calculation")
                    evanscl

                         I receive an error message in regard to "TestPerformed" in the statement, RightWords (TestPerformed ; 1 ): The specified field can not be found.

                    • 7. Re: CASE function embedded within a SQL query (field type = "calculation")
                      philmodjunk

                           Hmmm, that won't work either will it... I pulled that expression out of the SQL because RightWords is a FileMaker function not a SQL function. But that won't work in the context of a calculation defined in the Parent record. There is a Right function, however so I think that this will work:

                           et ( Result =
                                GetValue (

                                     ExecuteSQL (

                                     "SELECT TestResult FROM \"Human_Testing\"
                                     WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND Right ( TestPerformed , 3 )  = 'IgM'
                                     ORDER BY TestResult DESC"
                            
                                     ; "" ; ¶ ; Human_PatientInfo_pKey
                                     ) ; //ExecuteSQL
                            
                               ; 1 ) // GetValue
                            
                               Case (
                               Result = "POS" ; "Positive" ; 
                               Result = "Equivocal"; "Positive" ; 
                               Result = "NEG" ; "Negative" ; 
                               "Not Done"
                               )// Case
                            
                           ) // Let
                      • 8. Re: CASE function embedded within a SQL query (field type = "calculation")
                        evanscl

                              ; 1 ) // GetValue

                             Error Message received at the semi-colon: A number, text constant, field name, or "(" is expected here.

                        • 9. Re: CASE function embedded within a SQL query (field type = "calculation")
                          philmodjunk

                               And I just spotted another issue here. What I have specified should work if at least one record is POS, but not if the only "positive" record is "Equivocal" as that won't sort to be the first value returned by the query.

                               make the first part:

                               FilterValues ( ExecuteSQL ; "POS" ; "Equivocal" )

                               and the case function becomes:

                               Case (
                                   Result = "POS¶" ; "Positive" ; 
                                   Result = "Equivocal¶"; "Positive" ; 
                                   Result = "NEG¶" ; "Negative" ; 
                                   "Not Done"
                                   )// Case

                          • 10. Re: CASE function embedded within a SQL query (field type = "calculation")
                            evanscl

                                 I'm not following your shorthand. Sorry. :)

                                 In the statement, FilterValues ( ExecuteSQL ; "POS" ; "Equivocal") ...

                                 do I replace "ExecuteSQL" with:

                                           ExecuteSQL (

                                           "SELECT TestResult FROM \"Human_Testing\"
                                           WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND Right ( TestPerformed , 3 )  = 'IgM'
                                           ORDER BY TestResult DESC"
                                  
                                           ; "" ; ¶ ; Human_PatientInfo_pKey
                                           ) ; //ExecuteSQL
                                  
                                 ???
                            • 12. Re: CASE function embedded within a SQL query (field type = "calculation")
                              evanscl

                                   I entered the following query, and I received the error message: There are more ")" han there are "(" or there are more "(" than there are ")". However, the parentheses seem to match up to me. Do you see anything wrong?

                                   FilterValues (ExecuteSQL (
                                    
                                   "SELECT TestResult FROM \"Human_Testing\"
                                   WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND Right (TestPerformed , 3 )  = 'IgM'
                                   ORDER BY TestResult DESC" ; 
                                       "" ; ¶ ; Human_PatientInfo_pKey )
                                       //ExecuteSQL ; "POS" ; "Equivocal" )
                                    
                                   Case (
                                       Human_Testing::TestResult = "POS¶" ; "Positive" ; 
                                       Human_Testing::TestResult = "Equivocal¶"; "Positive" ; 
                                       Human_Testing::TestResult = "NEG¶" ; "Negative" ; 
                                       "Not Done" )// Case
                              • 13. Re: CASE function embedded within a SQL query (field type = "calculation")
                                philmodjunk
                                     FilterValues (ExecuteSQL (
                                      
                                     "SELECT TestResult FROM \"Human_Testing\"
                                     WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND Right (TestPerformed , 3 )  = 'IgM'
                                     ORDER BY TestResult DESC" ; 
                                         "" ; ¶ ; Human_PatientInfo_pKey )
                                    //ExecuteSQL
                                     ; "POS" ; "Equivocal" )
                                      
                                     Case (
                                         Human_Testing::TestResult = "POS¶" ; "Positive" ; 
                                         Human_Testing::TestResult = "Equivocal¶"; "Positive" ; 
                                         Human_Testing::TestResult = "NEG¶" ; "Negative" ; 
                                         "Not Done" )// Case
                                      
                                     // is a "comment operator" that tells FileMaker to ignore all text to the right of it.
                                • 14. Re: CASE function embedded within a SQL query (field type = "calculation")
                                  evanscl

                                       I received another error message at the semicolon before "POS"; "Equivocal"): -- in red font below.  The error message was: A number, text constant, field name or "(" is expected here.

                                       FilterValues (ExecuteSQL (
                                        
                                       "SELECT TestResult FROM \"Human_Testing\"
                                       WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND Right (TestPerformed , 3 )  = 'IgM'
                                       ORDER BY TestResult DESC" ; 
                                           "" ; ¶ ; Human_PatientInfo_pKey ) ; 
                                           //ExecuteSQL
                                  ; "POS" ; "Equivocal" )
                                        
                                       Case (
                                           Human_Testing::TestResult = "POS¶" ; "Positive" ; 
                                           Human_Testing::TestResult = "Equivocal¶"; "Positive" ; 
                                           Human_Testing::TestResult = "NEG¶" ; "Negative" ; 
                                           "Not Done" )// Case
                                  1 2 3 Previous Next