Finding report errors

@Amberlee_Peace,

Welcome to the Airtable Community!

I’m not sure exactly what you’re trying to do with your precise data, but I think I may be able to make a suggestion that will help you, even without knowing your precise details. At least let me tell you how I go about handling error checking and validations.

.

Create a formula field that counts errors

The basic idea is: I create a formula field that I usually call “ERRORS”. The formula consists of a series of linked tests joined by a plus-sign ("+"). Remember that a true result equals 1 while a false result = 0. So the tests have to be written to test for errors rather than non-errors, if you follow me. Here’s an example:

DOB = BLANK()

If the DOB (“Date of Birth”) field is empty, this will return true or 1. I’d only test for this if DOB is a required field. Now I’d add a plus-sign and the next test, say,

DOB = BLANK() +

( DateOfActivity <= DateInvoiced )

That second test assumes that I can’t bill for something before I’ve done it, so I want to make sure that the activity records aren’t dated later than the invoice that includes them. If I was really being scrupulous I could create another test to make sure that the invoiced date isn’t, say, more than the year LATER than the activity date.

And on and on. If you have two ID values that must be compared are supposed to be equal, write a statement that compares them for inequality:

ID1 <> ID2

Anyway, what you get in the end is a column “ERRORS” that has numbers in it. You want to see only zeroes. But if there are errors, the number in this column will tell you how many there are. This is better than just testing for any error because it lets you know how many errors you need to track down and fix.

.

Better: a formula field that actually NAMES errors

You could take it one step further and instead of simply returning zeros and ones, you could actually let yourself know WHAT the errors are. To do this, use IF statements.

IF( DOB = BLANK(), “DOB problem!” ; “”)

And you’ll want to concatenate these error messages, perhaps separated by an asterisk or something. This is a little more complicated formula to write but provides more info.

.

This can get a little complex…

These formulas can get a little complex depending on how many errors you have to check. In my invoicing activity base, for example, an Activity record must

1. be linked to a project and account


2. have an activity date


3. have a description of what was done


4. if the activity is for time spent then the rec needs an agent and a rate entered


5. it’s possible for an activity record to represent a credit, or a fixed charge (say, a request for reimbursement) or a calculated charge (say hours * hourly rate) but it cannot be more than one of those types of rec so I have to check that, if a credit has been entered, the other fields are empty


6. the activity must have a value in the Charge formula field

Expect I’m missing something but you get the idea. To confirm this, I just have an Errors field that performs six different tests. Before I invoice any records, they have to all be certifiably error free.

.

Make sense? Would that work for you?

William

This makes perfect sense and I appreciate it so much! I’ve been using some IF statements giving me Y and N, but I haven’t been stringing them together into more complicated formulas. I think if I take my time to make a good formula string those together, that’ll set me on my way.