keywords

Help with ExecuteSQL additional parameter

Discussion created by keywords on Jun 13, 2017
Latest reply on Jun 14, 2017 by 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.

Outcomes