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 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:

    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.