Hey Brian,
Interesting approach, does the stock table then become something of a
transaction table accounting for various "moves" of the item via translation
of the "move code"? FWIW I used to use requirements or requistion records
for that purpose. (i;e Order 123 requires 3ea., 3 pulled and issued) that
kind of thing....
>From: "Brian Abbott" <brian AT abbott D.O.T plus.com>
>Reply-To: ProFox Email List <profox@leafe.com>
>To: "'ProFox Email List'" <profox AT leafe D.O.T com>
>Subject: RE: inventory stock table
>Date: Tue, 30 Dec 2003 17:50:20 -0000
>
>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@leafe.com
> > [mailto:profox-bounces AT leafe D.O.T 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 Desmond Lloyd |
