Collapse All Expand All

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.

1. Start a new project.

2. Place the following controls on the form (Form1) as shown in the figure: a TDataLite control (TDataLite1), an ADODC control (Adodc1) and a True DBGrid 8.0 control (TDBGrid1).

   

3. 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
   Adodc1.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDBGDemo.mdb Adodc1.CommandType 2 – adCmdTable Adodc1.RecordSource [Order Details] TDataLite1.DataSource Adodc1 TDBGrid1.DataSource TDataLite

4. Open the TDataLite1 property pages by clicking the right mouse button over TDataLite1 and selecting TDataLite 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.

5. 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. 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 TDataLite property pages are equipped with a special expression editor dialog to help you build an expression. The 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 TDataLite 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 TDataLite 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 TDataLite 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 TDataLite variable is illustrated in the next step:

1. Although you are almost ready to run the program, we advise you to first review the 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, Operators, and Functions and Constants. 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.

2. 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 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.

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

3. 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.

Run the Program and Observe the Following:

• The ExtendedPrice field shows the value of the calculated expression.

• Change one of the three values: UnitPrice, Quantity or Discount. The ExtendedPrice value is automatically updated.

Close the program. You have successfully completed Tutorial 4.

 

 

---

Copyright (c) GrapeCity, inc. All rights reserved.

Product Support Forum  |  Documentation Feedback