Form vs. function

[Guest]

By way of introduction, I'm Neal Murphy, co-founder and CIO of the Diesel Hot Rod Association (dhraonline.com). As CIO, everything related to information is my job. This includes member services, the website, bookkeeping, merchandise sales and shipping, printing and publishing. I'm swamped trying to keep up; unfortunately this means I don't have the time to thoroughly search these fora to see if anyone else has broached this topic.

 

Last year (our first), I kept track of member and merchandise purchases via e-mail, but this system broke down near the end of the year. So I looked for an open-source solution, and found OSC. We are still a very small company, and can't afford to keep merchandise in stock. So I allow orders to collect for a week or so, aggregate them and have vendors fill them. Some vendors will drop-ship for us, others bulkl-ship to me and I handle the sctual shipping.

 

Thus far OSC has worked pretty well this year, at least on the purchasing side. [There was a glitch in that a number of orders were 'lost', but I found them through an audit of PayPal receipts and requested the customers re-enter their orders; I think I've solved it by changing a PayPal setting.] The management side of OSC leaves a lot to be desired. Pondering the situation, it occurred to me that maybe too much attention was paid to form, and not enough to function.

 

There are a few features that would make my job far easier. First, a means of bulk-adding products and attributes would be real useful. It is far easier to edit a text file containing all the desired attributes for a product and then 'importing' that data into the database. The existing form has too many levels and is too cumbersome to use on a 'larger' scale; that said, it does have its place.

 

Second, it would be *really* useful if there was a screen that contains a form for each order. This screen would let me scroll down to the order I need to modify, modify a status or price, and click that order's update button.

 

Related to this, there should also be a bit-map status field so I can keep track of which components of an order I have prepared and/or shipped. For example, members receive an ID card, rule book, decals, and a shirt or ballcap. The status of each of these ought to be tracked. I was thinking two bit-map fields would work: current_status and required_status. An automated script (outside of OSC) could be written to set the required_status bits. Then as I have prepared, e.g., a batch of ID cards, I could use the above-mentioned forms screen to update those bits. Perhaps an 8- or 16-bit status field with each ordered product would do the trick. The bit meanings and usage would be external to OSC, but the storage would be there by default.

 

Also, when an order is paid via PayPal or CC, its status should be set to "Paid". If paid via check/money order, the status should be set to 'Payment Pending'.

 

Finally, by default there should be a reasonably rich set of order status codes: Cancelled, Payment Pending, Paid, Member Created, Processing, Ordered, Shipped, Auditting, Audit Complete, Historical. I'm sure others can add to this list. (Historical would mean an order from a prior time period that has long since been completed.)

 

With these in place, it would be far easier to write the scripts I need to produce PDFs containing supplier build lists and order packing lists. Trying to track what I have and have not handled, order-wise, is almost truly cumbersome with the existing OSC.

 

In the next week or so, I will be adding some of these features to the version of OSC I'm using (osCommerce 2.2-MS2), as I need to get our on-line store functioning smoothly. I'll also be writing a bunch of PHP code to cull pending orders from the database and create the PDFs on the fly, so I can get outstanding orders submitted to suppliers. I actually have some written already, but I need to be able to have those scripts update the ordered product status - I don't want to be filling any one order more than once.

 

So has anyone else pondered these suggestions? Would anyone be interested in the PHP code I develop to automate ordering processing? Bear in mind, given my time contraints, the code I develop is almost purely functional; it won't be pretty, but it'll work. And I try to include good comments in all my code - I've found I can no longer remember what I wrote, or why, even weeks after I write it, so I comment all code.

 

I will try to follow this thread, but feel free to email me if I seem to disappear. (cio at-sign dhraonline.com).

 

Thanks for wading through this!

Neal

[AXM]

Fest3er,

 

Yeah the admin side is by no means perfect and has its flaws. I am too going to trying to find ways to designing the admin side to be more functional...but it'll have to wait until i'm full satisfied with the catalog/ordering side. I have to get the orders rolling in first before i'll have a real problem with the admin :) .

 

My goal is separate orders into three segments: Fullfilled, pending, and backorder. Items when ordered through the catalog are added to the database and each item is labeled as pending. I would then be able to create a purchase order distributing the various pending items to different vendors.

 

Its going to get complicated because you don't want to split orders too much between vendors, certain vendors have only certain items, some vendors will have better pricing on certain quantities, some charge drop-shipping fees if the order total is under $XXX dollars, some vendors might not have a particular item in stock, etc.

 

So i'm still toying with this in my head. So excuse me if my thoughts get wandering.

 

On the admin side, once you distribute the orders to the various vendors it will e-mail (or fax) them a purchase order and drop-ship instructions.

 

Once the order has been fulfilled on their side they will log into a speciallly prepared php page on the website, enter their name and password and orderID#, and update the order themselves. Indicating the parts shipped, their quantities, and tracking number. This page can also be used to confirm the details on the e-mail/fax so there is no confusion.

 

If the order had 3 widgets and the vendor A updates in the system that it shipped 2, then you know you have 1 widget that Mr. John Doe is still owed. The 1 widget remaining will be labeled somehow as "unfulfilled". Knowing that vendor A only could send 2 you can either forward that remaining unfulfilled widget order to another vendor or place it in a backorder status.

 

I just wanted a way to keep track of not only orders but each individual item content of the order. And the mess I typed above is what my intentions of doing. Whether I can make it work on my own, or someone is going to come to my rescue...only time will tell.

[Guest]

pretty much everything you talk about is in a contribution one way or another, almost no two business' are alike, in that one cio may do it one way, another may do it another way. there is a way which one can take each order automatically, and based upon the product, send an email with a purchase order number with the drop ship info. or you can print out daily, a report which will list the product totals by model # and to the side, it will list the order number tied into the product. there also is another one which will tie tie in with quickbooks accounting, you can generate lots of things in that.

you can use access to perfo9rm more functionality you want also.

it is all what you make of it or what you want to do with it. i have helped many people put things together, to make it easier for them to do what they really want the system to do.

you talk about default settings for order status, there are four in there and all you have to do is add more, for what you want them to be.

this is not an accounting system, this is a sales tool.

[Guest]

...

this is not an accounting system, this is a sales tool.

I agree that no two businesses are alike. However, any merchandise sales operation needs certain basic order states. I've chosen Payment Pending, PayPal Pending, Paid, Processing, Delivering, Complete, Refunding, Cancelled. And I may re-think the PayPal Pending, in case I add more payment methods later.

 

For OSC to be a useful tool, it should be set up to be most useful out of the box. One does not want to ship a product before payment has been received, so there has to be a differentiation between 'waiting for payment' and 'paid in full'. Once the order has been paid, it can then be processed: if there is insufficient stock to fill the order, the order should move to the Processing state. In the case of memberships, processing would indicate that ID cards are being made, membership data are being copied into the member database, et al. Once the order can be filled, its state can be changed to Delivering (or Shipping). Parts are gathered, boxed, labelled and shipped. Once everything has been shipped, the order status moves to Complete.

 

If, after paying, the customer changes his mind for one reason or another, the order status should become 'Refunding'. Once the refund has been completed (products returned if necessary and payment refunded), the order status becomes 'Cancelled'.

 

I would think Payment Pending, Paid, Processing, Delivering/Shipping, Complete, Refunding and Cancelled should be the minimal, default set of order states in OSC.

 

Yes, OSC is a sales tool. But it must also be an accounting tool. Its purpose is to account for orders, payments, goods and fulfillment. And to properly account for the goods and cash, the order and merchandise/service states must be properly and well tracked. Once OSC does this, it will be a better sales tool.

 

Fest3er

[Guest]

Fest3er,

 

Yeah the admin side is by no means perfect and has its flaws. ...

 

So i'm still toying with this in my head. So excuse me if my thoughts get wandering.

 

On the admin side, once you distribute the orders to the various vendors it will e-mail (or fax) them a purchase order and drop-ship instructions.

 

...

 

I just wanted a way to keep track of not only orders but each individual item content of the order. And the mess I typed above is what my intentions of doing. Whether I can make it work on my own, or someone is going to come to my rescue...only time will tell.

Your thoughts haven't wandered much at all.

 

In your case, handling backordered items may well require adding a 'quantity shipped' field. Until the number shipped equals the number ordered, the status of that order product cannot be 'Complete', and thus the order cannot be 'Complete'. That's why I suggested possibly adding a multi-purpose extra field in the order_products table. Perhaps it could be used as a 'shipped counter', which could handle your problem. Or it could be a value that is defined outside OSC. For instance, when someone purchases a DHRA membership, I have a number of things to do before that membership is 'complete'. I have to add him to the member database, make his ID card, get his rulebook and decals and get his shirt or ballcap. So in my case, I could use that field as a bitmap, where I'd need 5 bits to track processing status, and 5 bits to indicate which bits need to be set before that 'product' can be considered as 'shipped'.

 

Hmmm. I think what I want is a database external to OSC. And it could well be what you want, too.

 

Perhaps, for my needs, I should have a tool that finds all Paid orders and copies relevant order item data into another database; it would also set my required_status bitmaps in that other DB. As the order is processed (picked and packed?) the items' status is updated. This process is completely separate from tracking purchases and payments. It can be looked at as "the customer has handed me his shopping list; now I need to get everything together for him." Once everything on the list has been checked off, the order can then be shipped (or delivered to the customer). So I need another tool that finds all of my items where the item status equals the required status, and updates the 'quantity shipped' field for that item in the OSC orders_products table; the last thing this tool does is go through the orders_products table and changes the order status to Delivering for each order that has all of its items ready for shipping.

 

I already have a few PHP scripts that pull ordered items out of the DB and produce 'build lists' and packing/shipping slips for the supplier; they produce PDFs on the fly. I just need to determine *where* to track this status info (the fact that I have built the lists). I believe I'm going to need another table that relates back to the orders and orders_products tables.

 

OSC definitely needs more useful admin interfaces, which I's'll be pondering for a while. It is pretty good at putting order info *into* the database. Getting that info *out* in a usable format still needs work.

 

OSC should do one thing well: reliably store incoming orders, payment details and order status and allow admins to very easily keep order status up-to-date. Tracking order processing details belongs in a separate database and tool system, yet one that is interconnected with OSC.

 

Fest3er