Sometimes date values in one column, A1, will need to be compared to a date value in another column, say it’s named D1.
If D1 is user entered, the user might not enter a date–don’t know what it is yet, haven’t decided, etc. This should be typical for a product like Coda. It doesn’t try to enforce all the rules that a relational database would require. In other words, Coda needs to handle null, or missing or in Coda-speak, Blank, gracefully.
Alas, Coda does not…
if D1 = “”, then date(2022,12,1) > D1 will always return false because of the type mismatch. You can replace the date function literal with A1–assuming it does always actually contain a valid date value.
So, we have to coerce D1 to be a low (early in history) date.
We can do that with:
if(thisrow.D1.isBlank(), Date(1900, 1, 1), thisrow.D1)
Now, lets say that the actual name of the column is quite long, such is very common in Coda so that columns are meaningful to users. So, we then might be compelled to us a withName block to avoid repeating the long name.
Then we get this:
withName(
thisRow.[Last Completed (Lustrous)],
d,
Date(2022,12,1) > if(d.IsBlank(), date(1900, 1, 1), d)
)
This just returns true or false for the simple comparison to either d or a low date value if d is blank. If, more realistically, we want set a result value based on the comparison, then we have to embed all of that within an if statement.
Then we could get a bigger mess if both sides of the date comparison need to be tested for blankness:
withName(
[Lustrous All Tasks] # a view in my document
.Filter(
[Routine Item] = thisRow
)
.[Planned Start], # a column name in the source table
n,
If(isBlank(n),
"",
If(
withName(
thisRow.[Last Completed (Lustrous)], # assume this is the user input date
d,
Max(n) > if(d.IsBlank(), date(1900, 1, 1), d)
),
Max(n),
""
)
)
)
Being polite, this is infelicitous. This is a bear. In my example, one date comes from a collection in another table. I test it for Blank-ness and I take it’s Max, which will work for a scalar or an array of dates. Then, I compare this to a user entered date, that might be there or not.
Suggestion:
D1.toDate() should work for valid string literal representations of dates, as it does today
AND
when D1 is ""
D1.toDate() should return the lowest valued date permitted by the Coda interpreter. Because the internal date is an integer, often called a Julian date, then we return the date for either 1 or 0. Preferably 0 so that D1.toDate() >= ("").toDate() is always true when D1 represents any date in the permitted range of dates.
Now, we don’t need to test the date twice: once to resolve a blank date and once to do the intended comparison.
I grant this is somewhat of a special case, but because Coda doesn’t have a NULL or MISSING value (see Julia or Python Numpy) for every numerical type in Coda (probably just Int64 or Float64–though internally I bet Coda does everything in floating point) we can make the conversion function provide a way to convert the NULL value to the appropriate zero value.
I acknowledge that NULL often does not mean zero in numerical problems, but Coda is not exactly the language one would choose for numerical methods or machine learning.
(P.S. It is really great that I can create these formulas in a text editor and paste them in a Coda usually does the correct replacement for the reference.)
