Data Entry Constraints
How to ensure consistent value formats

by Bill Marriott

If you've ever done consulting for a small business or organization, one of the headaches you've invariably run into is data entered a variety of different ways. Some dates have two-digit years while others have four. Some cash amounts have currency symbols and commas while others are plain. The variations are endless, it seems.

One reason for this is that data may have been entered by different people at different times. Another common reason is importing information from different sources. Whatever the cause, it makes it impossible to get accurate reports until the data is cleaned up. Revolution can make that cleanup a lot easier, thanks to its powerful chunk expressions and other commands.

But the best thing is to ensure those kinds of data entry errors never enter the system in the first place. Revolution excels at that, too. Off-the-shelf database software packages often have some kind of rudimentary data validation facility. But typically they do so after-the-fact. The end user of the system can enter whatever they like into a field, then the database pops up with a generic message about requiring a numeric value, for example.

How much more preferable would it be if:

- Only numbers could be entered, not letters or punctuation
- Formatting such as hyphens and spaces were entered automatically
- The cursor jumped from field to field automatically
- And so on

These kinds of sophisticated filters can speed data entry at the same time making it less error-prone, without the jarring interruption of a dialog box. You can have the system beep, for example, when a non-numeric digit is entered… or do nothing at all.

Unlike the off-the-shelf systems, Revolution can script the entry of data in virtually any way you want. It's due in a large part to the event-oriented and message-passing setup of the system.

If you've used Revolution at all, you know you can just add a field to a card and then click the Browse tool and type something into it. But a lot happens between a user pressing a key on the keyboard and it appearing on the screen. You can view the kinds of events being triggered by turning on the Message Viewer.

With respect to data entry, we're interested specifically in the following events:

- keyDown
- rawKeyDown
- returnInField
- enterInField
- returnInField
- tabKey
- arrowKey
- escapeKey
- openField
- closeField
- exitField

These are just some of the possible messages sent to a field during data entry. By “handling” them, you can control what happens as the end user interacts with the field, with high granularity.

To “handle” the event, you write a “handler” in the following format:

on <event>
   do something
end <event>

Some of the events listed above come with some extra information, called a parameter, which you can access like this:

on <event> <variable to hold the parameter>
   do something
end <event> 

Here's an example which will show you how this works with a very useful message for handling data entry:

on rawKeyDown theKey
   put theKey
   pass rawkeydown
end rawKeyDown 

If you put this into the script of a field, you'll see the computer's internal code for any key you press while within the field appear in the message box. Breaking this down: rawKeydown is the event, or message sent, to the field. theKey is our name for the parameter … the extra bit of information sent along with the message. pass tells Revolution to pass along the message, so the results of the keypresses actually appear in the field. If you try this out, you'll see numbers used to represent various keys on the keyboard. Some of them are identical to what you'd get with the chartonum/numtochar functions, but some of them aren't. Watch what happens as you press the shift key, delete key, arrow keys, escape, etc.

(An interesting aspect of rawKeyDown is that it can be used to track and respond to the mouse wheel, if present. The clicks as you roll it up and down are considered keypresses.)

So how difficult is it to force only numbers to appear in a field? It's easy. Try the following script:

on keyDown theKey
   if theKey is a number then pass keyDown
end keyDown

Now you'll find that only numeric input is accepted into the field. Note that because we're using keyDown and not rawKeyDown, you can still backspace, use arrow keys, etc. to edit the value. As your data entry needs are more sophisticated, you can have more lines, and multiple handlers within the field script. Here are some ideas:

- Program the Escape key to clear data in the field, or restore a previously entered value
- Test with each key how many characters have been entered, and insert hyphens as needed
- Automatically consider the right two digits to be after the decimal, without requiring the decimal key be pressed (like some cash registers)