How do I design this form to work most of the time, but allow the user to access additional functionality when needed?

Question

This is a rough draft of a Microsoft Access form contained in an irrigation materials rental system. The business owner rents irrigation pipes to local farms. (I hid the company name from the form). (Farms rent pipe for $5/pipe/month instead of buying them outright for $60 each...typically a farm only needs irrigation pipe for a few months out of the year.)

This system will receive requests for new irrigation pipe from local farms, email a PDF'd order to the delivery truck, who delivers the pipe in multiple loads. Each load (and its associated driver) is tracked in the system for later analysis. At the end of the rental period, the entire order is typically picked up (often in multiple truckloads), and the contract is closed. This simple scenario happens 80% of the time. The other 20%, however, involve multiple requests for new pipe during the length of the contract period, as well as requests for extensions for a portion of the existing rented equipment.

Here's how the form will work as its currently designed:

• Customer calls in to request new pipe and fittings
• order taker creates a new Order
• Each line item requested is entered in the top Order Detail box as a Delivery request.
• Note that the button on the top right of the Order Detail box is a triple-state toggle button that switches the view from All Deliveries to All Pickups to All Requests
• when a delivery is made, the driver fills out a manual Delivery Ticket that is emailed to the database operator.
• The database operator then presses the new Delivery button under Deliveries & Pickups
• The user then adds items to that delivery by clicking on a line item in the Order Detail, entering a quantity (ignore the From and To fields for now), and pressing [Post]. Line items are entered one at a time in a delivery.

for Pickup, the customer calls back (or is contacted by the company) and schedules a pickup. Each line item to be picked up is entered as a separate line item on the Order Detail as a Pickup request. The same process used to record a Delivery Ticket in the system is used to record a Pickup Ticket.

The box on the right lists a running total of all line items at a site, and is updated real-time. The list can grow quite lengthy if you consider all the fittings, pumps, sprinkler attachments, sizes of pipe, etc.

Some obvious improvements would be to:

• add a checkbox system in the Order Detail box that would dump multiple line items into a delivery/pickup all at once
• make it more obvious that you're creating a Delivery request vs Pickup request (change the title of the section, hide the "type" field, for example, depending on what is being displayed

But, here's what I am wrapped around the axle about: My client understands this form, but his first comment was, '80-90% of my orders are simple deliveries and then complete pickups at the end of the term. Why are you making me enter the same information twice on this form (once for the order request, and once for the delivery)' Great point. I'm not sure how to simplify this, but also accommodate for the 10-20% of cases where multiple requests come in during an order period, and multiple pickup requests are also received during this period. (Their customer calls up in January and says "I need 1000 pipes." Then, he calls back in February and says "send me 250 more." Then, in May, they call in and say, "Can you pick up 600 of the 3" pipes, and deliver 250 more of the 2" pipes?" Finally, in June, they call back and say "I'm done with everything, can you come pick it all up?"). Note also that the original order request and the actual delivery are two separate events that need to be tracked separately.

Thanks!

Answer 1

This is a quite common problem in Enterprise Resource Planning systems (ERP) where you break the convention of deliveries by order lines. Everything that went out by an order line has to come back by order line – which is true if you made the design of the system that way.

There are other techniques to design this, where one is Electronic Data Interchange (EDI). Instead of pushing products to your customers (usually by order line) – they pull the products from you. You update the record accordingly saying that these products are now located elsewhere (at your customers) and can’t be moved to another customer. The systems blocks their availability if the warehouse is not your location.

When customers return products to your warehouse, they are made available again since products are now located at your warehouse. What you need to do is to make several warehouses or store locations, where one is yours and others are your customers. Your job is to keep track of transactions between warehouses, and at the end of the month (or whatever period you chose) bill the customers for what products they currently have in their “warehouse”.

One line – one transaction. No more handling of order lines on a purchase order.

Answer 2

Thanks everyone for posting. Sorry it took so long to get back to this. After considering everyone's comments, I came up with the following solution. I did need to keep track of delivery detail, so I couldn't use Benny's excellent post above. I decided to clean up the form, and make it easier to copy and paste between the various sections. It works like so:

1) User enters a delivery request, 1 line item at a time, and then clicks the Delivery Request button up top to PDF a delivery request form for the driver

2) When the delivery occurs, the user creates a new delivery (lower left), and then puts a check mark in the items that were delivered, and then presses the [Deliver Selected] button

3) When the customer calls to have the items picked up, the user checks off the desired items and presses [Schedule Pickup]. This changes the entire form to "Pickup Mode" (the user can toggle this mode using the toggle buttons in the upper right)

4) Pickups are recorded just like Deliveries in the lower section of the screen

5) A running total is listed in the Order Summary section.

I'd love to get your thoughts on this (hopefully) final version.

Pickup mode looks like this: