How
to add a Calculated field with
dQuery
by Roland Wingerter
PROVIDED by dBI,
dQuery is a very handy utility to automate the coding of table and database
objects associated with Object-Oriented Data Manipulation Language (OODML). The purpose of this
article is to show how to use dQuery to add a calculated field to a query already
declared within a datamodule (DMD).
Before loading dQuery, be sure
that dBASE’s current folder is the one where the DMD is located. If not,
use the View|Change Current Folder menu option under dQuery to move
to the appropriate folder (trying to open a DMD located elsewhere from
the current folder — with the File|Open menu option, for example
— will get you an error message).
Under dQuery, there are four ways
to open a DMD located in the current folder:
-
Click the File|Open menu item.
Select the DMD in the Open Datamodule dialog box. Finally click
the Open pushbutton.
-
Use the
Ctrl-o shortcut to access
directly the Open Datamodule dialog box. Select the DMD and click
the Open pushbutton.
-
In the left bottom pane (called
Explore & Open), select the first choice (Datamodules) among the
items listed in the combobox. With the help of the Navigator under that
combobox, go to the current folder. Click the “+” sign at the left of that
folder in order to see the DMDs it contains. Drag’n drop the DMD unto dQuery’s
upper right pane.
-
From Windows’ Exporer, drag’n drop
the DMD anywhere unto dQuery.
Click on the table to which you want
to add the calculated field and push the F11 functional key or right-click
on that table. From the context menu, select Table and Query properties
- F 11. This will load the table’s query under the Property Inspector.
Click the Events tab. Click on the onOpen item. A wrench
tool will appear at the right of the onOpen event handler. Click
that wrench tool: this will minimize dQuery and open the Source code editor
under dBASE PLUS.
The query’s
onOpen()
event handler
is now ready to receive your code. Let’s add the code for creating a new
field object:
| |
Function MyTable1_onOpen
local oField
oField = new Field()
oField.fieldName := "newFieldName"
oField.length := 60
this.rowset.fields.add(oField)
// this.parent = fields object
oField.beforeGetValue := {|| this.Parent["First_Name"].value.rightTrim();
+ " " + this.Parent["Last_Name"].value }
return |
| |
|
Close dQuery, save your changes
and reopen dQuery. The calculated field should now be displayed in the
table’s field list and in the data area.
In my opinion, the difficult part
is to get correct code in the codeblock of the
beforeGetValue() event
handler, especially when having to refer to objects other than the field
itself (see next paragraph).
If you prefer, you can add the codeblock later,
after saving and reopening the form, and here’s how:
- Under the Form designer, click
on the query object. Use the combobox near the top of the Inspector to
access directly to the query.rowset.fields
array.
- Click on the name of the calculated
field in the Inspector: an “I” tool will appear at right: click on it.
- Select the Events tab and
click on the beforeGetValue() event
handler. Two tools will appear at right: select the “Wrench” tool.
- Type your code in the Codeblock
Builder.
Regardless of when you add the
beforeGetValue() codeblock,
note that the code will be called from the field object’s point of view,
so in this context the object reference
this represents the (calculated)
field object. Then, working back up the object hierarchy,
this.parent is the field
array object, this.parent.parent is
the rowset, this.parent.parent.parent
is the query, and
this.parent.parent.parent.parent is
the DMD (or the form if the query belongs directly to the form).
If you manage to survive this sort of convoluted coding, the calculated
field itself should be a breeze.
I would like
to thank Jean-Pierre Martel and David L. Stone for the improvements they brought
to this text.