I have a multiline form that reads records from a table A which has as one of its columns expiration. The physical database stores ‘0000-00-00’ instead of null when there is no expiration date.
On the form, a user can select a record from A and click Delete. It would then search a related table B using {expiration} as one of many other search criteria in sc_lookup, delete that record in B as well as this main one in A.
My code failed to find records so I had to explicitly detect the case where date is null and use an additional clause as follows:
$expClause = is_null({expiration}) || {expiration} == NULL || {expiration} == '0000-00-00' || {expiration} === null ? " and (expiration = '0000-00-00' or isnull(expiration)) " : " and expiration = '{expiration}' ";
What is very strange is all these failed to work. The only thing that really detects it is
$expClause = {expiration} == 'null'? " and (expiration = '0000-00-00' or isnull(expiration)) " : " and expiration = '{expiration}' ";
Even stranger is this: I check for the value and type of the record’s {expiration} fields in both the onLoadRecord and onValidate and find they are different even though my code doesn’t change them
in onLoadRecord, value is '0000-00-00' and type is string
in onValidate, value is 'null' and type is also string
All PHP docs indicate I SHOULD NOT be testing against a string ‘null’.
Is this a bug or am I getting something wrong?
The code migrated from earlier SC which didn’t have this issue.