Formula Help

[CST]

I need to write a formula to do the following:-

Field 1 = Qty in Stock
Field 2 = Qty Branch Ordered.

I need the qty in the Qty in Stock field to go down when a branch orders the stock from Qty Branch Ordered.

Hope the above makes sense, I am new to this.

Thanks

Catherine

 

[suesloan]

The "simple" answer is that you can write a macro that runs when the Qty Branch Ordered is entered, and use a SET command to change the Qty in Stock. Like this:

SET Qty in Stock = Qty in Stock - Qty Branch Ordered

The problem you will find is that someone enters 100 and meant to enter 10, and your macro is likely to subtract the 100 and then subtract the 10 making a mess of your Qty in Stock.

A better method is to have the Qty in Stock be dynamically calculated for you. Instead of having a field "Qty in Stock" you could have a database to contain the "stock received" records. Adding up (using a SSum()in a calculated field) all the amounts in this new database gives you the Total Received. Then add up all the Qty Branch Ordered and that is the Total Ordered. Then your Qty In Stock = Total Received - Total Ordered, and the error I described above will not occur.

Note that on a form view, the Total Received should have a summary option of "All records in the received database" and Total Ordered would have "All records in the ordered database".

This is how professional order entry systems, like mine, work!

Sue Sloan

http://www.odpro.com

 

[CST]

Dear Sue,

Thanks for the advice. It's been a great help.

Catherine

 

[CST]

Dear Sue,

I was wondering if you could please answer one more question for me. I have tried your first option for deducting the stock by creating the macro you suggested but instead of deducting the stock, it creates a new record which ends up - the figure the branch has ordered. The original stock stays the same. How do I get the macro to find the correct stock figure to deduct. Is there a formula where it can marry up the item name if it equals the same??

Thanks

Catherine