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 . 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?