A Job Bank Application
Alpha Five V5 is a software product that is designed to make database development both efficient and economical. It can be used to solve a particular problem that occurs frequently in organizations that use database applications.
by Ed Gray
The Lancaster County Office of Aging in Lancaster, Pennsylvania was using a DOS based DBMS to manage the data in their 55+ Job Bank program. It served them well for over a decade. However, the county was mandated to upgrade all of their computers from Windows 98 to Windows 2000 and XP. They found that when they tried to use their existing database application on the newer operating systems, the application would exhibit problems such as data corruption or the application freezing.
The solution to this problem was to purchase a modern RDBMS product such as Microsoft Access. After studying the programming effort needed to use this product, they decided to use Alpha Five V5. Since none of the staff had formal programming training, I was asked to design, develop, and implement the new Job Bank application on a part time consulting basis.
The first step in the process for me was to perform a very thorough examination of the way that the users used the old application. This was needed to determine what changes were necessary to make the new application function better for the users. The users needed to be heavily involved in this step. Then it was necessary to analyze each data field to see if it should be retained as is, modified, or deleted. It was also necessary to see if any new fields should be added. Finally, a very detailed diagram was drawn up on paper that showed the fields in each table and the relationship of the records in each table with records in other tables. All of this had to be done before a single table was constructed or a single form designed.
Perhaps the most difficult problem was not so much in designing the application, but rather in dealing with the existing data that consisted of about 2000 records. As is generally the case, there is not always a direct correlation of the old data with the new data fields. For example, the client name in the old application had the first and last name in the same field. This field also used a tilde character to determine which part would be displayed first in a report.
I constructed the tables, forms, reports, letters, and labels that were required. I had to design a set of navigation forms containing buttons so that the users could easily accomplish what they wanted to without using the control panel. In fact, I disabled the control panel altogether so that the users would not accidentally get themselves in trouble by possibly modifying the application. I then added a database maintenance button on the main menu that would enable the control panel when pushed. This button was password protected to allow only authorized personnel access.
After the new application was finished and tested with test data, it was then necessary to delete the test data and transfer the actual data from the old application. Because the old data did not directly map into the new fields, I had to write an Extract/Transform/Load utility. This was actually easy to do using Alpha Five Action Scripting. I wrote a script that would read each original record one at a time, manipulate the data as needed, and then store the modified record into the correct table in the new application.
I had to write several routines in Xbasic to do the actual field transformations. For example, one routine took the old combined name field with the tilde character, determine which set of characters represented the first or last name, and then store the separated first and last names into variables before being written out to the new record. There were other similar transformation problems, but each was rather easily solved using a simple Xbasic routine that was inserted into the right place in the Action Script. When all was ready, the Action Script was executed and the new application then contained all of the approximately 2000 transformed records.
After the new application was tested by the users and found to be performing properly, it was placed into full production on the network server. As time went by, the users found that there was another function that they wanted. They needed to delete worker records that were more than four years old and employer records that were more than three months old. They found that after they had deleted the records, if for some reason they needed to see a particular record again, they would have to manually search through many filing cabinets to hopefully retrieve an old paper copy.
To remedy this, I decided to add an archive table to the application. It was to hold only deleted records. I did not set up any relationship with this table to any other table so that it would be isolated from the rest of the application. Now, before the users delete records from the working table, they simply run a query to return a set of records that they want to delete. Then they move the records from this set to the archive table. Since the records in the archive table will never be deleted, it will never be necessary to search through filing cabinets for old paper records.
There still existed what I considered to be a major limitation in the way the Job Bank application was being used. If an older citizen who lived in Lancaster County was interested in looking at what jobs were available, he or she had to drive to the city of Lancaster to view the job openings list. This is because the Office of Aging is located in the city of Lancaster. Many of the older citizens either no longer drove or did not own a car. Lancaster County is also rather large and is not well covered by bus service.
I thought it would be a good idea if we could post the current job openings on our Office of Aging website which is operated by the Lancaster County government. I looked into how to accomplish this and again found that Alpha Five V5 made this an easy and straightforward procedure. All that I needed to do was write a report that would display the current job openings in the standard Alpha Five format. Then this report could be saved as a dynamic html file into a directory that could be accessed by our web server. I wrote the report and added a button on the reports menu form. Thus, it is now very easy for the users to update the job list on a periodic basis on the web server. You can see the list for yourself by going to:
http://www.co.lancaster.pa.us/lanco_aging/site/default.asp
Click on "Employment Opportunities"
Click on "55+ Job Bank"
At the bottom of the page, click on "click here to view the current job list"
After completing the Job Bank application, I went on to complete six more Alpha Five database applications here at the Office of Aging. I also have several more Alpha Five database applications waiting for me as future projects. These projects represent typical applications found in government that can be easily designed, developed, and implemented using Alpha Five V5.
I am glad to see Alpha Software partner with Pace Training and T3 Corp to make their products and services available to the Federal, State, and Local governments. This, of course, represents a huge market. Because of the ease of use of Alpha Software products, the government will benefit by reducing their cost in database development efforts.
I am also excited about the upcoming Alpha Five V6. I think that Richard and Selwyn Rabins have remained totally focused on maintaining the ease of use of this product.
The database applications that can be accessed over the Internet will be easy to develop compared to using other products such as Visual Basic.NET. Since the data can be fully encrypted, this product should be very popular with government applications.
I have found Alpha Five V5 to be extremely easy to use for database development. It is especially useful for converting older database applications so that they will run properly on modern operating systems. I strongly recommend that anyone considering writing a database application should look at Alpha Five V5. You will save yourself a great deal of effort and frustration and will be quite happy with your resulting application.
In closing, I wish to express my appreciation to the Lancaster County Office of Aging for giving me permission to publish this article.