Branch networking on e-mail

Small companies that can’t afford to set up privatenetworks are now looking at e-mail as a means of data transfer. A typicalexample of such a company would be a medium-size enterprise with regionaloffices in all the major metros and eight to ten branch offices. Such a firmwould want a system that allows information about sales, stock, receivables,orders, and cash balances to be uploaded to the head office every night.Managers at each of the branch offices would generate a file containing thelatest transactions, which would be sent as an e-mail attachment to the headoffice. The data would then be imported into the main applications at the headoffice.

On the surface, such a system seems relatively easy todevelop and implement. All one requires is that the main applications have thecapability to (a) Export selected transactions in text or an acceptable databaseformat and (b) Import data from an external file. Unfortunately things are neverthat simple. Synchronizing data across multiple installations of the sameapplication can be a very complex process, as the rest of this column will show.

To start with, let’s divide the data maintained by theapplication into two parts–master data and transaction data. Master datarefers to data that changes infrequently, for example, the customer, product,and sales tax database. Transaction data refers to things like invoices,receipts, and bank transactions. It’s clear that the application mustsynchronize both master and transaction data.

The first hurdle is that of coding. One must ensure that thehead and branch offices do not assign the same code to different entities. Thisproblem will usually arise for entities like customers where new customer codesare assigned every day. Fortunately this problem is very easy to solve–breakup each code into two parts. The first part could be a prefix specifying theoffice, whereas the second part could specify the entity. For example, eachbranch office can be given a unique prefix such as BR1 or BR2. Customer codescan then be of the form BR10001, which will signify customer 0001 of branch BR1.Such a method will eliminate duplication in codes.

The next hurdle is to decide what master information must betransmitted to the head office each day. This step is nowhere as simple as itlooks. At the end of each day, one must be able to generate not only a list ofall new records added, but also a list of all records modified. For instance,synchronizing a customer database will call for generating a file consisting of(a) All new customer records created during the day and (b) All existingcustomer records modified during the day. Part (b) can be very tough becausevery few applications can generate an audit trail of changes to master data.Every single change in the master file, ranging from a change in customeraddress to changing credit limits for large dealers, has to be tracked andcommunicated to the head office.

One must carry out a similar exercise with regard to thetransaction data. A regional office may modify an invoice after a copy has beentransmitted to the head office. It’s clear that the software must either lockall transactions that have been transmitted and not allow changes or generate alist of changes that must be e-mailed to the head office.

Many applications modify transaction database records as thetransaction proceeds. For example, a receivables package will usually monitorthe balance due on each invoice. Such an application might keep the balance duein a single field and overwrite the field when a payment is received. Suchapplications can be almost impossible to synchronize. One must take care thatthe application generates a separate record for each event in the transaction.The software should be able to reconstruct the current state from thetransaction data.

Let me end with a caveat–export-import features do not workproperly in more than 90 percent of the packages in the market. You shouldthoroughly check out exactly what can be exported if you want to synchronizedata using e-mail.

The bottom line A lot of groundwork is needed to exploite-mail for synchronizing commercial applications data. The end result more thanjustifies the work involved. 

Gautama Ahuja, acontributing editor of PC Quest,

runs a turnkey software development company, AHC Infotek, in Delhi