Coordinating a Single Database from Multiple Sites
Part 1: Adding New Parent Records at a Remote Site
by Robert W. Newman, December 11, 1999

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:

1. New invoices had to be transmitted from the remote site to the home site on a daily basis.
2. Changes to old records at the remote site had to be updated daily at the home site.

Serendipitously, there already was a utility to update the inventory from the current invoice/installation table at the press of a pushbutton, so there was no need to transmit inventory table changes. Whether or not it was proper design, the invoice table contains all the model and serial number information from the installation process, so the updates to this table are made at the time the air conditioner is installed, including detailed directions to what are often remote, country locations. I finally settled on two forms, one for exporting and importing the new invoices, and one for handling the changes to previous invoices; and each form has two pages, one for the remote site and one for the home site. When the remote site opens the utility, the form defaults to page one, and at the home site it defaults to page two.

The Export and Import Forms for Invoices

The operator at the remote site sees:

The operator at the home 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:

- I originally envisioned the two procedures as independent of one another, and thought they might be done at separate times or at different frequencies. This was flexibility that caused confusion, so the procedures were joined conceptually.
- I could avoid potential problems of files being copied to the wrong folder, with the resulting phone calls saying that databases were not updated or records have been duplicated. The extraction path is now coded in the procedure.
- America OnLine, Bruce’s Internet Service Provider, has a nasty habit of converting multiple ZIP attachments to MIME format.

At the home site the import utility is loaded and the files unzipped. The one-record table,`sendinv.dbf`, contains the date info and the last invoice in the data sent,`recntinv.dbf`; the last invoice in the current invoice table is inserted into the corresponding field. The View Tables button allows the user to be sure the data is correct, but became superfluous as I continued to build error trapping into the procedure. Clicking on Import appends these new invoices to the resident database. Next the user invokes the Update utility, unzips the files, and does the actual update. The `updtinv.dbf` contains the dates and the invoice numbers in the `chngedinv.dbf`. The Do the Update button opens the appropriate tables and updates the home site invoice table from the `chngdinv` table. The Notes (memo) field is untouched, since each site uses this for different purposes. The Compare Tables button allows the user to view the records that will be changed, and what those changes will be. Limited editing is also possible from this utility, but not encouraged.

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.

Coordinating a Single Database from Multiple Sites
Part 2: Adding New Child Records at a Remote Site
by Robert W. Newman, December 11, 1999

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` `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.