The right level of data validation?

[kennedy]

From a usability standpoint, data validation can be extremely helpful or extremely annoying, or anywhere in between. This thread is intended to collect experience and opinions on how much data validation is too much or too little in different situations...

1) GENERAL QUESTIONS

a) Strictly enforced loose validation vs. tight validation but allow override?

In many validation situations, you can choose to provide very restrictive validation to help catch mistakes 95% of the time... but would need to allow override because you'll get some false-negatives. However, lazy users will often just override and put in bad data. Alternatively, you can disallow user override, but then the validation must not get false-negatives, and that may result in a large number of mistakes going without warning. It would be nice if FMP allowed both at once... but since it doesn't, which do you tend to prefer?

: Checking for uniqueness or duplication?

Its sometimes easier to hit "New" and just put in a new customer or whatever than to hit "Find" and first find if that customer already exists and then make sure no info needs update. But in many cases, uniqueness checks can be slow, complex, or imprecise (related to question 1a). For example, you could check for matching first and last name... but that may or may not be an error... but if 95% of the time it is, it may be worth detecting and allowing override.

c) Checking for common typos; one issue though is that the standard FMP validation only gives you one error message for all the validations

d) Validation vs. Cleanup Scripts

For example, when I imported my data, I converted all addresses to use the standard post office abbreviations for St, Rd, Dr, Blvd, Ct, etc., all with no periods, and N, S, E, and W instead of the full word, and so on. I can now easily run marketing analyses on the addresses, as they are highly consistent. But how do I maintain that? Insist the user puts it in right? Or every night run a script to try to clean it up?

2) DATE VALIDATIONS

a) Is it good to restrict dates to a range? That would seem to help prevent century errors... though it could be argued that telling me I can't be born in 2066 is just stupid... skip the error message and just change it to the obvious 1966.

: Is it good to restrict them to a range in the past/future? Frequently dates are either historic or future plans... never both. Thus, you could restrict to >= or <= Status(CurrentDate). Any issues with doing that?

c) Require 4-digit year? In most of my fields, it is clear what year is meant. But its not clear to me how FMP decides whether its 19xx, 20xx, or 00xx. I've gotten all three at times... and that led me to requiring 4-digit year. However, I'd rather specify what to use and let the user input 2-digit... if easily done.

3) PHONE NUMBERS

a) Strictly numeric -- and then use a cover field to present the phone number with standard hyphens

: Strictly require exactly 10 digits (prevents entry of extensions or requires an extra extension field for that purpose)

c) Separate area code field (this seems to cause as many errors as it helps... and for rapid data entry, its easier to not have to tab between the area code and the rest.

d) Given 98% of my phone numbers are from one of 5 area codes, I could validate its one of those... but then it couldn't be strict... and thus the 10-digit check couldn't be strict.

e) Given 80% of my phone numbers are from one of about 20 exchanges, I could validate its one of those... same issue on strictness as d)... but added issue of annoyance in 20% of cases that don't fit that. "This is not a typical exchange, are you sure its right?"

4) ZIPCODES

a) Strictly numeric (given I only have US zipcodes)

: Strictly 5-digits (given I only have US zipcodes)

c) Since 95% of my zipcodes are one of a dozen, I could validate on those... but then I can't be strict, and thus can't strictly enforce 5 numeric digits.

d) see 5a below

5) CITIES

a) I could validate this based on zipcode, which provides a double-check of both zipcode and city

: I could auto-enter the city based on zipcode... but then you lose the double-check

c) I could validate its one of about 10 that cover 95% of the records.

d) I could validate its one of about 100 that cover 99%.

e) I could validate its NOT one of a number of common misspellings

6) ADDRESSES

a) In the import process, I normalized all these to where I always use the std post office abbreviations for Dr, St, Rd, etc., consistently without period... and always use N, S, E, and W rather than the full word, and so on. Should I validate for that? Or allow it and just plan to run automation to clean it up?

: Detect apartment numbers in address which should be in the separate Apt field?

7) NAMES

a) Have a non-strict validation on last names in a family matching? Or just look for near-misses (spelling errors)? (The problem with the latter is that FMP doesn't make such a calculation easy, given its lack of regexps.)

What validations do you all find most effective?:?

[LaRetta]

Brian, I love this post. I think we all deal with these issues daily. I have no answers -- I'm just as indecisive as you. My thought, although I'm not sure how to implement it, is that -- on phone numbers, zips, social security #'s etc, allow the user to enter anything they want -- they all enter them differently. But, FM *knows* how they should be. I think our bright db-Gods here can help us come up with forumulas that can be used to convert these fields, since many are very result-specific. Maybe user enters in global (oh, unless networked, maybe not), or regular field and then it can be converted to its proper structure by a calc.

Excellent thoughts ... thanks for asking these questions!

[kennedy]

C'mon... I know you smart guys have some opinions on what's too much validation (you've mentioned some in other threads)... and I'm sure on what's too little... and you have way more practical experience with these things... please share...

(I guess my question was way too long... I was trying to feed the conversation, but I think I smothered it...)

[jasonwood]

Here's what I do for cities to avoid spelling errors...

Rather than using validation, I set up the field as a popup list (the value list comes from the same field, city). This way it shows all of the cities which have been entered in the past. If your database has been growing for a while, you should have most of the cities that you will be dealing with, so users will normally only have to type a few letters to select the one they want. Otherwise they can simply click again and spell it out in full.

For zip codes, you don't want to limit to 5 characters unless you're certain that you'll never have a Canadian customer! I can't stand it when I end up in a form on the internet where I can only enter 5 characters for my postal code! Often they give you an option to select Canada as a country but they still won't let you fill in all 6 characters of your postal code!

I VERY MUCH like the idea of strictly validating telephone fields, because I like to use this field for searches, and if everyone enters them differently (which they do), the search won't be reliable. I like your idea about requiring 10 digits and then using a field mask to put in the dashes for display purposes.

Generally, I like to use less validation when it isn't really necessary, cause we all know how annoying those messages are when we REALLY don't want to enter things properly! Especially the ones that say "field may not be empty"... gotta love it when you create a new record by accident and now you have to enter characters in these fields so you can exit the record!.... consequently, I probably would not validate an address based on these abbreviations... the automated cleanup is probably a better option.

--

Jason Wood

HeyWoody.com

[kennedy]

Rather than using validation, I set up the field as a popup list (the value list comes from the same field, city). This way it shows all of the cities which have been entered in the past. If your database has been growing for a while, you should have most of the cities that you will be dealing with, so users will normally only have to type a few letters to select the one they want.

How do you make FMP do completion like that? I know you can set it up as a value list and they can type the first character... but it only operates on the first... it won't let you type in 2 or 3 and narrow the list... at least AFAIK... is there a way?

[jasonwood]

hmm... maybe it's a Mac thing?

You DO have to type quickly. If you stop for a couple seconds it goes back to the beginning again.

It's not like autofill in Internet Explorer and some other programs (if that's what you're thinking)... the whole list is still there, it's just scrolling to the one that best matches your typing.

--

Jason Wood

HeyWoody.com

[kennedy]

You DO have to type quickly. If you stop for a couple seconds it goes back to the beginning again.

Ahhh... cool... I thought it was just a bit schitzo... or that I was mistyping... but whenever I tried to figure it out, I slowed down to be sure what I was doing...

Now that I know how it works, I can exploit it... but I have to wonder whether my typical users will be able to... whether its worth re-wording my lists to make the first few characters unique (rather than just the first).

Anyway, thanks!

[mdpres]

Kennedy,

Thought I would put my 2 cents in here. I was asked to put popups in customer fields so a person could do what you are describing. The user with little or no keyboard skills like it, the people with good keyboard skills hated it so I duplicated the layout wrote a script to disable the popups if thats what a user wanted or default to the popup layout.

Dean