Creating a Merged Shipping and Receiving Client

Duwamish Books Shipping and Receiving, Phase 1

Dale Smith
Microsoft Developer Network

May 1998

Summary: Describes the Shipping and Receiving applications. (8 printed pages) Knowledge of Visual Basic, ADO, and object-oriented design will be helpful. Discusses:

• Data encapsulation
  
  
• Interface design
  
  
• Code samples

Introduction

This article describes the Shipping and Receiving application used in Phase 1 of the Duwamish Books sample application. See Robert Coleridge's introduction to the sample, "Duwamish Books," for specifics concerning the phases. The Shipping and Receiving application is one of a number of desktop applications that together automate some of the routine business processes for a small bookstore.

The Big Picture

The process of adding items to and removing items from inventory makes up the shipping and receiving process. The receiving process is driven by a purchase order and the shipping process is sale driven.

Normally, receiving items into inventory requires a receiving person to remove the packing slip from the package and match it to a previously created purchase order. This purchase order contains the name of the supplier and a list of items ordered. The receiver accounts for each of the items received, logs the actual quantity, and changes the inventory quantity by the quantity received.

The Shipping and Receiving application I've developed for the Duwamish Books sample handles the last few steps automatically. When an order comes in, the receiving person selects the purchase order number from a drop-down list that contains outstanding purchase orders. The form displays the purchase order details, including the supplier and a list of ordered items. From this list, each item received is selected, a quantity entered, and an optional note created. After all of the received items are entered, the receiver clicks the Submit button, which causes the application to automatically log each of the received items' attributes and adjust the inventory accordingly.

In the shipping part of the application, a sale order is selected from a list of sale order numbers. The displayed sale order includes the customer information and a detailed list of the items ordered. As with receiving, each of the items is accounted for, the actual shipping quantity is logged, and the inventory is decreased by the quantity shipped.

Under the Hood: Moving Inventory

Without inventory, a retail sales operation could not function. The same criterion applies to the Shipping and Receiving application. Items must be received into inventory in order to have stock on hand to sell.

The Shipping and Receiving application uses a common database application programming interface (API) that accesses a Microsoft Access database through ActiveX® Data Objects (ADO). Later in this article, I will explain how data is actually handled through encapsulation. For now let me explain the basics of inventory status and historical tracking information as it applies directly to shipping and receiving. For a more complete description of the actual database structure see Steve Kirk's article "Designing a Simple Retail-Oriented Database: Duwamish Database, Phase 1."

The shipping and receiving data process is handled through the Items and InventoryTrack tables, and through references from the Orders, OrderDetails, Sales, and SaleDetails tables. The Items table contains various attributes associated with that item. The InStock field contains the current inventory quantity for an item. As items are received into inventory, that item's quantity is added to the current InStock value, while for shipping the quantity is decreased. In order to maintain historical data as items move in and out of inventory, a record is added to the InventoryTrack table each time an item is received or shipped. A reference to an ordered item is included in the added record that associates an order with an InventoryTrack record. Because of the added complexity of tracking partial receipts or overages, we decided that, for this sample's purposes,  once any quantity of an individual item included in an order is received (or shipped), that item is considered completely received (or shipped). Therefore, for each item included as part of an order, there is only one InventoryTrack record.

Interface Design Concepts

This application incorporates a number of procedures which, when combined, model the typical shipping and receiving process. Because of the similarities between the shipping and receiving processes, I decided to make the user interfaces (UIs) for the shipping and receiving modes similar and to use common procedures. I took a modular approach in the design, creating procedures that would model particular processes, such as ProcessItem. This procedure is used in both shipping and receiving to change the inventory quantity and add a tracking record, thus "processing the item." For data access, I used a common database API that incorporates ADO to interface with the database. For the actual processing of data, rather than using SQL to return a recordset and then work directly on its data, I chose to encapsulate the data and then work on an image of it. (It's much easier to work with objects than to work with raw data in the form of recordsets.) This methodology is more in line with the concept of object-oriented programming. Later in this article I demonstrate the use of data encapsulation as it applies to the process of tracking the movement of inventory.

Navigating the Application

The application is started in either Shipping or Receiving mode by a command-line switch, which is supplied by the Launcher. The Shipping and Receiving application uses a standard Windows interface, including a menu bar and toolbar, which are customized for each mode. Some of the toolbar buttons and the menu items are disabled until they are needed.

Figure 1. The application in shipping mode

The Application Interface

Figure 1 shows the application in shipping mode; the UI for the receiving mode is essentially the same. Tables 1–4 show what choices the user may select from the toolbar, menu bar, and submenus.

Table 1. Toolbar

Table 2. Menu Item = Base Menu

Table 3. Menu Item = File

Table 4. Menu Item = View

Object-Oriented Design and Data Encapsulation

A number of objects and object collections are used to represent data in the database. The object collection represents a database table. The object represents a record in this table and the object's properties represent fields. For example, PurchaseOrders represents a collection of PurchaseOrder objects.

Object Initialization

The Initialize procedure populates many of the collections with data, and it also refreshes the data in those collections. For each object, Initialize calls the GetRecordSet function in the database API, which returns a recordset containing data that pertains to the targeted object. Then each property is assigned the value of the associated field in the current record. Subsequently, each object is added to the collection. This process encompasses the initialization process for each object collection. In addition to the Initialize function, order collections are initialized, when needed, in their respective functions, InitializePurchaseOrders and InitializeSaleOrders. In the code segment that follows, sale order items and their attributes are returned as the oSODetail collection is initialized:

'Get current shippable sale orders.
sSQL = "SELECT Orders.* FROM Orders INNER JOIN Sales ON " & _
"Orders.PKId = Sales.OrderId WHERE (((Orders.Status)='" & scREADY & _
"') AND ((Orders.IsSales)=True) AND ((Sales.Ship)=True))"
If DataAccessAPI.GetRecordSet(scCONNECT, sSQL, oRS) Then
  Set g_oSaleOrders = New SaleOrders
  If oRS.RecordCount > 0 Then
    For iCount = 0 To oRS.RecordCount - 1
      Set oSaleOrder = New SaleOrder
      On Error Resume Next
      With oSaleOrder
        .PKId = oRS!PKId
        .CustomerId = oRS!CustomerId
        .EmployeeID = oRS!EmployeeID
        .OrderDate = oRS!OrderDate
        .PickupDate = oRS!PickupDate
        .Status = oRS!Status
        .SubTotal = oRS!SubTotal
        .ShippingHandling = oRS!ShippingHandling
        .Tax = oRS!Tax
        .Total = oRS!Total
        .IsSales = True
        .ShipToName = oRS!ShipToName
        .ShipToAddress1 = oRS!ShipToAddress1
        .ShipToAddress2 = oRS!ShipToAddress2
        .ShipToCity = oRS!ShipToCity
        .ShipToState = oRS!ShipToState
        .ShipToZipCode = oRS!ShipToZipCode
        .ShipToPhone = oRS!ShipToPhone
      End With
      On Error GoTo 0
      g_oSaleOrders.Add oSaleOrder, FormatPKIdAsKey(oSaleOrder.PKId)
      Set oSaleOrder = Nothing
    oRS.MoveNext
    Next iCount
  End If
  oRS.Close
  Set oRS = Nothing
End If

Order Processing and Inventory Movement

As I mentioned before, the shipping and receiving processes essentially mirror one another. In general, the steps for each process are the same, except that a shipped order should include a packing slip and a shipping label. The Shipping application includes the toolbar buttons and the menu bar items for printing a packing slip and a shipping label, but we leave the implementation of the feature up to you. In the example that follows, I will highlight Shipping, with the understanding that Receiving uses the same procedures and user interface.

Selecting a Sale Order

When a sale order number is selected from the drop-down list, the application calls the displayOrder function, which is common to both the shipping and receiving processes. This procedure contains the processes that display either the selected sale order or the selected purchase order. The displayed sale order includes the customer information and a detailed list containing items ordered but not yet shipped. In the initialization process, the sale order detail collection is populated only with items that are available for shipping. The following code segment illustrates displaying a sale order using the displayOrder function:

Sub displayOrder(Mode As Integer)
  Select Case Mode
    Case scMODE_RCV
.
.
.
    Case scMODE_SHIP
      'Hide receiving mode.
      mskShippedQty = 0
      cmdItemDetail(icMODE_SHIP).Visible = True
      displayCustomer (Val(cmbOrders(icMODE_SHIP)))
      displaySODetails (Val(cmbOrders(icMODE_SHIP)))
      'Show shipping mode.
      frmeCustomer.Visible = True
      lstSODetail.Visible = True
      lblSODetail.Visible = True
      mskShipDate = Date$
      mnuShipLabel.Enabled = True
      mnuPackSlip.Enabled = True
      tlbInventory.Buttons(3).Enabled = True
      tlbInventory.Buttons(4).Enabled = True

Preparing an Item

The sale order contains a list of customer-ordered items. For an item to be submitted, its inventory-related attributes need to be entered. The user has the option of selecting an item from the list of available items and then entering a quantity or dragging the item to the quantity text box. Also, at this time, a note can be typed into the note text box. A group of items can be selected and each of their associated attributes entered. Prior to these items being submitted, these entries are used as values for the properties of the oInventoryTrack object, as illustrated at the beginning of the following code:

Private Sub SubmitItem(Mode As Integer)
Dim oInventoryTrack As New InventoryTrack
Dim lEmployeeID As Long
    
Select Case Mode
  Case icMODE_RCV ' Purchase Order / receiving
.
.
.
  Case icMODE_SHIP ' Sales Order / shipping
    With oInventoryTrack
      .IsSale = True
      .ItemId = lstSODetail.SelectedItem.Text ' selected item
      .TransactionId = lstSODetail.SelectedItem.SubItems(6)  ' SO number
      If GetLoggedinUser(lEmployeeID) Then
        .EmployeeID = lEmployeeID
      End If
      .TransactionDate = mskShipDate
      .Quantity = -Val(lstSODetail.SelectedItem.SubItems(4))
      If lstSODetail.SelectedItem.SubItems(5) = "" Then
        .Notes = " "
      Else
        .Notes = lstSODetail.SelectedItem.SubItems(5)
      End If
    End With
  End Select
  If ProcessItem(oInventoryTrack) Then
  End If
End Sub

Submitting an Item

Once an order is submitted, the SubmitItem function is called for each checked item in the list. After the property values of the oInventoryTrack object are assigned, the ProcessItem function is called. The ProcessItem function that follows changes the inventory quantity and adds the inventory tracking record:

Public Function ProcessItem(oInventoryTrack As InventoryTrack) As Boolean
Dim bBook As Boolean
Dim oItem As Object
Dim lQty As Integer
Dim bReturn As Boolean
Dim sQry As String
    
ProcessItem = False
If GetItem(oInventoryTrack.ItemId, bBook, oItem) Then
  ' oInventoryTrack.Quantity is a positive value for receiving
  ' and a negative value for shipping
  With oInventoryTrack
    lQty = oItem.InStock + .Quantity
    sQry = "UPDATE Items SET Items.InStock = " & lQty & _
    " WHERE (((Items.PKId)=" & .ItemId & "))"
    bReturn = DataAccessAPI.ExecQuery(scCONNECT, sQry)
    sQry = "INSERT INTO InventoryTrack ( ItemId, " & _
    "TransactionId, EmployeeID, IsSale, Quantity, " & _
    "TransactionDate, Notes ) VALUES (" & .ItemId & _
    ", " & .TransactionId & ", " & .EmployeeID & _
    ", " & .IsSale & ", " & .Quantity & ", #" & _
    .TransactionDate & "#, '" & .Notes & "')"
  End With
  bReturn = DataAccessAPI.ExecQuery(scCONNECT, sQry)
  ProcessItem = True
End If
End Function

Conclusion

While this application may not be feature-rich enough for a real-world application, enough functionality has been incorporated into Phase 1 to allow us to demonstrate the techniques used to move from the desktop in the local bookstore to providing the computer resources to move the operation onto the Web. As we move the Shipping and Receiving application through the various phases, the modular approach used in Phase 1 will make it easier to separate the procedures from the main application and place them into distributable components.