Inventory Control

[yafreax]

It's times like these, i feel inadaquate (like when i can't spell too . . .)

Anyway, this seems like it would have to have a simple solution, but i just don't know it.

I have to develop a inventory control that keeps track of different companies shells (printing term). It obviously needs to subtract and add from the number of Quantity on Hand each time some are removed or added.

I was thinking initially 3 DB's. Main with all company info and Current QOH. Then a Removal DB and an Addition DB. IS THIS THE BEST WAY?:? and if it is, i have already tried it, but i can't figure a calculation or script that would correctly update the Current QOH?!?!?!

Is it just me, or is this hard. Sounds like it should be easy, but hey, i'm not very bright.

Thanks

Jeremy

[dtrots]

I am currently setting something similiar up. I am fairly new to FM so here is an idea maybe you an work from

One database with a sript that will add and subtrat your quantity number,

example- you just invoiced a customer and sold him something from your inventory. After the invoice prints a script subtracts 1 from the quantity on hand from your inventory database.

Just a thought maybe you can work from

Dave

[yafreax]

well, we're talking paper here. So i have different quantities coming and going, plus we want to keep track of when it's coming and going. If we didn't need to keep track of when it came and went, it'd be a sinch.

so i'm thinking i HAVE to have different databases to keep track of the coming and going. But if that is the case, i don't know how to keep an accurate count.

If you have any ideas, keep 'em coming, i'm working around a few, and if i come up with something, i'll let you guys know.

jeremy

[LiveOak]

You explained that a "shell" is a printing term, but you didn't explain what it is!

There is no one way to write an inventory control system. These systems can be greater in complexity than accounting systems.

For instance, systems that allow steel bar stock to be bought in lenghts of 20' but sold by the inch require special ways of handling quantities. Two 1' lengths won't fill an order for a 2' length, etc.

Most good systems do not directly credit or debit inventory quantity. When this is done there is not way to track an incorrect transaction. Most comprehensive systems keep track of every credit or debit transactions to inventory. This way the current inventory quantity can always be arrived at by processing through all transactions for an item.

You can, using relationships, calculate the sum of all the credit and debit transactions on the fly. The problem with this approach is it is VERY slow for a system with many inventory items. It is common to use a working total for daily transactions and then update the working number each night when time is available.

-bd

[This message has been edited by LiveOak (edited January 08, 2001).]

[BobWeaver]

If your inventory db has an InitialQty field to record the starting quantity for each product, and your additions db and removals db's have QtyAdded and QtyRemoved, you can calculate QtyOnHand as:

QtyOnHand=InitialQty +

Sum(Additions::QtyAdded) -

Sum(Removals::QtyRemoved)

Then on a periodic basis, you can update the InitialQty and inactivate (delete?) the related removals/additions records.

[yafreax]

thanks, live oak, that helps me understand what i'm looking at a little more.

BTW a shell is simply a pre-printed stock paper. For example, if we do all the business cards for a given company, then we would print a large quantity of "Shells" that includes all the static items, logo, slogan, etc. So that when we get orders, all we have to print is the personal and varying info. So for all practical purposes in this case, it's just paper. . .

Well, like i said, i atleast see what i need to work with. If i come across any questions you know i'll post them.

jeremy

Bob, just read you post, (posted while i was replying i guess) if i get this to work, then that's what i need, thanks.

[This message has been edited by yafreax (edited January 08, 2001).]

[BobWeaver]

As LiveOak says, a lot of records will slow this down, so you may want to provide a regular method of updating the initial inventory (maybe daily) and then somehow inactivate the related transaction records (anything older than than the last inventory update time/date). This way you can still trace shipping/receiving, but limit the number of transactions that you have to crunch through to get current qty on hand.

[LiveOak]

Just to make sure the point doesn't get lost. Doing the calculation Bob suggests can lead to VERY slow operation in databases with list views showing many inventory item quantities based upon a lot of transactions. -bd

[This message has been edited by LiveOak (edited January 08, 2001).]