XML component streamlines client's order entry system
By using XML programming, Excel, Access, and Active Server Pages, one consultant automated a client's order entry process, cut the time the client had to spend rekeying data, and eliminated order entry errors.
If your clients are like mine, the projects that really get their attention are those that eliminate repetitive tasks or save them time and money. If you can help them with all three, as I did on a recent engagement, you've likely endeared yourself to the client.
Recently, I was hired to extend an existing order entry system by integrating XML programming with Microsoft Excel, Microsoft Access, and Active Server Pages. Here's what the client, a distributor of printed and promotional materials, was facing and what I did to fulfill the contract.
Background
My client used a third-party system for order entry, inventory maintenance, and invoicing. The typical workflow went like this:
1. An order would be called in to a salesperson by a customer.
2. The salesperson would key the order into the system and generate a purchase order.
3. When ready to be shipped, a pick slip would be created and the items would be deducted from inventory.
4. An invoice would be created and sent to the customer.
The system also had an e-commerce component that would present customers with a catalog, and they could place their order via the company's Web store. In this case, instead of the salesperson keying the order into the system, the order would be generated by the Web store and uploaded into the system at the end of the day.
My client also had two large print providers who had their own order entry systems. In these cases, the orders would initially be entered into these other systems and my client would receive notification about the order in one of two ways.
My client would log on to the first provider's system via a Web browser and run a report of items that were ordered. The second provider's order entry system would generate an e-mail that was sent to my client. In each case, these orders would then get rekeyed into my client's order entry system by a salesperson. Initial estimates said the salespeople spent more than 60 hours a week rekeying these orders.
My job was to automate the order entry process, to reduce the number of hours spent rekeying the data, and to eliminate any order entry errors.
The solution
There were two ways of entering orders into the system. One method was to open the desktop client and key in the order. The second was to enter an order on the Web site, which wasn't directly linked to the order entry system.
Once a day, orders keyed into the Web site were written to an XML file, which was copied to the application server, where the system would import the XML and create orders from it. This was where I would hook in. I would take the order data from the provider's systems, generate an XML file with the same schema as the Web catalog, and load it onto the application server.
I ran my ideas past the vendor, and it verified that the approach would work. A few signed nondisclosure agreements later, and I had the XML schema I needed. The architecture of my solution would be straightforward.
I would open the report from the first provider's Web site in Microsoft Excel (a feature it already provided), extract the order data through the Excel Object Model, and pass the data to a component that would generate the XML file.
The approach for the second provider was similar. I would retrieve the e-mails in Microsoft Outlook, extract the order data through the Outlook Object Model, and pass the data to the same component to generate the XML.
This opened up a third option for me. The salespeople had begun working from their homes and accessing the client's systems over a newly configured virtual private network (VPN). But the order entry application didn't perform well over the VPN connection, presumably because of the amount of data being passed from the application server to the desktop client. All that the salespeople needed was the ability to enter orders.
I developed a simple but effective solution: a single form on the corporate intranet where they would enter the order data. The Web server (Microsoft IIS running Active Server Pages) would pull the data from the form and pass it to the XML-generating component. (The VPN and intranet were considered secure enough so that opening up the order entry application without requiring an additional sign on was not a significant security concern.)
I now had three front ends feeding the same XML component, which would upload data to the order entry system. The plan was to build the XML component first, since it was the common denominator, followed by the Excel interface, the Outlook interface, and the ASP form.
The XML component
The XML generator would have an ORDER object representing the order. This object would be responsible for receiving the order data from the front end and writing the XML file. For simplicity's sake, the file path was hard coded, but it could easily be read from a configuration file. The ORDER object would have properties representing each of the elements described in the XML schema above.
It would also encapsulate a collection of LINEITEM objects representing the line item details. The LINEITEM object would have properties representing the corresponding data in the XML schema. The ORDER object would receive the order data, creating LINEITEM objects as needed, create an XML data structure with the order data, and write the structure to an XML file.
The file would be named with the order number, which had to be unique. It would be generated by the front end and be a combination of an alphabetic prefix unique to each front end and a time stamp.
Coding the XML component
I programmed the component in Visual Basic 6, creating a project for an ActiveX DLL and adding classes for the ORDER and LINEITEM objects. Working with the ORDER class first, I added properties for each XML element.
The first element in the XML schema was ORDER ordernum="abcde12345". The ORDER class would have a property called ordernum.
Implementing properties in VB6 was a two-step process. First, I needed to create a private variable to store the property value. Then, the ordernum property would be implemented as a string with a required length of 10 characters.
Private strOrderNum as String
The property would become accessible to users of the class with Property Let and Property Get subroutines. These subroutines tie the private variable to the public property and can also implement the required validity checking; in this case, ensuring the order number was 10 characters.
Users of this class could now get or set the ordernum property of an order with the following lines of code:
Dim objOrder as Order
Set objOrder = new Order
objOrder.OrderNum = "111222333x"
MsgBox objOrder.OrderNum
The ORDER class would also have properties for the following XML elements:
- clientNum: Corresponding to the CLIENT_NUM element
- orderDate: Corresponding to the DATE element
- clientCostCenter: Corresponding to the CLIENT_COST_CENTER element
- clientPurchaseOrder: Corresponding to the CLIENT_PURCHASE_ORDER element
- attn: Corresponding to the ATTN element
- shipTrackNo: Corresponding to the SHIPPING element
- shipCompany: Corresponding to the SHIPPING element, company attribute
- shipAddrLine1: Corresponding to the ADDR_LINE_1 element of the SHIPPING_LOC element
- shipAddrLine2: Corresponding to the ADDR_LINE_2 element of the SHIPPING_LOC element
- shipAddrCity: Corresponding to the ADDR_CITY element of the SHIPPING_LOC element
- shipAddrState: Corresponding to the ADDR_STATE element of the SHIPPING_LOC element
- shipAddrZipCode: Corresponding to the ADDR_ZIP element of the SHIPPING_LOC element
- comments: Corresponding to the COMMENTS element
First, the private variable to store the collections was declared as follows:
Private colLineItems as Collection
Then, a subroutine to add line items was created, which would take a LINEITEM object as an argument and add it to the colLineItems collection.
The LINEITEM class itself was implemented in a similar way to the ORDER class. It would have properties corresponding to the XML elements as follows:
- itemCatalog: Corresponding to the ITEM element, catalog attribute
- itemID: Corresponding to the ITEM element
- description: Corresponding to the DESCRIPTION element
- unitPrice: Corresponding to the UNIT_PRICE element
- quantity: Corresponding to the QUANTITY element
At this point, I have properties defined for all the elements needed to create a complete XML file describing the order. Next time, I'll focus attention on the process of setting up the Excel interface to extract the first provider's order data through the Excel Object Model and pass the data to a component that generates the XML file.
TechRepublic originally published this article on 3 July 2003.