Single formula field to list all inter-field errors in a row

[Airtable_Clerk]

I have a table whose user entered fields are susceptible to errors arising from invalid conditions between two or more fields. For example:

• Start and end dates are optional but if both start and end date are filled out the end date must be AFTER the start date.
• If the value of the Type field is ‘Underwriting’ then the Underwriter field must be non blank.
• Depending on the value of a certain status field, the total count of linked record in another field should be just 1, or 0.

I want the user to be alerted to a lot of error conditions as they enter data or view records, but I don’t want a whole bunch of formula fields alerting the user and cluttering up the users’ views or interfaces.

To keep things simple for the user I created just one formula field to concatenate together all possible error checks. Each term in the concatenation returns a descriptive message if the error check logic detects a problem, or a zero-length string if the error check logic doesn’t find a problem.

An extract from the final formula field looks like this:

    IF(IS_BEFORE({Expiration Date},{Effective Date}),
        "❌ Expiration date is before effective date.\n",
        ""),

    IF({#Items}=0,
        "❌ This Placement Group contains no copy items.\n",
        ""),

    IF(NOT({Default Copy Type}),
        "❌ Default content type is missing.\n",
        "")

This approach puts all error check logic in one field. As long as you’re willing to copy the field to your favorite text/code editor it’s easy to maintain this field.

Tweaks:

• Each error string returned starts with a red ‘x’ emoji❌ and ends with a line feed. This makes for a nicely formatted list of errors. Call this the ‘QA Errors’ field for now.
• Use the concatenated errors as part of a 2nd formula field that checks for an empty string and returns a green emoji check mark like this :white_check_mark: . Otherwise, the formula returns the contents of the concatenated errors field. Call this the ‘QA Check’ field for now.
• I hide the actual error string I the ‘QA Errors’ field in most views and just show the ‘QA Check Field.

The result is a single field that makes it easy for the user to see if the table row has interfiled errors. I’m happy with the result but I’m betting that other folks might have the same problem and figured out a more simple or foolproof way to handle it.

Is there an easier/simpler way to do this?

[Nathaniel_Grano]

For what it’s worth, I have built very similar types of status/error handling in several projects.

[kuovonne · Accepted solution]

Airtable_Clerk:

To keep things simple for the user I created just one formula field to concatenate together all possible error checks.

I do this too. I tend to name this type of formula field {Data Validation}.

I tend to leave off the third parameter in the IF functions. It is less to type, less to read, and still works.

Sometimes I add record coloring based on this {Data Validation} field. Sometimes I create new view that only shows records there this field is not empty.

[Airtable_Clerk]

Thanks! Data Validation is more understandable than “QA”. QA is IT jargon. I’m going thru and removing the third parameter when I’m editing a table that contains a Data Validation field.