Multiple Location Inventory Design

[Mike Schwartz]

> On 3/25/10 17:37, Scott Walker wrote:
> > I have an order processing system.  Currently it only handles
> inventory kept
> > in one location.  Now a customer wants to start keeping inventory at
> > multiple locations.  So the same part# could be in the inventory at
> the main
> > office and also at several branch offices.  I'm just starting to
> think of
> > the design of this.  Should I have a separate record in the inventory
> file
> > for each part#/location combination?  Should I use a qualified file
> for each
> > of the inventory of each location? (I don't use qualified files for
> anything
[Snip]
> 
> A detail level file that contains itemID, location, qty, vendor,
> costing
> and date should be kept - again for purposes of costing and FIFO/LIFO.
> Additionally you can then implement a stock locating system for
> efficient picking and correct allocation under the FIFO or LIFO logic.
> This allows you to direct material handlers for picking items for
> invoicing or stacking items from orders.
[Snip] 
> Craig Tooker

     Craig, Del Neroni and others have already given you some great advice.
The inventory control programs I've written are probably the most complex
filePro structures I've designed, although the "factored vendors" and area
calculation programs for the flooring industry that I wrote might possibly
be a little bit more complex, if I ever took the time to count up the files.


     It took a multiple filePro file approach to handle all of the inventory
programs I've written. To answer one part of your question that I haven't
seen anybody else tackle, "YES! Qualifier files can help a LOT."

     However, adding qualifiers to any level of your inventory files adds
only one more sub-level to your system, and as Del mentioned, you will
probably need several levels of header/detail files to complete your system.

     (It would be great if there were multiple levels of qualifier files to
make it easier to "drill down" into the databases more deeply.  There are
other database programs that do drill-downs nicely, but multiple browse
windows within filePro using the DROP command can accomplish the same
thing.) 

     For inventory systems, you really have to do a lot of questioning, head
scratching and note taking BEFORE you layout any files.  No two inventory
systems I've written are even close to one another.

     One thing that has always worked for me is a "teach me your job"
scenario.  I get right into the business and have a sales person train me to
be a salesman. I need to know exactly how they go about selling and
reordering items, and I don't mean just a quick 10-minute tour of the
warehouse.  If the boss asks why I need to "train" for a day or two, I say.
"I need to understand every detail of how inventory moves throughout the
place so that I could do any job that involves inventory, just short of
learning to run a forklift."  If necessary, I offer to do this day or two of
training for FREE.
.  

     In one instance I found out that it was really the special order items
that were losing all the money, so I concentrated on writing the special
order files so that special order items were handled as efficiently as
possible.  

     In another instance, I found that the inventory control problems were
not due to having multiple locations, but rather from selling both single
units and case lots.  I didn't have to change the file structure to fix this
problem, but I had to adjust the quantities fields to allow selling a half
or a twelfth of a case.

     So the key here is to do lots of thinking (and asking questions on the
list) BEFORE you write any code.
 
     One last tip:  You need to find that one key employee who is really
interested in what you are doing and is willing to help make suggestions and
test things with you.  I mention this because a lot of the employees still
see computers as job threats, and they will do anything they can to derail
the project.

Mike Schwartz