Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Finding report errors

Solved
Jump to Solution
1177 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Amberlee_Peace
4 - Data Explorer
4 - Data Explorer

I am new to relational databases in general

My goal is comparing reports of what are supposed to be the same items, but will occasionally have small (and sometimes large) errors. Because the reports come from 3 different locations, each one is already slightly different, but they each have a Serial Number field and an Object ID field. Those are what are most important to me

What I’m trying to be able to do is have a table where I can plug in my Serial Numbers and see what each report says their Objected ID is, so if they are mismatched, I can see that easily. I know this seems straight forward, but I’ve been trying to use linked records and Lookup…and it keeps messing up.

1 Solution

Accepted Solutions
WilliamPorter
9 - Sun
9 - Sun

@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

See Solution in Thread

3 Replies 3
WilliamPorter
9 - Sun
9 - Sun

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

Great!

IF() statements in Airtable are a bit of a headache for me, too. I’d suggest that you consider my suggestion that you simply ADD up the number of tests that return false. The example I gave above was

DOB = BLANK() +
( DateOfActivity <= DateInvoiced )

No IFs (or ANDs or BUTs, either). Just define the error (e.g. it’s an error if the DOB field is blank), type “+” and a new line, define another error, rinse, repeat.

This is not really all that complicated, because there’s nothing “nested” here. It’s just one simple test statement after another, each test on 1 line.

Good luck!

William