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 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
Set Safety Off
&& To avoid messages
Select sbac
Copy To RecntInv For invoice_no
> form.Entryfield1.Value .and. ;
invoice_no
<= form.Entryfield2.Value
MsgBox("Export complete", "Info",
64)
Set Safety On
Procedure IMPORTBUTTON_OnClick
Select sbac
Set Order To invoice_no
&& Just to be certain it's set
Go Bottom
Form.cLastInvoice = sbac->invoice_no
If Form.cLastInvoice >= sendinv->last_sent
MsgBox("It
appears that you have already appended this set of records. ....","Warning",16)
Elseif (Form.cLastInvoice >
sendinv->prev_sent .and. Form.cLastInvoice < sendinv->last_sent)
MsgBox("It
appears that you have already appended at least SOME of the invoices in
this set of records. ...","Warning",16)
MsgBox("...
View Tables button, delete those records, then edit the 'prev_sent' field....","Info",64)
Else
Select sbac
Set Order
To
Go Top
Append From
RecntInv
MsgBox("The
new invoices have been appended. The latest invoice is "+form.Entryfield2.Value+".","Info",64)
Class::ResetLastInv()
Endif
Procedure ResetLastInv
Use sbac Again In Select() Order
Tag invoice_no Alias GetInv
Go Bottom In GetInv
Use last_inv Again In Select()
Alias LastInv
Replace LastInv->invoice_no
With GetInv->invoice_no
Use In GetInv
Use In LastInv
MsgBox("Since new invoices have
been appended, the Last Invoice Number has been updated.", "Information",
64)
Procedure SetNewNumber
Select SendInv
Go Bottom In sbac
*-- Place last invoice_no
in the last_sent field
*-- This shows as Last Invoice
to be Sent This Time on pg 1,
*-- and Last Invoice Being
Sent This Time on pg 2
Replace last_sent With sbac->invoice_no
form.Refresh()
Procedure RefreshNumber
*-- Place last invoice_no
in the the current table in the
*-- Last Invoice in Current
Database on pg 2 Shouldn't change)
Go Bottom In sbac
Form.cLastInvoice = sbac->invoice_no
form.Entryfield5.Value = Form.cLastInvoice
*-- Get value of Last Invoice
Being Sent This Time from unzipped file.
Select sendinv
form.Entryfield2.Value = sendinv->last_sent
Procedure ZIPBUTTON_OnClick
If File("recntinv.zip")
Erase recntinv.zip
Endif
Use In sendinv
&& Close it so it can be zipped
Run(.t., "c:\progra~1\winzip\winzip32.exe
-a -ef recntinv.zip recntinv.db? sendinv.dbf")
MsgBox("The RecntInv.zip file
is ready to email.","Info",64)
Use sendinv In Select()
&& need to replace data
Select sendinv
Replace prev_date With last_date,
prev_sent With last_sent
Procedure UNZIPBUTTON_OnClick
Use in sendinv
&& so can overwrite file when unzipping
Run(.t., "c:\progra~1\winzip\winzip32.exe
-e -o -j recntinv.zip")
MsgBox("The RecntInv.zip file
has been UnZipped.","Info",64)
Use sendinv In Select()
*-- Refresh the Entryfields
for Last Invoice in Current Database
*-- and Last Invoice Being
Sent This Time.
CLASS::RefreshNumber()
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
replace updtinv->last_sent with
form.Entryfield2.Value
Set Safety Off
Select sbac
Copy To ChngdInv For invoice_no
>= form.Entryfield1.Value .and. ;
invoice_no
<= form.Entryfield2.Value With Production
*-- Count the number of records
exported...
Use chngdinv in Select()
nExported = RecCount("chngdinv")
MsgBox("The changed invoices
in the range "+form.Entryfield1.Value+" - "+form.Entryfield2.Value+" have
been exported." ;
+chr(13)+chr(13)+
;
"The number
of records that have been exported is "+Ltrim(Str(nExported))+"." ,"Info",64)
Set Safety On
Select updtinv
Replace prev_date With last_date
Procedure UPDATEBUTTON_OnClick
Select sbac
Set Order To Tag invoice_no
Use chngdinv In Select() Order
Tag invoice_no
Select sbac
Update on
invoice_no
From ChngdInv Replace ;
sbac->unit_type
With chngdinv->unit_type, ;
sbac->ac_size
With chngdinv->ac_size, ;
sbac->ac_make
With chngdinv->ac_make, ;
sbac->ac_model
With chngdinv->ac_model, ;
sbac->ac_sn
With chngdinv->ac_sn, ;
<snip>
*-- Count
the number of records that were updated...
nUpdated =
RecCount("chngdinv")
MsgBox("The
invoices in the range "+form.Entryfield1.Value+" - "+form.Entryfield2.Value+"
have been updated.","Info",64)
Procedure ZIPBUTTON_OnClick
If File("chngdinv.zip")
Erase changdinv.zip
Endif
Use In updtinv
&& Close it so it can be zipped
Run(.t., "c:\progra~1\winzip\winzip32.exe
-a -ef chngdinv.zip chngdinv.db? chngdinv.mdx updtinv.dbf")
MsgBox("The ChngdInv.zip file
is ready to email.","Info",64)
Procedure NEWDATAZIPBUTTON_OnClick
*-- Create a newdata.zip
*-- Erase any previous copies
of NewData.zip and NewData.exe,
*-- since this could cause
problems if the user does not insure
*-- the new zip files are
the ones being zipped.
Close Tables
If File("newdata.zip")
Erase newdata.zip
Endif
If File("newdata.exe")
Erase newdate.exe
Endif
Msgbox("NewData.zip will now
be created.","Info",64)
Run(.t., "c:\progra~1\winzip\winzip32.exe
-a -ef newdata.zip chngdinv.zip recntinv.zip")
MsgBox("The NewData.zip file
is ready to be made into an EXE file." +chr(13)+chr(13)+ ;
"To make the NEWDATA.EXE, you must ...<snip> detailed instructions here
...","Info",64)
Procedure UNZIPBUTTON_OnClick
Use In updtinv
&& need to overwrite the file after getting info
Run(.t., "c:\progra~1\winzip\winzip32.exe
-e -o -j chngdinv.zip")
MsgBox("The ChngdInv.zip file
has been UnZipped.","Info",64)
Use updtinv In Select()
*-- Now refresh the Entryfields
so user sees the new values.
form.Entryfield1.Value = updtinv->first_sent
form.Entryfield2.Value = updtinv->last_sent
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.
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
Set Safety Off
&& avoid overwrite messages
Select sbac
Set Order To Tag invoice_no
Go Top
Copy to newinv For (sbac->invoice_no
>= form.Entryfield6.Value ;
.and. sbac->invoice_no <= form.Entryfield2.Value)
MsgBox("New Invoice are records
ready to be exported. Place a floppy disk in the A: drive.", "Alert",48)
Close Tables
*-- The files will be small
enough that zipping is not necessary, and they can be copied directly
to floppy.
form.MousePointer = 11
Copy File newinv.dbf to A:\newinv.dbf
form.MousePointer = 1
form.MousePointer = 11
Copy File newinv.dbt to A:\newinv.dbt
form.MousePointer = 1
Msgbox("Files have been copied.
You may remove the disk from the A: drive.","Info",64)
Set Safety On
Procedure IMPORTBUTTON_OnClick
Select sbac
Set Order To invoice_no
&& Just to be certain it's set
Go Top
If form.Entryfield5.Value >=
form.Entryfield3.Value
MsgBox("It
appears that you have already appended this set of records ... <snip>
give some options...","Warning",16)
Return
Else
Append From
newinv For invoice_no >= form.Entryfield3.Value ;
.and. invoice_no <= form.Entryfield6.Value
MsgBox("The
new invoices have been appended. The latest invoice number is "+form.Entryfield4.Value+".","Info",64)
Class::ResetLastInv()
Endif
Procedure ResetLastInv
Use sbac Again In Select() Order
TagG invoice_no Alias GetInv
Go Bottom In GetInv
Use last_inv Again In Select()
Alias LastInv
Replace LastInv->invoice_no
With GetInv->invoice_no
Use In GetInv
Use In LastInv
MSGBOX("Since new invoices have
been appended, the Last Invoice Number has been updated.", "Information",
64)
Export/Import Warranty Service:
Procedure Init
*-- Open the form on the
appropriate page for each App
<snip>
Procedure EXPORTBUTTON_OnClick
Set Safety Off
&& avoid overwrite messages
Select service
Set Order To Tag service_id
Go Top
Copy to newserv For (service->service_id
>= form.Entryfield6.Value ;
.and. service->service_id <= form.Entryfield2.Value)
MsgBox("New Service Warranty
records are ready to be exported. Place a floppy disk in the A: drive.",
"Alert",48)
Close Tables
*-- The files will be small
enough that zipping is not necessary,
*-- and they can be copied
directly to floppy.
form.MousePointer = 11
Copy File newserv.dbf to A:\newserv.dbf
form.MousePointer = 1
form.MousePointer = 11
Copy File newserv.dbt to A:\newserv.dbt
form.MousePointer = 1
Msgbox("Files have been copied.
You may remove the disk from the A: drive.","Info",64)
Set Safety On
Procedure IMPORTBUTTON_OnClick
Select service
Set Order To service_id
&& Just to be certain it's set
Go Top
If form.Entryfield5.Value >=
form.Entryfield3.Value
MsgBox("It
appears that you have already appended this set of records, ... <snip>
offer options...","Warning",16)
Return
Else
Append From
newserv For service_id >= form.Entryfield3.Value ;
.and. service_id <= form.Entryfield6.Value
MsgBox("The
new service has been appended. The latest service invoice number is "+form.Entryfield4.Value+".","Info",64)
Endif
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.