Using Lookups to Control Data Integrity

If you’ve ever taken a GoldMine training class with Bob Ritter, President of First Direct Corp. I’m sure you have heard the expression “Dirty data is the enemy of marketing!” Sometimes with all the bells and whistles of an advanced CRM system we forget that the most important element of any database is the data itself.

Reporting, analysis, marketing campaigns, direct mailings, e-mail blasts – All of these depend on a solid foundation of good data. Poorly managed data is why information is missing from your reports, why bounce-backs are in your inbox, and why “undeliverable” is stamped on a whole bunch of those nice glossy flyers you just mailed out. I am going to show you how to increase the consistency and the completeness of your data while at the same time making data entry quicker and easier, by explaining the features and functions of GoldMine’s lookups.

Every field in GoldMine has its own lookup list and lookup window. A field’s lookup list is simply a list of values that are appropriate for that particular field. A field’s lookup window is GoldMine’s interface used to select values and it also contains functions for managing the lookup list itself. To see a field’s lookup list, click into a field and click the little triangle at the end of the field. The last selection on the list is “lookup window…” which open’s the field’s lookup window. You can see that there are buttons to add, remove, and edit lookup list entries (“New”, “Delete”, and “Edit”). There are buttons on the bottom used to select an entry or to cancel out of the lookup window (“Select” and “Cancel”). Clicking the “Setup” button brings you to the F2 Field Setup window (F2 refers to the keyboard shortcut which can also be used to access a field’s lookup window – and a simple right click in a field will open the lookup window too.) You must have master rights in order to change the settings here. The Field Name textbox value is used to set the title bar text of the lookup window. Usually this matches the field’s label name.

The Field Settings area is where you can define how this particular lookup window interacts with the user. Let me explain those options and what they do when they are checked and turned on:

■Allow blank input – Allows the user to traverse through the field without entering any data in that field.
■Force valid input – If a value is entered into the field, that value must be selected from or match an entry in the lookup list.
■Insert Closest Match – Inserts the entry in the lookup list that is closest in alphabetical value to what the user typed into the field. (Only applicable when Force valid input is turned on.)
■Capitalize first letter – Forces the first letter of a value typed into a field to upper case.
■Pop-up when selected – When a field is selected (blinking cursor) the lookup window for that field will automatically pop-up.
■Allow Adding, Editing, and Deleting – Allows non-master users to perform these functions for this particular field’s lookup window. These checkboxes enable/disable the New, Edit, and Delete buttons mentioned previously.
■Auto-fill – Automatically fills the field with matching lookup list values as the user types characters into the field. Similar in functionality often seen in applications like Excel and web forms in browsers.
Lastly there is the checkbox and dropdown for “Import lookup entries from another field.” This is a great time saver when you have more than one field where the same lookup list applies. (Hint: The fields are actually listed by their lookup window Field Names/lookup window titles.) The import function copies the values from another field’s lookup list into the current field’s lookup window. It is not however a permanent link, just a one-time copy.

Here are some recommendations to help you get the most out of GoldMine’s lookup window features:

■Turn on auto-fill and force valid input whenever possible. This will not only make data entry more consistent but will also make data entry quicker and easier for users. Say goodbye to spelling mistakes and typos!
■When not allowing blank input and forcing valid input make sure you give users a way out. Create a selection for “n/a” or “Unknown”. Otherwise users will be forced to select a value which may not be relevant just to get out of that field.
■Do not allow non-master users to add/delete/edit lookup entries for important fields. You should determine who in your office will be responsible for maintaining your lookup lists. This person will need master rights, and will have to be able to respond to users’ requests in a timely manner. Users should know how to and to whom they should submit lookup requests.
■Do not use “Insert closest match”. It just seems to invite incorrect data entry.
■Do not turn pop-up and auto-fill on for the same field. Use one or the other there.
■Watch out for field lengths! It is possible to create a lookup entry that is too big for the field. Letters will get truncated, or the end chopped-off, when selected.
■Lookup entries can be coded with descriptions by using the “CODE // Description” notation. For example, let’s say we have a field called Territory. The lookup entries may look like this (N // North, S // South, E // East, W // West) Only the letters before the “//” go into the field.
■Putting a semi-colon after each entry in the lookup list allows for multiple entry. Let’s say we had a field called Product Interest. Since one contact can have interest in multiple products the lookup list might look like this (Books; , Pencils; , Paper;)
Hopefully this gives you not only a better understanding of GoldMine’s lookup window controls, but also illustrates that proper setup of GoldMine’s lookup lists and lookup windows directly impacts the consistency and ease of data entry. Please note that not only are there lookup lists for contact record fields, but also for all of the activity dialog fields when scheduling/completing calls, appointments, and other activity types as well. Following these guidelines should improve the integrity of your data and will lead to more accurate targeting, more complete reports, and an increased overall effectiveness of your efforts.