Yellow is the New Blog

By now, we all know (hopefully) not to use GetNthDocument to loop through document collections, but here's something that often gets overlooked: don't use NULL in formula to determine if a field is blank. NULL doesn't exist.

In LotusScript, the keyword Null basically means "unknown" or "invalid". For example, ArrayGetIndex returns Null if the search value does not exist in the source array. You can manually assign Null to a variable - but only if it's a Variant - and then use IsNull to see if it's still Null, but I'd advise against that, since the only way a value can be Null is if you specifically tell it to be or assign it to an expression that returns Null. IsNull( "" ), for example, returns False.

In formula, although @IsNull does exist, NULL simply has no meaning. It's not a reserved keyword, so much like extended syntax in LotusScript, it doesn't know what it means, so it assumes you're referring to a field. Since there is no field (again, hopefully) named NULL, NULL returns "". So technically you get the same result, just more slowly.

This reminds me of trying to navigate one of those voice-activated menus when I try to call the customer service department of an ISP, utility company, insurance agent, etc. After trying unsuccessfully to determine how to contact an actual human, I'll just say "human". More often than not, the response is: "I'm sorry, I didn't understand you. I'll transfer you to a representative." The result was precisely what I wanted, it just took longer than it would have if I'd known to just push 0 at the start of the call.



In other words, the following formulae all return the same Boolean value:

Subject = NULL
Subject = ""
@IsNull(Subject)
@Length(Subject) = 0

The last is the most efficient, but arguably the least readable. I'd still recommend it, though, for use in column formulae for potentially large views and view selection for any view in a potentially large database. For performance purposes, hide-whens on forms/subforms should be kept to a minimum anyway, but the more you have the more noticeable it becomes if you're asking if some field value = NULL in each: Notes has to check each time whether a field named NULL exists, find out that it doesn't, evaluate NULL to "", and then do a string comparison between "" and the field you've specified. With @Length(FieldName) = 0, it's just checking the length of the field value, then comparing one number to another. Again, slightly less readable, but faster every time.