home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Monster Media 1993 #2
/
Image.iso
/
database
/
p4w_all.zip
/
TI1502.ASC
< prev
Wrap
Text File
|
1993-05-12
|
11KB
|
397 lines
PRODUCT : Paradox for Windows NUMBER : 1502
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 1/6
TITLE : Performing a Calculation Based on a Calculated Field
Intended Audience:
ObjectPAL programmers
Prerequisites:
Basic knowledge of ObjectPAL data structures and control
structures. How to enter ObjectPAL code in a built-in method and
a custom method. How to create a calculated field.
Purpose of the TI:
This Technical Information Sheet describes how to perform a
calculation that is based on a calculated field a method to do
this.
It is sometimes desirable to perform a calculation that is based
on a calculated field. For example, suppose you have a Selling
Price and a Quantity field in your detail table, you created a
calculated field in your detail table that calculates Selling
Price times Quantity for every record in the detail table, and
now you want to create a calculated field that calculates the sum
of Selling Price times Quantity. You will find that you cannot
accomplish this task with a calculated field. This Technical
Information Sheet describes two techniques achieve this result.
EXAMPLE 1
The first, and simplest, way to do this is to create a field in
your detail table called Total, and then create changeValue
methods on the Quantity and the Selling Price fields which place
the multiplication of the two fields in your Total field. You
can then create a calculated field which summarizes your Total
field. A good explanation of how to create the changeValue
methods on the Selling Price and Quantity Fields may be found in
Chapter 7 of the Learning ObjectPAL manual in the section titled
"Supplying values".
EXAMPLE 2
The second technique is much more complex and the remainder of
this Technical Information Sheet is dedicated to it. The primary
advantage of this technique is that it does not require you to
PRODUCT : Paradox for Windows NUMBER : 1502
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 2/6
TITLE : Performing a Calculation Based on a Calculated Field
store the total for each record in your table. The trade-off is
complex ObjectPAL code to achieve the result.
This example is based on using the Orders and Lineitem tables
that are installed in your sample directory with Paradox for
Windows. The quantity field in the Lineitem table is named
"Qty", so the remainder of references to the quantity field will
refer to Qty.
1. Create a form and link the Orders table to the Lineitem
table to follow this example.
2. Create a variable in the var window of the page object of
your form. This variable will hold the difference between
values entered in the current record and the values stored
in the current record. The code in your var window should
look like the following:
Var
num number
endVar
3. Next, create a changeValue method on the Qty and Selling
Price fields. The end result of this method is to
calculate the difference between the current values
entered into the Qty and Selling Price fields, and the
values stored in the table. This difference is stored in
the variable you've already created in the var window of
the page.
Here's the changeValue method for the Selling Price field.
method changeValue(var eventInfo ValueEvent)
var
ar DynArray[] anytype
tc TCursor
endVar
;This method gets called before the calculated
;field. The purpose of this method is to calculate
;the difference between the values in the table
;and the current values.
PRODUCT : Paradox for Windows NUMBER : 1502
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 3/6
TITLE : Performing a Calculation Based on a Calculated Field
doDefault
tc.attach(self)
;TCursor is used instead of a UIObject because
;TCursor.copyToArray can use a DynArray, and a
;DynArray is easier to use.
tc.copyToArray(ar)
;CopyToArray copies the unposted records to the
;array.
tc.close() ;close the TCursor
;if ar.size()>0 then
if not self.recordStatus("new") then
num=(self.value*Qty)-(ar["Selling Price"]*
ar["Qty"])
;This means an existing record is being modified.
;This calculation calculates the difference
;between the posted and unposted values.
else
num=self.value*Qty
;This means a new record
endIf
endmethod
NOTE: The semicolon ";" indicates that a comment follows.
Here's the changeValue method for the Qty field. It is
similar to the changeValue method for the Selling Price
field.
method changeValue(var eventInfo ValueEvent)
var
ar DynArray[] anytype
tc TCursor
endVar
PRODUCT : Paradox for Windows NUMBER : 1502
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 4/6
TITLE : Performing a Calculation Based on a Calculated Field
;See the changeValue method of the Selling Price
;field for comments on this code.
doDefault
tc.attach(self)
tc.copyToArray(ar)
tc.close()
;if ar.size()>0 then
if not self.recordStatus("new") then
num=(self.value*Selling_Price)-
(ar["Selling Price"]*ar["Qty"])
else
num=self.value*Selling_Price
endIf
endmethod
4. Next, attach the following code to the action method of
your Lineitem table object. This code makes the variable
holding the difference between the posted value in the
table and the value entered into the current record zero
if the record is written to disk.
method action(var eventInfo ActionEvent)
if eventInfo.id()=dataPostRecord or
eventInfo.id()=dataUnlockRecord then
num=0
;If the record is posted, make the difference
;between the posted and the unposted equal to 0.
endIf
endmethod
5. Create a field object to contain the sum of Selling Price
times Qty. Inspect the field, and give it a name of Calc.
6. Create a custom method attached to your table object.
Regardless of what else it does, this custom method always
returns a value of 1. To improve efficiency, if the
current record is not a modified record or the first
record of the table, this method returns a value of 1. If
the current record is modified, or is the first record of
PRODUCT : Paradox for Windows NUMBER : 1502
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 5/6
TITLE : Performing a Calculation Based on a Calculated Field
the table, it first performs a scan loop and calculates
the result of Selling Price times Qty for all records
posted to the table. Then, it adds in the number
calculated as the difference between the current posted
record, and entered values, and enters that value into a
field named Calc.
method doit() number
var
tc tcursor
temp number
endVar
;This custom method is called by the calculated
;field. It calculates the formula for all existing
;records and then adds in the difference between the
;posted and unposted values for the current record
;which the changeValue method calculated. To
;maximize performance performance, if you are not on
;the first record or a modified record, nothing needs
;to be done.
if self.recNo<>1 and not
self.recordStatus("modified") then
return 1
endIf
temp=0
;Process all records that currently written to disk
tc.attach(self)
scan tc:
temp=tc.Qty*tc."Selling Price"+temp
endScan
tc.close()
;Num is a variable in the var window of the Lineitem
;table. It is calculated in the changeValue method
;of the Qty and Selling Price fields.
if num.isAssigned() then
calc=temp+num
PRODUCT : Paradox for Windows NUMBER : 1502
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 6/6
TITLE : Performing a Calculation Based on a Calculated Field
else
calc=temp
endIf
;Reinitialize the difference between the posted and
;unposted and unposted values back to 0.
num=0
return 1
endmethod
7. Finally, create your calculated field that calculates Qty
times Selling Price for each record. The calculation
should look as follows:
[file."Selling Price"]*[file.Qty]*doit()
Note to users with a modem: a form that contains a working
example of this code may be found on the Borland Download BBS and
in Library 4 of the PDOXWIN forum in CompuServe. The filename is
CALC.ZIP. For information on Borland's Online Services, refer to
Technical Information Sheet 9604.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.