7 Replies Latest reply on Jun 14, 2017 4:38 PM by keywords

    Help with ExecuteSQL additional parameter

    keywords

      Being still pretty raw using ExecuteSQL I'm struggling. I'm developing a system which generates error records at various points along the way, and these need to be resolved later. I need to extract an ID from the error table, to facilitate accessing the specific error record when the user wishes to resolve it. Error records are related in various ways, so ExecuteSQL seems the best way to isolate a particular errorID when needed.

      The following query:

      ExecuteSQL (

        "SELECT errorID

        FROM relatedErrorTable

        WHERE error_Status = 1  // NOTE: 1 is active; 0 is resolved

        AND thisError = 1"  // NOTE: this field narrows it to a specific type of error

        ; "" ; ""  )

      … successfully produces a list of errorIDs. The problem I'm struggling with is that I now need to extract the one errorID from the list that links to the current parent record. I have tried the following variation to narrow the list to the one that matches, but all I get is the dreaded ?

      Let (

      [

         thisRecord = parentTable::recordID

        ; list = ExecuteSQL (

                                 "SELECT errorID

                                 FROM relatedErrorTable

                                 WHERE error_Status = 1

                                 AND spouseError = 1

                                 AND fk_recordID = thisRecord"

                                 ; "" ; ""  )

      ] ;

      list

      )

      I'm sure it's something simple, but I can't see it.