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).
" SELECT ClinicTime
WHERE ClinicTime NOT IN (
WHERE ReferralClinicDate = ?
; "" ; "" ; Episodes::ReferralClinicDate )
Any help on where I am going wrong would be gratefully received.
Thanks in advance