Bobby
The 'stock' table is in fact a 'stock movements' table. What you are better
off doing is having something like this structure:
Field Name Type Width Dec
UID Integer 4
LOCATION Character 8
PARTNO Character 16
MOVEDATE Date 8
QUANTITY Numeric 10 2
MOVECODE Numeric 2
The movecode can be a numeric code for the type of movement and reason, it
will give you more flexibility than having a separate column for each type
of movement. Quantity can be + or - depending whether in or out.
Don't bother with scratch totals - the fox is fast enough that if you index
properly the totals can be computed on the fly as needed - just with sql
selects. Much less liable to embarrasing glitches when scratch totals have
got out of kilter ;-)
HTH
============
Brian Abbott
ACA Systems
============
> -----Original Message-----
> From: profox-bounces /AT/ leafe .DO.T com
> [mailto:profox-bounces@leafe.com] On Behalf Of Bobby Khaund
> Sent: Tuesday, December 30, 2003 4:55 PM
> To: Profox
> Subject: inventory stock table
>
> Hi All
> I need some changes to my app which is similar to a inventory
> app. A inventory app has a product master table which records
> the details of the product. Then there is a stock table. I
> want to know the function of a stock table. I had seen a example like
>
> product stock
> --------------------
> stk_prdcode
> stk_location
> stk_qtyreceived
> stk_qtyissued
> stk_qtybalance
>
> When is a entry made to such a table ? Does this table have a
> 1:1 or 1:M relation with the product master.
>
> My client needs a report like
> Prodname OpeningStock Receipts Adjustments Issues Closing Stock
>
> How do you generate such a report. Say today is 30th Dec and
> my clients needs to know the stock position on 1st December.
>
> Any kind of help will do. Right now I am scanning the
> products table and for each product I run a query to find out
> total receipts less issues upto the previous day which is the
> openingstock for the product. Then a sql to find receipts for
> the day and so on.
>
> Thankx a lot.
>
> Bobby
>
> "Walking on water and developing software from a
> specification are easy if both are frozen."
> - Edward V. Berard, "Life-Cycle Approaches"
>
> http://tinyurl.com/5pij
>
>
>
[excessive quoting removed by server]
©2003 Brian Abbott |
