Discrepancy in implicit conversion to string

[W_Vann_Hall]

I’m not sure this is actually a bug, as it looks more like intended behavior. However, it is inconsistent and undocumented, and it has cost at least one user some money.[1] I’m documenting it here so that, bug or not, there is at least a trail of breadcrumbs for those encountering it in the future.

---

There’s a known but seemingly undocumented Airtable behavior where, if a formula field might sometimes evaluate to a string and sometimes to a number, numeric responses are converted to strings before being displayed. For example

IF(MOD(VALUE(RECORD_ID()),2)=1,'Yes',1)

results in a column of various 'yes's and '1's, left-aligned, showing the ‘1’ has been converted to ‘'1'’.[2]

However

IF(MOD(VALUE(RECORD_ID()),2)=1,'Yes',0)

results in a column of 'yes's and blank fields. Furthermore, if I test the results using T(), all fields in the first column evaluate as text, while only the ‘yes’ fields in column 2 do.

This occurs regardless of whether the ‘0’ value is stated or derived; that is, if the second portion of the IF() statement contained a function that evaluated to zero, the field would remain blank. This is the formula that initially tripped up my client:

IF(
    {Test},
    '😊',
    DATETIME_DIFF(
        TODAY(),
        CREATED_TIME(),
        'days'
        )
    )

One can force the display of a zero value by explicitly casting the response to a string; for instance, either

IF(
    {Test},
    '😊',
    DATETIME_DIFF(
        TODAY(),
        CREATED_TIME(),
        'days'
        )&''
    )

or

IF(
    {Test},
    '😊',
    DATETIME_DIFF(
        TODAY(),
        CREATED_TIME(),
        'days'
        )
    )&''

will return a left-aligned ‘0’ if the record was created today.

I can understand arguments for either behavior. Personally, I would prefer the zero to be returned as a string, requiring the user to declare an explicit test for zero to return a BLANK() value — but that’s just me; who knows how many existing bases depend upon this quirk?

Airtable

✊ Zero Conversion demo - Airtable

Explore the "✊ Zero Conversion demo" base on Airtable.

. __________

1. My client. On the other hand, it’s made one Airtable user — me — an equivalent amount, so if the economic health of Airtable users is a zero-sum game, no harm, no foul.
2. This example works only because of another behavior I consider a bug — namely, that VALUE() will convert a string containing both numerals and characters into a numeric value equivalent to the string with all non-numeric characters stripped out, rather than returning #ERROR — but that’s fodder for a later post.