Building an Order Entry and Tracking Application

Duwamish Books Order Entry, Phase 1

Steve Kirk
Microsoft Developer Network

May 1998

Summary: Describes the Order Entry application. (13 printed pages) Knowledge of Visual Basic and ADO will be helpful. Explains:

• Sharing user interfaces with other applications
  
  
• Code samples
  
  
• Pros and cons of app design for migration

Introduction

This article describes the Order Entry application used in Phase 1 of the Duwamish Books sample.

The Order Entry application (orderent.exe) is part of a suite of desktop applications, written in Microsoft® Visual Basic® version 5.0, that automate business processes in a small bookstore. These applications share a common database, as described in "Designing a Simple Retail-Oriented Database: Duwamish Database, Phase 1." The Phase 1 applications are a starting point for what will be a migration, as described in "Duwamish Books," from a two-tier architecture, where each application contains its own presentation, business rule, and data access code, to a multitiered architecture, where these services are distributed across a network.

This application provides order entry and tracking functionality, including related interfaces to manage customers, employees, and suppliers. It shares user interfaces with the Point of Sale application (see "Coordinating Sales and Inventory: Duwamish Books Point of Sale, Phase 1") and the Launcher application, and borrows from the Catalog application (see "Searching the Inventory: Duwamish Catalog Application, Phase 1").

Data Flow

The Order Entry application manages purchase orders and sale orders. A purchase order represents a purchase of items from a supplier and a sale order represents a sale that requires shipping or other processing.

Purchase Order

A purchase order consists of an order entity and a set of order details. It uses the following attributes of the order entity.

Table 1. Purchase Order Attributes

Each order detail uses the following attributes of the OrderDetail entity.

Table 2. OrderDetail Entity

Purchase order lifetime

A purchase order has the following states during its lifetime:

1. ENTERED indicates that the order has been entered but has not yet been submitted to the supplier. The application sets the order status to ENTERED when an order is inserted into the database.
   
   
2. ORDERED indicates that the order has been submitted to the supplier. Because order submittal techniques vary among suppliers, the application merely provides a method to manually promote order status to ORDERED.
   
   
3. RECEIVED indicates that some items from the order have been received and added to inventory. The Shipping and Receiving application promotes a purchase order to RECEIVED.
   
   
4. COMPLETED indicates that all parts of the order have been accounted for. When a purchase order is completed, it leaves the scope of this system.

Sale Order

A sale order represents an order that requires more processing than a simple "customer buys and takes book" sale. This type of sale may be shipped or held for the customer and may require items to be ordered. Sale orders are generated by the Point of Sale application when sale quantity exceeds quantity on hand or when the ship option is selected.

A sale order uses the following attributes of the order entity.

Table 3. Sale Order Attributes

Sale order lifetime

A sale order has the following states during its lifetime:

1. ENTERED indicates that the order has been entered. The Point of Sale application inserts sale orders into the database with the status ENTERED.
   
   
2. READY indicates that the order is ready to be shipped. The Order Entry application promotes orders from ENTERED to READY when sufficient inventory is available.
   
   
3. SHIPPED indicates that parts of the order have been shipped. The Shipping/Receiving application promotes a sale order to SHIPPED status.
   
   
4. WILLCALL indicates that parts of an order are being held for customer pickup. The Shipping/Receiving application makes this promotion.
   
   
5. COMPLETED indicates that all parts of the order have been shipped or are being held for customer pickup.

Application Organization

The Order Entry user interfaces (UIs) are designed to emphasize reuse. The Order interface can represent either a purchase or a sale order. The Actor interface represents entities such as a Customer, Employee, or Supplier that I am referring to as Actors. The Actors interface represents a list of these Actors and, finally, the Orders interface represents a list of either purchase or sale orders.

I used common data classes throughout the application so that whenever my code is manipulating customers or sales it is using objects of class cCustomer or cSale rather than opening database records or maintaining other variable types to represent these entities. When I select an order from the orders list I receive an object of cOrder. These data objects make a clean application that is easy to maintain while leading naturally to a client/server architecture that does not require a constant database connection.

Business rules (such as the process that marks a sale to be shipped or that determines what sort of sales generate immediate inventory transactions) are grouped together so they can be easily changed in one place without having to make changes throughout the application. Business rules update the totals for an order object or determine that a sale exceeds stock and that a shipping order will be generated.

Finally, I use the common data access module (data_api.bas) wherever possible and I'll document where I had to add to it. I extended this application programming interface (API) where I wanted to insert parent/child records and enclose multiple database operations in an ActiveX® Data Object (ADO) transaction.

Freestanding Forms

The Order Entry application is basically the thinnest possible container (orderent.exe) around a set of freestanding form modules. The forms are freestanding in that they have well-defined interfaces and code dependencies and their inner workings are largely hidden. This encapsulation allows them to be freely reused in ways unknown to the original designer whenever an application has need for or needs to work with one of the common application data entities (Customer, Employee, Supplier, Order, Order Detail, or Catalog item).

The services of a form are invoked by assigning the operating mode, presetting any Object properties, and then showing the form. When the user exits the form, resulting data such as a Customer, Employee, Item, Order, or Supplier Object is exposed as a Form property.

User Interfaces

The Order Entry application allows store employees to enter new purchase orders and to track existing purchase and sale orders. Order entry is performed by employees in Phase 1, so little effort is made to restrict access to data, although future phases of the application will have increased security as customers are able to write their own orders from kiosks in the store and over the Internet. Before discussing implementation of the user interfaces, I'll specify them as a suite of objects with properties and methods that accomplish the required data flow. Many of the data properties described below are of classes that wrap the data entities described in "Designing a Simple Retail-Oriented Database."

Table 4. Order Entry User Interfaces

Order Interface

The Order form displays orders and provides an interface to create new purchase orders and to look up existing purchase and sale orders. The interface includes menu and toolbar selections for new and existing orders. The Order form (frmOrder) exposes the following properties and methods.

Table 5. Order Form Properties and Methods

Table 6. Order Form OrderMode = icBASE_MENU

This is the Entry interface. Base Menu mode presents the following choices.

Table 7. Order Form OrderMode = icADD_PURCHASE_ORDER

Add Purchase Order mode is for entering a purchase order. It presents the following choices.

Table 8. Order Form OrderMode = icDISPLAY_PURCHASE_ORDER

Orders Form OrderMode = icDISPLAY_PURCHASE_ORDER

Similar to icDISPLAY_PURCHASE_ORDER.

Orders Interface

The Orders form provides a way to select an existing order from a list. The list of purchase orders may be filtered according to supplier and sale orders may be filtered by customer. The Orders form (frmOrders) exposes the following properties and methods.

Table 9. Orders Form Properties and Methods

Table 10. Orders Form OrdersMode = icPURCHASE_ORDERS

Displays a list of purchase orders filtered by supplier.

Table 11. Orders Form OrdersMode = icSALES_ORDERS

Displays a list of sale orders filtered by customer.

Actors Interface

The Actors form provides a way to select a Customer, Employee, or Supplier from a list or to Add/Edit these entities. The Actors form (frmActors) exposes the following properties and methods.

Table 12. Actors Form Properties and Methods

Table 13. Actors Form ActorsMode = icGET_CUSTOMER, icGET_SUPPLIER or icGET_EMPLOYEE

Actor Interface

The Actor form provides a way to add, display, or edit a Customer, Employee, Publisher, Shipping Address, or Supplier.

The Actor form (frmActor) exposes the following properties and methods.

Table 14. Actor Form Properties and Methods

Table 15. Actor Form ActorMode = icADD_ADDRESS, icADD_CUSTOMER, icADD_EMPLOYEE or icADD_SUPPLIER

Table 16. Actor Form ActorMode icDISPLAY_ADDRESS, icDISPLAY_CUSTOMER, icDISPLAY_EMPLOYEE or icDISPLAY_SUPPLIER

Table 17. Actor form ActorMode = icUPDATE_CUSTOMER, icUPDATE_EMPLOYEE or icUPDATE_SUPPLIER

Using and Implementing the Interfaces

Now, with the data flow and the user interface specified, I'll give a quick code demo showing how well the UI components work together. After I show the components working together, I'll drill down into more detail.

Using the Forms

The following code segment shows how to use the Orders form to select an existing purchase order and to get data objects representing the selected order, the employee who created the purchase order, and the supplier:

' Configure orders form to list purchase orders
frmOrders.OrdersMode = icPURCHASE_ORDERS
Set frmOrders.Order = m_oOrder
' Show orders form as a modal window
frmOrders.Show vbModal
' Get selected order with employee and supplier objects
Set m_oOrder = frmOrders.Order
Set m_oEmployee = frmOrders.Employee
Set m_oSupplier = frmOrders.Supplier

In a similar way, the following code segment shows how to use the Actors form to select a customer:

' Configure actors form to list customers
frmActors.ActorsMode = icGET_CUSTOMER
Set frmActors.Customer = m_oCustomer
' Shows actors form as a modal window
frmActors.Show vbModal
' Get selected customer object
Set m_oCustomer = frmActors.Customer

Now I'll use the services of multiple forms to create a purchase order. The following sequence could be taken from the Order form New Purchase choice but, to show how the services of freestanding forms can be used in ways not anticipated by the original designer, I'll use the new order services directly from a piece of client code.

1. Display the Order form:

   frmOrder.Mode = NewPurchaseOrder
   frmOrder.Show vbModal
   
   

2. User selects Supplier button:

   frmActors.Mode = GetSupplier
   frmActors.Show vbModal
   Set oNewSupplier = frmActors.Supplier
   ' Clear order items if another supplier was previously selected
   If Not oNewSupplier Is Nothing Then
   If Not oSupplier Is Nothing Then
   If oNewSupplier.PKID <> oSuplier.PKId Then
   ' Supplier has changed so clear items in list
   lvwItems.Items.Clear
   Set cItemDetails = New clsItemDetails
   End IF
   End If
   End if
   

3. User selects Search button:

   ' Have search form cache an 'order' of item/quantity pairs.
   frmSearch.SetOrderable True
   ' Clear previous 'order'
   Set frmSearch.Orders = Nothing
   ' Filter catalog items by selected supplier 
   frmSearch.SetSupplierID m_oSupplier.PKId
   ' Show the form modally
   frmSearch.Show vbModal
   ' Add items that were selected to the order details collection
   If CBool(frmSearch.GetOrderCount) Then
       For i = 1 To frmSearch.GetOrderCount
           Set oOrderDet = frmSearch.GetOrder(i)
              ' Use a temporary ID to be replaced by db autonumber
           oOrderDet.PKId = NextDetID
           sKey = OrderCommon.IDToKey(oOrderDet.PKId)
           ' Add to collection
            m_cOrderDetails.Add oOrderDet, sKey
            Set oOrderDet = Nothing
       Next
   End If
   ' Business rules for change in order
   SumOrder
   ' Refresh detail list
   FillListView
   ' Refresh form controls
   UpdateUI
   

4. User selects Submit button:

The following code passes the order to the business rules that validate the order and attempt to insert it into the database. If successful, the interface is returned to Base Menu mode and refreshed. If the order is not complete or does not pass validation, the appropriate error message is announced and control is returned to the user:

' Submit order to database
    If MsgBox(scCONFIRM TO_SUBMIT, vbYesNo) = vbYes Then
' Hand order to business rules governing submit
        If SubmitOrder Then
            MsgBox (scSUBMIT_SUCCESS)
            ' Place form in base menu mode and refresh
            m_iOrderMode = ORDER_MODE.icBASE_MENU
            ' Configure form controls
            ConfigureForm
            ' Refresh detail list
            FillListView
            ' UpdateUI
            UpdateUI
        End If
    End If

The Next Steps

Where will the architecture that I implemented here need revision as the bookstore grows along the scenarios outlined in the "An Introduction to the Duwamish Books Sample" article? As covered in the overview, data access will first be encapsulated into an API that supports all of the transactions that have been identified here. This will make the applications easier to maintain, because all data access code is in one place, and it will provide other benefits such as better data security and scalability. The data access code will need revisions.

First, let's note some of the good points of this design:

• It uses common data access code wherever possible and organizes data access code separate from business rule and presentation code. This will ease the transition to Phase 2 where data access services are provided by a separate component.
  
  
• Even though the common shared code in data_api.bas doesn't handle treating multiple database changes as a single transaction, I have added code that treats the parts of an order insertion as a single transaction. This works well because these transactions are always inserting records rather than updating records that are in use by other applications.
  
  
• The technique of copying data from the database into local data objects in memory and returning to the database only when data for a change has been prepared is database-friendly in that it doesn't hold records open for a long time and cause unnecessary lock contention. This reduces locking problems as the number of users increases.
  
  
• Even the fact that data access code is compiled into the application is made less objectionable from a management perspective because Open Database Connectivity (ODBC) data sources can be configured for each client.

Although the design does have these good points, the migration to Phase 2 will address one design weakness: Changes to data access technology may require that all the applications be modified, recompiled, and reinstalled. By putting data access code in a separate component, data access changes can be made without changing the client application.

Beyond Phase 2, business rules will be broken out so that they can be revised without rebuilding the application and, finally, the user interfaces identified here will be refined so that they work well in a distributed environment and become language-independent.