2 Replies Latest reply on Apr 3, 2015 9:47 AM by MatthewMackenzie

    ExecuteSQL NOT IN function issue



      ExecuteSQL NOT IN function issue


      I am having trouble with an ExecuteSQL calcuation match-field which I am trying to use to filter a value list:

      ClinicTimes table has the field ClinicTime. Currently 18 records with pre-set clinic times (as TEXT fields)

      Episodes table has a field ReferralClinicTime (Text)

      I am trying to populate a match field with row separated values using an un-stored calculation so that I only get times that are not already allocated for a particular date (Episodes::ReferralClinicDate) across a set of records.

      When I exclude the NOT function (just use IN), I get the reverse of what I want (ie all the current times allocated for the specified date), but when I keep NOT IN (see below for calculation) I get a blank field (no ? either).

      ExecuteSQL (

      " SELECT ClinicTime
        FROM ClinicTimes
        WHERE ClinicTime NOT IN (
            SELECT ReferralClinicTime
            FROM Episodes
            WHERE ReferralClinicDate = ? 
         ) " 

      ; "" ; "" ; Episodes::ReferralClinicDate )

      Any help on where I am going wrong would be gratefully received.

      Thanks in advance