Tutorial 4 - Calculated Fields and True DataControl Expressions

In this tutorial, you will learn how to create a calculated field. A calculated field is one whose value is not stored directly in the database but is determined by the values of other fields. More importantly, this will be your first acquaintance with True DataControl expressions.

Start a new project.
Place the following controls on the form (Form1) as shown in the figure: two TData controls (TData1, TData2); four TextBox controls (Text1 to 4); a DataCombo control (DataCombo1, because you need to include Microsoft DataList Controls 6.0 (OLEDB) into the project’s list of components); a MaskEdBox control (MaskEdBox1; include Microsoft Masked Edit Control into the project’s list of components); and six labels (Label1 to 6).

Set properties as follows (you can use the DataSource property page to set TData data source properties, as described in Tutorial 2):

Example Title	Copy Code
TData1.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB TData1.CommandType 2 – adCmdTable TData1.RecordSource [Order Details] TData2.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB TData2.CommandType 2 – adCmdTable TData2.RecordSource Products DataCombo1.DataSource TData1 DataCombo1.DataField ProductID DataCombo1.BoundColumn ProductID DataCombo1.RowSource TData2 DataCombo1.ListField ProductName DataCombo1.Style 2 - dbcDropdownList Text1.DataSource TData1 Text1.DataField OrderID Text2.DataSource TData1 Text2.DataField UnitPrice Text3.DataSource TData1 Text3.DataField Quantity MaskEdBox1.DataSource TData1 MaskEdBox1.DataField Discount MaskEdBox1.Format 0%

Example Title

Copy Code

TData1.ConnectionString

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB

TData1.CommandType   2 – adCmdTable

TData1.RecordSource  [Order Details]

TData2.ConnectionString

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB

TData2.CommandType   2 – adCmdTable

TData2.RecordSource  Products

DataCombo1.DataSource      TData1

DataCombo1.DataField ProductID

DataCombo1.BoundColumn     ProductID

DataCombo1.RowSource TData2

DataCombo1.ListField ProductName

DataCombo1.Style     2 - dbcDropdownList

Text1.DataSource     TData1

Text1.DataField      OrderID

Text2.DataSource     TData1

Text2.DataField      UnitPrice

Text3.DataSource     TData1

Text3.DataField      Quantity

MaskEdBox1.DataSource      TData1

MaskEdBox1.DataField Discount

MaskEdBox1.Format    0%

Set the caption properties of Label1 to Label6 as shown in the figure in Step 2.

Note: The use of a DataCombo control and a MaskEdBox control is not essential for the True DataControl calculated fields feature demonstrated in this tutorial. They are used to provide a more realistic example of an actual application, and to demonstrate that True DataControl can work with any type of data-bound controls. The labels, as well, are obviously not essential. The TData2 control’s only purpose is to serve as the RowSource for DataCombo1.

Open the True DataControl property pages by clicking the right mouse button over TData1 and selecting TData Properties. Select theFields property page. Click the right mouse button over the fields list area and select New Field from the menu (alternatively, you can press the New button). A new field with the default name FIELD_0 appears. Change the name to ExtendedPrice by typing it into the Name text box. Select the Calculated radio button. Select Currency in the Data Type combo box.

Type the following in the Calculated Expression text box while the ExtendedPrice field is selected:

Example Title	Copy Code
UnitPrice * Quantity * (1 - Discount)

This is the formula that provides the value of the ExtendedPrice calculated field. It computes the value using the values of three other fields: UnitPrice, Quantity, and Discount. These are data fields, meaning their values are actually stored in the database.

Intermission 1 - True DataControl Expression Language

The True DataControl expression language is a subset of VBScript, or Visual Basic Scripting Edition language. It is primarily the subset of VBScript that contains expressions and only expressions. You cannot use VBScript statements such as: If...Then...Else. Because of this, you cannot write full-fledged programs in this language, but you may certainly use any VBScript function and operator in your expressions. The variables you can use in True DataControl expressions are fields and parameters (parameters are fully described in Tutorial 7). You can refer to variables from an ancestor of your True DataControl if it participates in a master-detail relationship with other controls (see Tutorials 5, 6, and 7). There are also two functions, IIF and Format, that were added to the True DataControl expression language since VBScript does not support them (although they are available in Visual Basic). You will use the IIF function in some of the following tutorials.

If you are not familiar with VBScript, do not worry about needing to learn an entirely new language. It is still Visual Basic. The most significant difference is that VBScript has only one data type, Variant, making VBScript merely a simplified version of Visual Basic.

As you will see later in this tutorial, the True DataControl property pages are equipped with a special expression editor dialog to help you build an expression. The True DataControl Expression Editor enables you to browse through lists of available variables, functions and operators and paste them into the expression.

Intermission 2 - Evaluation and Re-Evaluation of True DataControl Expressions

Calculated expressions are just one type of True DataControl expression. There are other expressions that may be used and these will be introduced later in following tutorials.

Every expression is automatically evaluated at a specific and appropriate time, for each particular type of expression. Calculated expressions, for example, are evaluated to yield a calculated field value each time the value can change. The calculated expression is evaluated both when a new record is retrieved from the database, and when any of the variables in the expression changes its value.

First evaluation mode (retrieving a record) is an example of synchronous evaluation. A synchronous evaluation occurs as a part of a procedure performed by a TData control. You do not need to perform such an evaluation from code since it will be done automatically when necessary. Also, you can rely on the fact that expressions will be evaluated in their correct order. For example, if Quantity is a calculated field, the ExtendedPrice expression will be evaluated after the Quantity expression since the ExtendedPrice is dependent upon Quantity.

Second evaluation mode (on change of one of the variables used in the expression) represents asynchronous evaluation. An asynchronous evaluation is performed if a change of a variable value can cause the expression to change its value. When the TData control detects such a situation it will automatically re-evaluate the expression, without programmer’s interference. This is one of the most attractive features of the TData control, making application creation much easier. Once the user has changed one of the three fields, UnitPrice, Quantity or Discount, the ExtendedPrice field will immediately display the updated value. Please note that this is accomplished without writing a single line of code.

At the moment of synchronous evaluation of expressions, an event is also called for almost every type of a True DataControl expression. This allows you to program a more involved computation in code, if the True DataControl expression language does not satisfy your needs. For example, you can use the CalcFields event to assign values to calculated fields. The syntax for accessing a True DataControl variable is illustrated in the next step:

Although you are almost ready to run the program, we advise you to first review the True DataControl Expression Editor. To bring up the expression editor, press the ellipsis button located at the right edge of the Calculated Expression text box in the Fields property page. The expression editor is shown in the following figure.

You can build or edit an expression with the help of four lists of elements available in the expression: Fields, Parameters (which will be introduced in Tutorial 7), Operators, and Functions and Constants. In case there is a master TData control defined (see subsequent tutorials for an explanation of master-detail relationships), you can choose to display variables (parameters and fields) of either the TData control itself or one of its ancestors, by selecting it in the From combo box. You can also select a category of operators and functions in the two combo boxes above the lists: Operators, and Functions and Constants. Double-click on one of the elements in any of the four list boxes to paste the selected element into the expression.

Without closing the expression editor, try to enter invalid data in the expression. For example, type aUnitPrice instead of UnitPrice. Press the OK button to display an error message stating "Unknown variable: aUnitPrice." Alternatively, you can check the expression syntax without closing the expression editor by pressing the Check button. Leave the "aUnitPrice" error in the expression by selecting OK from the error message.

There are other ways of checking for expression syntax and other possible errors. Press the Check button located in the lower right corner of every True DataControl property page. You will receive a message box with a list of detected errors: " Field OrderID: Unknown variable: aUnitPrice - Error in calculated expression aUnitPrice." If there is more than one mistake, there will, of course, be more error messages. You can go directly to the location of the detected error by pressing the OK button or double-clicking an error message in the list. This will show the Fields page if it is not current. Select the ExtendedPrice field node, and set focus to the Calculated Expression edit box so you can correct the error there. Although most errors may occur in expressions, there are other kinds of errors that are also detectable by the syntax checking. Syntax checking is not only performed when you press the Check button, but is done automatically every time you apply changes made in the True DataControl property pages.

Note: Do not run your program if True DataControl syntax checking reports an error. First fix the error to ensure against unpredictable behavior.

Fix the error in the Calculated Expression text box so that it contains the original formula:

Example Title	Copy Code
UnitPrice * Quantity * (1 - Discount)

Press OK to close the dialog and save changes.

Now, after you have created a new ExtendedPrice field, you must set it as the DataField of the Text4 control:

Example Title	Copy Code
Text4.DataSource TData1 Text4.DataField ExtendedPrice

Run the Program and Observe the Following:

The ExtendedPrice field shows the value of the calculated expression. When you navigate through records using the buttons of the TData1 control, the ExtendedPrice value is updated synchronously with the other fields.
Change one of the three values: UnitPrice, Quantity or Discount in its corresponding TextBox control (MaskEdBox for Discount), and press Tab. The ExtendedPrice value is automatically updated immediately after focus leaves the control.

Close the program. Now we will experiment with the ModificationMode property of a True DataControl field. It enables you to fine-tune the behavior of your application to react to changes made by the end user. To experiment with this setting, open the property pages for the TData1 control. Go to the Fields page, highlight the Quantity field, and select 1 - Immediate in the Modification Mode combo box.

Run the Program and Observe the Following:

The ExtendedPrice is updated each time you make a change in the Quantity field without waiting for the end user to move focus out of the Quantity text box. Every keystroke in the Quantity field updates ExtendedPrice. With this example, it would probably have been more sensible to choose the Modification Mode setting 2 - Leaving Control. But there are cases where immediate update is preferable, one such case is described in the next tutorial.

Close the program. You have successfully completed Tutorial 4.