MENU
    Catching errors in Airtable using logical functions
    • 04 Dec 2023
    • 2 Minutes to read
    • Dark
    • PDF

    Catching errors in Airtable using logical functions

    • Dark
    • PDF

    Article summary

    In this article, we’ll cover different approaches to identifying and outputting errors in a formula field.

    Introduction 

    Plan availabilityAll plan types 
    Platform(s)Web/Browser, Mac app, and Windows app
    Related reading

    ERROR()

    ERROR() is a logical function that returns the error value. This means that you can output an error message - #ERROR!- by calling this function.

    To show how this looks in an Airtable base, here is a formula field that only has
    ERROR()written in the formula field.

    1500000119122ERRORS001.jpg

    ISERROR()

    ISERROR() returns "true" (represented by a 1) if the expression it references causes an error, or "false" (0) if there is no error. As an example, because you cannot divide a number by zero, this formula example would result as true:
    ISERROR({Price} / 0)

    360098990034ERRORS002.jpg

    This function can be helpful in identifying any type of error in a formula. In the screenshot below you'll see a couple of different errors showing in {Error Types} from the formula used.

    In the formula field on the far right, using ISERROR() identifies that there is any type of error in the formula rather than outputting the particular one. You'll learn why this is useful in the next section!

    360098990014ERRORS003.jpg

    Errors and logical functions

    This function can become even more useful when used in conjunction with an IF statement to output a warning or status whenever a formula results in an error. This could help you identify missing or incorrect values in another field, or display a generic message whenever there is any error found.

    For example, let's say you have a formula to calculate gross profit margin:

    {Net Sales} -  {Cost of Goods Sold} ) /  {Net Sales}
    Plain text

    360098989994ERRORS004.jpg

    In the screenshot above, an Infinity error is caused by a missing value in the net sales field. If you wanted to only run the gross profit margin formula whenever there was a value in {Net Sales}, you could write an IF statement like this:

    IF(
      {Net Sales},
        ({Net Sales} - {Cost of Goods Sold}) / {Net Sales},
        BLANK()
    )
    Plain text

    However, this wouldn't account for other potential errors that could occur within the table (a missing value in {Cost of Goods Sold}, or a zero value in net sales, etc). Rather than the formula above - or a more complex IF statement to account for each potential error scenario - you could instead use this:

    IF(
       ISERROR(
          {Gross Profit Margin}
       ),
       "🚨 Alert"
    )
    Plain text

    360098989974ERRORS005.jpg

    Having a single text value (🚨 Alert) in this field could trigger a notification to a project manager so they can review, or filter in records into a view where someone can address any missing/incorrect values as needed).

    Circling back to the ERROR() function, you could also use that in place of a customized message!

    IF(
       ISERROR(
          {Gross Profit Margin}
       ),
       ERROR()
    )
    Plain text

    360101209373ERRORS006.jpg


    Was this article helpful?