I have a table used to keep stock o 1

[fisol]

I have a table where I keep stock of my "Scheduled drug".
The table contains fields as follow:
"SerialNumber"(Numeric and autoincremental)
"Date" (date)
"Descriptions"(Alphanumeric)
"AmountIn"(Numeric)
"AmountOut"(Numeric)
"Balance"(Numeric)

My data entry form contains a simple "table which shows all fields" and the necessary push buttons for records handling.
I wish to have a "running balance" shown on the form as I enter new records. How do I go about doing it?
Example:
SN Date Description AmtIn AmtOut Balance
==================================================
100 8/1/02 A(new stock) 500 0 500
101 8/1/02 B 0 50 450
102 8/2/02 C 0 25 425
103 8/4/02 D 0 400 25
104 8/6/02 E 0 25 0
105 8/8/02 A(new stock) 400 0 400
.
.
==================================================
It was easy doing it using Excel..

 

[footpad]

Fisol,

It looks like you've laid the ground work pretty well; however, this is really a job for two tables, not one.

You need one table to manage the actual stock and another to manage the transactions into and out of stock, much like the ORDERS and LINEITEM tables provided with Paradox's samples.

(Now, a full explanation may take a little longer that these boards will take, so I'll try to be brief. If I don't get everything covered adequately, please feel free to followup via email at the email address listed in my profile.)

In any event, start by creating two tables:

Stock.db
1.  SerialNumber   N*
2.  Date           D
3.  Descriptions   A??
4.  CurrentBalance N

StockXacts.db
1.  SerialNumber   N*
2.  XActDate       @*  Default: NOW
3.  AmtIn          N
4.  AmtOut         N
5.  XactDesc       A??  ; if you like.

In this example, the Stock.db table manages each item in your catalog and their overall totals. The StockXActs.db table handles only transactions into and out of stock.

(This is the type of thing people talk about when they say each table in a database should only handle one thing.)

Now, one might quibble about whether or not there should be two fields for AmtIn and AmtOut. I laid it out this way because I know some accountants prefer to see transactions laid out in separate fields. It makes a little more work as far as Paradox is concerned, but it makes the final reports eaiser to write.

On the other hand, it's pretty easy to create appropriate accounting-style reports froma single field. So, it depends on where you want to do the work. In any event, we'll assume this layout for now.

continued in next message...

 

[footpad]

Now, you'll need to design your form so that the transactions are laid out in a table frame. Also, make sure the overall total from the Stock.db table has been placed on the form.

Link Stock.db to StockXacts.db so that Stock is the master table and you have a 1-M link to the transactions table. This qwill let you enter multiple transactions for each item you carry.

Next, you may have noticed that we didn't add a running total field in the transaction table. It's not strictly necessary to store the running total because it can be quickly recalculated at runtime. That's the database designer talking. If your accountant has a louder voice, then feel free to add one to the actual table.

Assuming we've left it out, we will need to generate that value as tranasactions are added. To do that, we need to add a calculated field to the transaction table frame. To do that:

1. Make certain the form is open in a Design window.
2. Select the transaction tableframe by clicking it once with the mouse. You should see the handles appear at appropriate points long the border.
3. Carefully click the table frame one more time. When you do this, the handles disappear and the mouse pointer turns into an arrow pointing striaght up.
4. Press the Insert key once. Paradox adds a new column to the table frame.
5. Now, either press the Esc key or click a blank area of the form to exit what I call "table frame editing" mode.

At this point, you have an extra field in the table frame, one you can define as a calculated field.

Uh-oh. My three year old daughter just broke something and it sounded like glass. I need to take care if it and will finish posting this as soon as I can, though it may take some time. I sincerely apologize for leaving you in the lurch like this, but will be bacn as soon as I can.

-- Lance

 

[Lewy]

Footpad,

How about the finish to this posting as I reckon I can adopt some of this code to my optical application!! I have taken a rest for a while but will soon be back adding more functions as I go - I hope.

All the best.

Lewy

 

[footpad]

Sorry for the delay in finishing this thread off, folks. Things took longer with my daughter that I expected, and, well...y'know. Real life happened.

In any event, I've take a little time to think this through a little more. To pull this off correctly, you'll either need to track the original total in the master (Stock) table or the updated (running) total in each transaction.

At this point, I think it's best to move the total items in stock out of the master table and then add a transaction to seed the initial inventory levels.

That way, each transaction updates the running total by checking to see if there's a previous record and, if so, adjusting the that record's running total with the changes for the current transaction.

I would do this using a small amount of ObjectPAL. First, add RunningTotal to the detail table and then add a column for that field to the table frame. Let's assume you named the field RunningTotal.

In the changeValue event of each field that affects the running total (e.g. AmtIn, and AmtOut), add:

   RunningTotal.postAction( userAction + 1 )

Now, in the action event of the RunningTotal field object, add something along these lines:

var
   numRunTotal  Number
   tc           TCursor
endVar

   If eventInfo.id() = userAction + 1 then

      ; get previous total
      if container.recNo > 1 then
         tc.attach( self )
         tc.moveToRecord( container.RecNo - 1 )
         numRunTotal = tc."RunningTotal"
         tc.close()
      else
         numRunTotal = 0
      endIf
      
      numRunTotal = ( numRunTotal + AmtIn.Value ) -
                    AmtOut.Value

      self.Value = numRunTotal
   endIf

Let me explain this by working backward: When a custom action is triggered, we see if this is the first transaction. If not, then we use a TCursor to make a copy of the transactions for this stock item, jump to the previously saved RunningTotal and grab that value; otherwise, we assume this is the first transaction for this item and set the stock level to 0.

Then we simply adjust the previous value by the current transaction.

Hope this helps...

-- Lance

P.S. One of the main reasons this sort of thing is easier in Excel than it is in Paradox (or most other databases) is that spreadsheets often think in terms of individual cell values while databases typically think in terms of row values. That is, you need an entire record before you can do anything with it.

It would be like having a spreadsheet that only recalculated with you moved to the next row. Since that's almost exactly what happens in Paradox, you need to add a little bit of code here and there to get the results you need.