AnsweredAssumed Answered

ExecuteSQL NOT IN function issue

Question asked by MatthewMackenzie on Apr 3, 2015
Latest reply on Apr 3, 2015 by MatthewMackenzie


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