Newsletter #15
How To Keep Your Database Clean

It can be darn embarrassing to send out hundreds of copies of a letter only to find you've misspelled something in it. And then you find you've addressed some of the recipients with salutations like "Dear Mrs. Bob." At a time like that, "Oops" doesn't cut it. You look unprofessional and that's just not good for business. All this can be avoided by adopting some or all of the following guidelines.

One of the biggest things you can do to keep your database clean is to control how the data is entered using such devices as checkboxes, drop-down lists and radio buttons. Items don't need to be typed so there are no spelling errors. And since the choices are preset, you avoid odd abbreviations and variations that need to be corrected.

You should have separate fields for first and last names so it's easier to create salutations for letters and for the purpose of sorting records by last name. Then provide fields for middle initial, nickname and appendix (things like III or Esq.)

A more important field (sometimes called salutation) will include one of either dignitary (Lord, Mayor), professional title (Professor, Doctor) or social title (Mr., Miss, Mrs., Ms.). This field is the perfect place for either a drop-down menu or a radio button set. Having something in that field should be required.

Figure 1 shows a very simple salutation form I built for myself. I already have the first, last and nicknames entered. Then I use the radio buttons in the left and middle to determine what the salutation will look like in a letter. The finished version appears in the lower right. The empty box in the lower left is in case I need something that's not provided with the radio buttons.

Figure 1 - One method of handling salutations.

After dealing with names, you might consider if and how you want addresses abbreviated. Will it be Street or St.? Road or Rd.? Most databases allow you to put restrictions on a field that will nag users into following the agreed upon format. If you need consistency, find out how to implement those field controls.

A feature in many databases is known as a Lookup. My favorite way to use it is by typing the Zip Code and having the City and State looked up automatically. It's a big time-saver and you don't run into the spelling errors that can crop up otherwise. If that's available to you, try to figure out how to get it working. You'll need a table of zip codes linked to cities and states.

Something that can make data entry difficult and error prone is when the workers are reading from forms that don't match the order of data entry in the computer. If you design the web and paper forms to closely match your database, you'll make their work so much easier. When that's not possible, create a different layout in your database that mimics the order of the external form you're using. One example is where you receive emails containing web data. Email data usually marches down the page in a single column. So create an onscreen form with that same single column.

I work with a number of companies who get batches of data sent to them on a regular basis that do not break up nicely into the right fields. The situation I see most often is where first and last names arrive in one field. Most databases provide tools to break that data into their separate fields. The method I use is to import the full name into a special field. Then a calculation determines how many spaces are in that field. If there is only one space, I use the "replace" tool to split them into the first and last name fields. Then I look at the remaining records to decide how they might be handled by other methods until you finally deal with the leftovers manually.

Another form of dirt is where the data arrives all in caps. There are tools in most databases that can change the text to Proper case. You'll still have to manually correct names like McArthur, which will likely get converted to Mcarthur.

I've observed workers in some offices hesitate to ask people how to spell their names. In order to make sure workers ask for all data, some companies write a script for their people to read when adding new members or prospects. Look at this example. "Are you already a member? Let me see if I can find you in our database. How do you spell your name? Might anyone else from your company already be in our files?" Using the information they gather, they should then perform a Find or a Query. You can even have a screen designed so that if the person isn't in your files, it will use the search data to begin the new record. That prevents your people having to ask how to spell the caller's name a second time and makes the company look more professional.

I talked earlier about web forms. Nothing beats proper data entry than simply importing it from other sources. I've worked at many companies where they receive web entries via email only to end up retyping everything. Worst of all, it often happens during convention time when harried employees are pushed into making more mistakes than usual. If you can get the data from your web provider in a standardized tab-separated text format that you can then import into your database, you'll not only avoid errors, you'll also be able to use your people for more important things. But you may still need to check that the data wasn't submitted with the caps lock on.

Employees at one association I work with added a new member or prospect record without checking to see if the caller was already in the file. As you can guess, there were a lot of duplicates. We instituted a method of controlled data entry. (Again, it's important to get your people to ask callers how to spell their names. It could be "Thom" instead of "Tom" or "Jon" instead of "John.") After initial data entry, we would present the user with a list of possible name matches. The next screen would check for possible company matches.

As good as that sounds, we found workers were clicking past the screens without checking to see if there was a match, because the process slowed them down. We had to resort to tracking who did the data entry in order to get them to look. Apparently there is only so much you can do to help with the data entry process.

While you're using controlled data entry, you can prevent users from leaving the new member screen without providing certain data. We're all familiar with the Name, Address and Email forms you fill out on the web where some fields have asterisks next to them indicating they are required. You can do something similar by highlighting missing data or using scripted buttons that display dialogs guiding the user back to the necessary fields.

Regardless of all this, you will still end up with duplicate records. I use multiple comparisons in order to root out all possible duplicates that may have crept in. First I look for duplicate full names. Then I use a calculation field that combines last name and zip code. The reason full names aren't good enough is because of nicknames and the previously mentioned alternate spellings of first names. Then I use a calculation field that combines company name and last name. But that won't catch everything because of abbreviations of company names. So you'll periodically want to sort your database by company name and provide the exact same company name in all records.

If you have a lot of turnover and want to keep training to a minimum, try this: Add a button that takes the user to a screen that duplicates the look of your data entry screen, but which adds explanation boxes for the type of data expected in the various fields. Just don't underestimate the value of good training.

Why would I bring up spellcheckers when the discussion is about databases? These days, many databases have spellcheckers built right into them. Many of my clients create their letters right in their database rather than exporting the member list to merge with a word processor. So it's an appropriate discussion here.

Spellcheckers are great as far as they go. But you shouldn't trust them completely. A word can be spelled absolutely correctly and still be terribly incorrect in context. Take this sentence for example: "My spell checker nose as soon as a mist ache is maid."

Another problem with spellcheckers is they sometimes highlight so many words (like proper names) that you stop paying attention to them. One time while trying to correct a word, I accidentally clicked on the "Learn" button. So now my checker thinks "adn" is the correct spelling of "and."

With all that in mind, when you're preparing a big mailing, actually have someone reread the letter and other materials. The same goes for a particularly important letter to some individual. And if you personally sign the letters, you'll have one more chance to catch any errors. It might be worth the writer's cramp.

Another thing to watch out for is invisible characters. Returns and tabs in text fields can put extra blank lines in the addresses in letters, envelopes and mailing labels. And it can mess up data that gets exported for use in Excel and other databases because it causes the columns to shift, moving the data into the wrong columns. Many databases have data entry controls at the field level that can remove these invisible characters. Find out how they work and implement the feature.

It's a good idea to make it part of someone's job to review new records about once a week. When new records are added to a database, they go to the end of the list. However, many databases sort the records. You can un-sort the records and review just the most recent ones, looking for errors in spelling, upper- and lowercase, and hidden characters like tabs and returns, most specifically in names and addresses. And don't forget to look for essential data that is just plain missing. I've seen many records with a city, state and zip but no address. Where's that envelope going to go?

Part of the review should include checking new records against existing records for duplicates as mentioned previously. You want to spot them as quickly as possible so you don't start attaching invoices to the new contact or sending out multiple items as part of your mailings.

If you lose your data, it won't matter whether it's clean or not. Make good backups and test that you can recover from the backups at least once every couple months. Be careful not to overwrite your original files. After you've recovered your files, open them and make sure they work. If you're still using tape backups, I strongly recommend you find another system. All twelve clients I've worked that used a tape backup were not able to recover their files when they had a crash. The daily process reported a successful backup every single time, but the software lied. Don't trust tape. Hard drives are cheap these days. Enough said.

So, did any of these ideas send up some red flags for you? Well, do something about it! Spending just a little time setting up the right tools and procedures in your database will reap many rewards. You'll save money, you'll save time, you'll look as professional as you really are, and you'll sleep better at night.

J **


Of course, the main purpose of this newsletter is to remind you that I'm here to serve your FileMaker needs - development, support, and training.

If you can think of anyone you might refer me to, I offer a 5% "Thank You!" for the first 2 years of billing on referrals.

I absolutely will not sell (or give) your email address to ANYBODY!!!

J **

< Back to In the News


Email us here.