Is it possible to use a checkbox in a SQL statement and validate it is empty? I am able to get the statement to work when the checkbox is checked, but not when it is empty

CASE WHEN {type} = 'Journal' AND {account} = '1102 Investments : Investments - Fixed Income : Investments - Fixed Income' AND {custbody1} != 'T' THEN {amount} ELSE 0 END

custbody1 = checkbox. This does work as expected when the value is set to

{custbody1} = 'T'

Why not change the conditional,

CASE WHEN {custbody1} = 'T' THEN 0 ELSE

the rest of your normal

Because of how database fields work in Oracle (which underlies all of NetSuite’s data types) checkboxes can have three values: If checked, they will always be

'T'

, however, if the checkbox was added after some records were created, then pre-existing records could have a

NULL

value. Newer records should have

'F'

as their values, if the box is not checked. Usually, I do this with checkboxes to account for the NULLs, forcing them to be `'F'`:

COALESCE({custbody1}, 'F') = 'F'

. Then, both NULL and unchecked values will make the clause true. Alternatively, you could do this (must be in parentheses because of the OR in the middle):

({custbody1} IS NULL OR {custbody1} = 'F')

, which is effectively the same.

Thanks for the solutions! I ended up creating a custom list with values Yes/No which is workable although a bit clunky, your methods would have been more elegant, I'll keep it in mind next time. I feel like I relearn this every time I work with check boxes haha