The Need
I built a rather complex application for a client to track sales, installation, and warranty service of air conditioning units for mobile homes. The project began in late 1995 and has, I believe, finally been completed. It is necessary to know the genesis of the project to appreciate why it was necessary to build the module that I wish to describe in this paper. I received a call from Bruce asking if I could build a database program for tracking his warranty service claims, a task he was attempting to maintain with a box of 4 by 6 cards. It was not working well and he found himself making repairs, only to find that the equipment was out of warranty and he would not be reimbursed by the manufacturer. This was costing him money. He had a brand new Digital computer with a 75 MHz Pentium, and knew absolutely nothing about computers. And so we began…
As I grew in my understanding of his business, and as he increased in understanding of what could be done with this thing on his desk, we added features, capabilities, reports, and more features, and… you get the idea. By 1998 we had a program that was capable of not only tracking warranty records, but was used for invoicing, inventory, accounts receivable, statements, installation details, installer and salesman pay records, and warranty service records, including a module to submit the warranty reimbursement on preprinted multipart forms for three manufacturers.
The business is growing, and since much of the work is being done in South Georgia, Bruce decided to move his warehouse 150 miles south of Atlanta, where he lives, and to hire a person to handle the invoicing and installation from that site. By this time we had expanded his current site to include three networked Pentium IIs and a fourth (standalone) Pentium II, but more about that later. I built a Site version of the program that did not permit access to any of the sensitive financial information, but allowed the operator to create invoices and installation records for new purchases, a copy of which was then faxed to the Atlanta office where the information was manually entered into the home site database. You can imagine how well this worked. It didn't, of course. On top of that, every time Bruce had a great idea for a new feature he couldn't do business without, I had to add the feature to the Site version as well. This was an unnecessary expense, and caused things to break as the two versions began to diverge further and further. This, in itself, was a valuable lesson; so I went back and recreated the two programs from a single code base and made the financial information invisible in the off site version. There are two EXE files, and a piece of code in the start program determines what objects will be available to that particular EXE. (Actually, there is a third EXE for the standalone computer mentioned earlier, and this was named Lite.) The solving of the problem that I wish to present in this paper is: How to coordinate the two databases at the two locations, while still allowing for necessary independence of operations at the two sites. The final WFMs that are pictured here contain some remnants of the developing and refining process, e.g., pushbuttons that can be useful but are not really needed any longer. This was another worthy lesson I learned from the project. Whereas I saw advantages in flexibility, the users often were confused by the possible options; so I ended up automating almost everything.
What are the requirements for this module? I actually had to build two modules to accomplish the desired end:
The Export and Import Forms for Invoices
The operator at the remote site sees:
The Procedure
The user at the remote site, having entered new invoices and edited other invoices after the installer reports back with the serial numbers of the air conditioners, coils, furnaces and the accessory equipment required for the installation, prepares to send the new data to the home site. The form reads a one-record table that contains the date when data was previously sent, the last invoice previously sent, and the last invoice in the current invoice table (to be sent this time). These fields can be edited if necessary, but ought not to be. The Export button creates recntinv.dbf, and the Create Zip File button invokes WinZip 7.0 to create recntinv.zip. The user then loads the second utility and enters the first and last invoices that were modified during the work day, exports to chngdinv.dbf, and creates chngdinv.zip. The Create File NewData.zip again button uses WinZip to put both these files into NewData.zip, and the user finally makes the self extracting NewData.exe, which is attached to an E-mail to the home site (and a copy is sent to me for monitoring).
The NewData.exe file is typically 150 Kb, and is convenient for E-mailing as an attachment. The chngdinv.dbf is usually less than 100 Kb and recntinv.dbf is usually 10 Kb; the other two files are minuscule. The chngdinv.dbf contains the entire set of records from the first modified record to the last modified; there is no attempt to filter the table. The user at the home site downloads the attached file and extracts the two zip files into the application folder. The reason for the two ZIP files and the addition of the self-extacting EXE file is several fold:
The Tables
Sendinv.dbf and Updtinv.dbf have the same structure.
The Code
I should probably state immediately that this code evolved; in fact, these forms began life as separate utility executables and were incorporated into the main application during the final stages of development. (Part of the initial flexibility that later had to be removed.) This means there is undoubtedly some extraneous code lurking in the bowels of the modules, but it has been working flawlessly for several months and so it remains.
New Invoices
Form:
Procedure Init Close Tables Use sendinv In Select() Use sbac in Select() Exclusive Order Tag invoice_no Select sendinv Replace Last_date With Date() Do Case Case _app.AppName = "seasite" *-- Refresh the Entryfields for Last Invoice Previously Sent and *-- Last Invoice to be Sent This Time. Replaces the Set New button. CLASS::SetNewNumber() form.PageNo = 1 form.Tabbox1.CurSel = 1 Case _app.AppName = "seaboard" *-- Get the value of the last record in the current sbac.dbf, *-- The Last Invoice Being sent This Time field will be updated *-- when the unzip is completed. Class::RefreshNumber() form.PageNo = 2 form.Tabbox1.CurSel = 2 EndCase Procedure EXPORTBUTTON_OnClick
Procedure IMPORTBUTTON_OnClick
Procedure ResetLastInv
Procedure SetNewNumber
Procedure RefreshNumber
Procedure ZIPBUTTON_OnClick
Procedure UNZIPBUTTON_OnClick
|
|
Update
Invoices Form:
Procedure Init Close Tables Use updtinv in Select() Use sbac In Select() Exclusive Order Tag invoice_no Select updtinv Replace Last_date With Date() *-- Open the form on the appropriate page for each App <snip> Procedure EXPORTBUTTON_OnClick
Procedure UPDATEBUTTON_OnClick
Procedure ZIPBUTTON_OnClick
Procedure NEWDATAZIPBUTTON_OnClick
Procedure UNZIPBUTTON_OnClick
|
|
Concluding Comments
I have included what may seem to be rather trivial code segments, but there are subtleties that gave me numerous problems along the way, including such things as users not pressing the buttons in the proper sequence, or importing a set of invoices twice, or not getting the proper files zipped and E-mailed, or downloaded and unzipped at the other end. One major lesson I learned from this project is that we, as programmers, have tacit knowledge about the underlying program design and code that makes many things quite obvious to us as we test the usability of our application. Our clients do not have this tacit knowledge, and it is not possible to circumvent this; so we have to constantly design with it in mind. Michael Polanyi, scientist and philosopher, described tacit knowledge as knowing more than we can tell. Furthermore, he argued that tacit knowledge cannot be taught, but is acquired by doing, usually under an apprenticeship. I have come to believe that participation in the dBASE newsgroups can be as close to an apprenticeship that many of us will ever have the good fortune to experience.
The Need
However, this is not the end of the story. Simultaneously with this project, Bruce decided he was going to hire someone to handle the warranty service records at yet another site. The installed air conditioning systems are under warranty for parts and labor for a one year period (or three years if the homeowner purchases the extended plan). If a customer calls for service, a repairman is dispatched to make the necessary repairs, and a record of replaced parts and labor charges is entered into the service.dbf in the database. This table is, of course, linked to the invoice table (sbac.dbf) through the invoice number.
It must be acknowledged here that I used the invoice number as the KEY before I learned about seqvalue.cc and the wisdom of not using a data field as the KEY. If I were to begin this project all over again, I would certainly do it differently. At this point the client is not willing to take on the expense of redesign, and I choose not to do it for free. But, there are times when I feel I am paying for it when I have to make modifications that would have been simpler had I done so.
To place this application on yet another remote computer created additional challenges, problems that were not encountered with maintaining invoice integrity. This utility required passing new invoices to the warranty service site, and then passing new service records back to the home site. I knew that I already had the module to send the new invoices to the service site, but now I had to devise a way to get the service records back to the home site, and these new child records had to be properly linked to the parent invoice records that were already in the home site database table.
Furthermore, I had to be able to send only the new child records, and not duplicate any previously existing service records on the home site database. This demanded a new sequential numbering scheme that applied only to the service.dbf table. It was therefore necessary to modify the program and the service.dbf to include a service_id KEY that would be generated at the service site using seqvalue.cc. (This required using LocalSQL methods in the start.prg to add the additional field to the tables involved in the modification, since these tables were already populated on several computers at several different sites.) The KEY linking the service.dbf to sbac.dbf (the invoices table) remains the invoice_no field, so there is no need to inform the home site database about these service_id numbers; they are only used for tracking what service records have been generated, and which ones need to be sent back to the home site. They are, however, displayed in the service form, and can be utilized for identification purposes.
The Warranty Service Form
The service information on this form is contained in service.dbf; the customer, dealer, and unit description (on the third page of this form) are contained in the linked invoice table (sbac.dbf); and the warranty claim data (on the fourth page of this form) is also contained in service.dbf. The submitted and tracking checkboxes were added because somewhere in the development process I also built a small app that would track the submitted claims.
It is this information now in the service.dbf that must be transferred accurately to the home site database tables. As mentioned above, the Warranty Tracking application mentioned above imports the service.dbf records having submitted = .t. to that workstation, and then marks tracking = .t. in that service record on the server database.
The Export and Import Forms for Service
The Procedure
These forms, and the code behind them, are similar to those in the previous paper, but there is no need for zipping these tables because they can be hand carried or mailed to the service site. In fact, the files are copied directly to the 3.5" floppy disk, and the import is also done straight from the floppy. In place of the View Files button there are only entryfields displaying the pertinent information. You will also note that the sending of the invoices is based upon the invoice number, whereas the service records are referred to by the service_id number.
The Tables
Lastsent.dbf and Lastserv.dbf have the same structure. The fields in the lastsent table contain the invoice_no for the records being sent whereas the fields contain the service_id when the service records are sent back to the home site. Dates are not required for this form, and this data transfer may only occur once or twice a month, rather than on a daily basis.
The Code
Export/Import
Invoices:
Procedure Init *-- Open the form on the appropriate page for each App <snip> Procedure EXPORTBUTTON_OnClick
Procedure IMPORTBUTTON_OnClick
Procedure ResetLastInv
|
|
Export/Import
Warranty Service:
Procedure Init *-- Open the form on the appropriate page for each App <snip> Procedure EXPORTBUTTON_OnClick
Procedure IMPORTBUTTON_OnClick
|
|
Conclusion
What I have attempted here is to present solutions to what initially appeared to be an insurmountable problem: maintaining coordination and data integrity between database tables at separate sites by updating the home site invoice and installation tables on a daily basis, and the service records on a bimonthly basis. In an ideal set of circumstances there perhaps would have been a better solution, but under the constraints imposed, a viable solution was indeed found. The system works, customer is happy, and I learned some new programming strategies. It is my hope that you might be encouraged by my success in this project, and that it might also encourage you to attempt the seemingly impossible.