Warning: Advanced Material

 

Alventis and Designer support Calculated Fields via expressions or formulas written using the SQL language. To create a Calculated field, all you need to do is create a String field as you normally would, and specify the formula for it in the Formula cell of the Fields grid. In general, a field can be either a "real" physical field that ends up saved to the physical data table or a Calculated field that is not saved to the table but is merely maintained "virtually", in-memory only, using the formula you specify. It is the existence of the formula that determines whether the field is physical or Calculated. All formulas are deemed to produce a String result, so only String fields can be Calculated and have a formula.

 

Formula Syntax. Alventis uses the formula by inserting it into a SELECT query statement: SELECT <formula> AS CalculatedFieldName FROM TheFieldsTable. For example, if the formula is "FName + ' ' + LName", the query becomes "SELECT FName + ' ' + LName AS CalculatedFieldName FROM TheFieldsTable". You can therefore use whatever SQL syntax is legal for specifying the calculated "column" value, including the full array of SQL operators and functions available to you. Please refer to the SQL Reference if you need more information.

 

Note that it is possible to produce multi-line "string" output by concatenating lines with the CR/LF characters like this:

'Text of first line' + #13 + #10 + 'Second line'

Such calculated String fields are great candidates for the Multi-Line Edit Box item (which is its primary intended purpose).

 

It is possible to "convert" an existing field between a physical and Calculated state. That is, a field that already exists in the table can become Calculated. Just supply the formula and save the Table. The physical field will be deleted and all the data it may have been storing will be lost, so exercise caution. Removing a formula from a Calculated field converts it to an ordinary physical one.