Stock Level Control

[Keith Hubert]

I used AlphaSports as my test bed.

Here is a little tip to make sure that the Quantity on an Invoice cannot be greater than the amount held in Stock and the level will never be a minus value.

First add a new field to the Invoice_Items table. Say we call it "Qty_in_stock". This will of cause be Numeric.

In field rules make "QUANT_IN_STOCK" calculated, the expression is the LookupN function.

LOOKUPN("F",PRODUCT_ID,"Qty_in_sto","product","Product_Id")

It important to note the Index of "Product_Id" on the field PRODUCT_ID, this syntax was a little confusing for me at first, but as this Index is already build into AlphaSports, it helped.

This is to get the stock level value with that PRODUCT_ID into the Invoice Item line.

Also in the same table, make the field rule for Quantity, with a Validation of, QUANTITY

The other rule is Posting, this is to subtract the value of Quantity from Qty_in_sto

Any questions please email me.

Regards

Keith Hubert

Guild Member
London.

[Bob Houle]

RE: Stock Level Control

Quote: ""Here is a little tip to make sure that the Quantity on an Invoice cannot be greater than the amount held in Stock and the level will never be a minus value.""

Keith....

In the real world of retail sales I've found it necessary to allow quantities to dip into the negative range simply because "crap happens".

Example:

#1 - The person doing the receiving hasn't yet inputed the invoice and the product mysteriously gets put out on the shelf. Try explaining to the customer standing in front of you... that you can't sell it yet, because it hasn't be received! (By the way, after receiving the quantities will be corrected)

#2 - A customer special order arrives, and the customer comes in before it can be received.

#3 - Some retailers want to be able to enter a negative quantity to allow returns or exchanges for another product.

--Bob

[mronck]

RE: Stock Level Control

Hmm.
Maybe as a certified CPA I can add some light to this.
I can understand that Keith was happy to share his findings with the community, after all that is what this forums are for.

BUT.
The fact whether this has any practical value depends on the used stock appreciation and management system.
We can speak of a "Technical" and "Economical" Stock.
Where the technical stock would be what is actually there, the economical stock would be what is there, plus what has already been ordered from the supplier and is yet to receive, minus what has been already sold to customers but has yet to be handed out.

It is obvious, that in the last case the stock could be negative at any given moment.

Then, understanding this principle, whe also have lots of stock in-out systems. There is LIFO (last in first out) or FIFO (first in first out), IRON STOCK (which relates to a number of articles always to must be in stock) and several other systems that influence how issues of goods are influencing stock.

There are dozens of complete books written about this subject if one should care for more info.

Greetings,

Marcel

[Keith Hubert]

RE: Stock Level Control

Thanks Guys for your constructive additions to my Tip.

As you know, with Alpha Databases there are many applications where a developer is asked to produce an app which prevents a user entering a data because of a condition.

Low level Stock could mean anything to anybody, e.g. the number of rooms in an Hotel. Or the number of spaces in a parking lot. At any given moment there will always be a finite number of stock available. How this informaion is used is for the end user to decide.

I just hope this little tip has helped at least one Alpha user.

Regards

Keith Hubert

Guild Member
London.