Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Discrepancy in implicit conversion to string

Topic Labels: Formulas
827 0
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

0d41b7239ab01c5f5e8f76a7f7d714f76ba86a36.png

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.
0 Replies 0