The World got itself some more Data Entry Screens

Last week I built some dozen and a half new Data Entry Screens for use within my company's homegrown Access database. We use it as a front end to our SQL Server. The whole system has a couple of features like double data-entry with a built-in Data Compare. The results of the compare will be visible to the user in electronic of paper form, and can then be looked up in the original paper forms for adjustment. In the end of course, the data in both datasets will be the same, and the data is declared 'clean'. Other features are an Audit Trail mercilessly logging al updates made to once entered data. The user has to give a reason for making the change. Trail data is also available to the users. We work with roles: some people have a limited ability to alter data or enter some new system-wide information, other are only allowed to enter data and nothing else. All kinds of reports can be automatically printed by the user. As a matter of fact, the whole system is self supporting for 95% of the time. Only some key information is to be added and updated by us, the administrators when necessary.

Validation
But before my new Data Entry Forms can be used in the system, some validation needs to be done. First of all, I open up the forms myself and enter some random data. This is to find any typos in field names or something. Most of the Form is built by hand; well, some back-end code is generated automatically, but the fields have to be placed on the Form by hand, of recycled from some old Form. One can understand that errors on my behalf are possible. Well, after the Forms fire up nice and cleanly, it's time to make the dreaded Test Protocol. This is a paper manuscript, which a Data Entry Clerk must follow closely to get some test data into the screens. The test data consists of some test patients (patients are the main entity in this -medical- database. One test patient’s data is used later for testing of the export to the statistical package; another has data which is completely different in both data entry runs (we have double data entry, remember?). This is done logically to test the Compare function: all these different fields have to be coughed up by it after being keyed into the system. Another patient will have completely similar data in both runs: the expected result from the Compare will be obvious to the considerate reader. Other test patients are used for other system functions (for example, because a lot of data can be missing, unreadable etc, we use a range of standard codes to address the issues; for these 'filter codes', but it's a bit too much detail for now. Let's consider the Test Protocol done for now.

Be Careful
Other things can go wrong, for example me forgetting to put the appropriate user rights on a newly created SQL Server table. Funny thing is, this will become obvious quite soon because we build these new tables into the production system. Now it's possible some database function which is used by one of the regular users of the system, and which needs to check all tables of some kind in the database, that it gets stuck on this table (mmm... just let me think on rewriting this sentence a bit). The error the user gets in his / her screen -which is obvious to me and can be fixed in seconds- will get my attention to details back quite quickly, as this should rather not happen :-)


[ASIDE]
Now I hear you guys rambling: for goodness sake, why do these morons work on the production server while building new screens into their live systems ?!? Let me tell you, as a two-man IT Department of a small company, it's just not an option to have a dedicated server at our disposal for testing purposes. It would be a case of too much money on one side, and too much administrative hassle on the other. Let me tell you, I've seen examples far worse than our situation. For the size of company we have, we're doing quite well. And it actually works very smooth. SQL Server has its nightly database backups and hourly backups of the Transaction Log. Tape backups are performed at night. So in case of a catastrophe we lose at most one day of work (the catastrophe happening at 6pm and taking the tape streamer in its decline, as everybody will. And in case one of us screws up with the database, at least we'll lose no more than an hour of data entry. Which I can live with. And o yeah: we do check our tape backups occasionally, thank you very much
[/ASIDE]

Go and Test 'em
Were was I.. Ah, I see we just finished the Test Protocol. OK, now some basic printing is done (at least our power printer / copier does holes and staples itself, can print double-sided with no trouble etc) on some packs of paper. Don't forget to insert the Access printouts of some Form Security checks, before making some additional copies, or you have to insert copies into copies by hand. We have different kinds of form security in the sense that sometimes it must be necessary to enter new information on the same screens, while on other occasions (read: screens) this must explicitly no be the case. Most of the times this works reasonably well. The only problems raises when one wants to use an old template screen in a new situation: suppose in the new situation it must be able to add new information, while the old template doesn't allow. A decision must be made to make a new copy of this screen, or adapt the old one. In the former case the whole test phase must be walked through again. Which costs me probably about 3 hours divided over multiple days during the test phase. And other people it will take some additional hours to enter data, performs checks etcetera. It is feasible that sometimes a trade-off will be made between the additional hours of work that have to be put in and the ease of just tweaking the old template a little bit.
Now most of the work for me is done. Only, chances are some Data Entry person will come back to me failing to try and enter some test data. After some little fixes being made, everything is supposed to work as a charm. I expect the Screens I built last week will be available to the system next week. Don't know if that's a good score compared to the way that is worked in bigger companies and / or IT Departments, but that’s for you to let me know in the comments.

For now I consider this the end of the article. Just wanted to let you know a bit about life on a small IT Department. It probably is completely different from your daily working environment. Hope you found it interesting reading, and if you'd like to hear some more stories , tell me what you want to know.... until next time