|
The Formula Language
The formula language uses a combination of functions, variables and operators to provide easy access to the database record, the controls on the page and to environment variables such as sessions and cookies. The language is not case-sensitive.
Arithmetic Operators
All standard arithmetic operators as well as the modulus (%) and power (^) operators are supported.
= a*2 + b ^ 2 - 100 % 5
Comparison Operators
All the standard comparison operators are supported. The not equal operator is <> and the equal operator is =.
= a <> 100
AND, OR, XOR, NOT Operators
Both logical and bitwise operations are supported. If both operands are booleans, then the operation is logical. If both are integer, the operation is bitwise. Any other combination results in an error.
Example (logical):
= a > 100 AND NOT b = 100
Example (bitwise):
= (100 OR 2) AND 1
Shift Operators
The left (<<) and right (>>) shift operators do a bitwise shift and are only valid on integer types.
= 100 >> 2
Concatenation
The + operator also serves as the string concatenation operator. If either of its operands is a string, it will perform a concatenate instead of an addition. It is valid for only one operand to be a string in which case, both operands are converted to string and concatenated accordingly.
= "abc" + "def"
= "the number is: " + 100
Indexing
The indexing operator takes the form: member[indexExpression]. Any expression can appear inside the brackets. Indexing a type which is not an array generates an error.
= array[i + 1] + 100
Literal types
These literals are supported in expressions:
|
Literal type |
Description |
|
Char |
A character in single quotes: 'a' |
|
Boolean |
Either true or false |
|
Real |
Any number with a decimal point. You can use the 'd', 'f', or 'm' suffixes to specify whether the number should be a double, single, or decimal respectively. The default data type used is Decimal. |
|
Integer |
Any number without a decimal point. Append "L" to force the number to a 64-bit integer and/or a "U" to force it to unsigned. The Formula Evaluator will try to assign an integer literal to the first integer type that can contain the value. |
|
Hex |
Integer constants can also be specified in hex notation: 0xFF12 |
|
String |
String literals are enclosed in double quotes and escaping characters follows the same rules as C#: "string\u0021\r\n a \"new\" line". |
|
Null |
Using the keyword null will load the null reference into an expression. |
|
DateTime |
A valid .NET DateTime. A specific date can be provided by surrounding it with #'s. Example: #08/06/2008#.ToLongDateString() |
|
TimeSpan |
A string in the format ##[d.]hh:mm[:ss[.ff]]#. Example: #08/06/2008# + ##1.23:45# |
Casting
Casting is performed using the special cast function which takes the form cast(value, type).
= 100 + cast(obj, int)
While casting work for most cases, there are times when there are additional formatting characters in the value. For example, the total amount may be displayed as $1,234.56. In this case, casting is not sufficient, but instead the value must be parsed to remove the currency symbol and separator characters. Conditional Operator
Conditional operators that return a result based on a Boolean condition are supported. It is implemented as a special function of the form IF(condition, whenTrue, whenFalse). The operator is a "true" conditional operator: only the expression that matches the condition is evaluated.
= IF(a > 100 and b > 10, "both greater", "less")
In Operator
The In operator is a Boolean binary operator that returns true if its first operand is contained in its second operand. It has two forms:
-
List: Searches a list of values for a given value: value IN (value1, value2, value3,...). The value is compared against each value in the list and true is returned if the value is found, false if no match is found.
-
Collection or Arrays: Searches a single collection or array for a given value: value IN collection.
Example (List):
= IF(100 in (100, 200, 300, -1), "in", "not in")
Example (Collection):
"= IF("ironspeed\mlam" in ROLES, "in", "not in")
Case Insensitive
Formulas are case insensitive regardless of whether the application language is Visual Basic .NET or C#. Function names such as ParseDecimal, PARSEDECIMAL, parseDecimal, parsedecimal, etc. all refer to the same function. Control names and properties can be specified in any case. For example, the following formula returns the same value:
= OrdersRecordControl.CustomerId.Text
= ordersrecordcontrol.customerid.text
Escape Characters
In order for application code function properly, escape characters are used to escape problem characters (“, \). In the Formula tab, the excape character used is ‘\’.
The examples show different escape character usage.
|
Formula |
Display |
|
= "C:\\Users\\IronSpeed\\Desktop" |
C:\Users\IronSpeed\Desktop |
|
= “\”” |
“ |
|
= “\\” |
\ |
Variables Available in Formulas
There are typically three types of variables available in formulas: database record variables, database default variables and user interface control variables.
Database Record Variables
Database record variables correspond to values retrieved from the current database record and have a very simple format:
<FieldName>
For example, if the current record being displayed is a Customer record, available variables include all columns within that database record, e.g.:
FirstName
LastName
CompanyName
These variables are typically used to initialize your page’s user interface controls via the “Initialize when Displaying” event, e.g.:
= FirstName
= LastName
= CompanyName
However, database record variables are typically not used for validation or saving data back into the database, since they contain the original value retrieved from the database, which may have been updated by the application user.
Database record variables typically require no conversion for use, but may require formatting for display purposes. Usage is dictated by the underlying type in the database. For example, an OrderDate database record variable would typically be a DateTime object displayed as “mm/dd/yy” (e.g., 7/31/20102), the default display format set in the database. The display format can be changed by modifying the “Display format” property on the Property Sheet, for example to “Weekday, Month Day, Year” (Tuesday, July 31, 2012).
Database Default Variables
You may have set a default value for a field in your underlying database or you may have specified a default value on the Database tab in Iron Speed Designer. These default values typically prevent NULL values from being inserted into the database if the application user does not enter a value. This also lets the application user to review the default value and change it if necessary.
Database default values can be used in formulas as:
<FieldName>DefaultValue
“FieldName” is the name of the underlying database field.
The variable returns an empty string if there is no default value for the database field. The default value is stored as a string and is formatted prior to being displayed in the textbox or label in the default format of the field.
Database default variables are typically used in the “Initialize when Adding record” event in the Formula tab. For example, user interface controls can be initialized to the database default variables on an Add Record or Edit Table page where new rows can be added, e.g.:
= ShipDateDefaultValue
= CountryDefaultValue
User Interface Controls
User interface control variables reference the contents of user interface controls displayed on the web page. User interface control variables can be referenced in formulas as:
<PanelName>.<ControlName>.<Property>
These variables are prefixed with the name of the record control or row being displayed. For example, a textbox for entering the first name of a customer record may be referenced as:
CustomerRecordControl.FirstName.Text
“Text” is the specific control property being referenced. Similarly, you can reference textbox controls within a table control (repeater):
EmployeesTableControlRow.BirthDate.Text
If the control is a dropdown list or listbox, then the property used typically would be “SelectedValue”, e.g.:
CustomerRecordControl.ProductID.SelectecValue
EmployeesTableControl.LastNameFilter.SelectedValue
Order_DetailsTableControlRow.ProductID.SelectedValue
The property for a checkbox would be “CheckedValue”, e.g.:
CustomerRecordControl.ActiveStatus.CheckedValue
User interface values are typically text values and must be converted to a native format before being used in formulas. For example, the HireDate field must be converted from raw text to a DateTime object before being compared to another date. Use the PARSEDATE function to convert text strings into DateTime objects, e.g.:
= PARSEDATE(CustomersRecordControl.HireDate.Text)
Use the PARSEINTEGER and PARSEDECIMAL functions to convert numeric values to integer or decimal objects. This includes all percentage values.
= PARSEINTEGER(EmployeeRecordControl.Age.Text)
= PARSEDECIMAL(OrderDetailsRecordControl.Amount.Text)
For example, to calculate the ExtendedPrice in a formula, the UnitPrice and the Quantity must be converted before multiplying them, e.g.:
= PARSEDECIMAL(Order_DetailsTableControlRow.UnitPrice.Text) * PARSEDECIMAL(Order_DetailsTableControlRow.Quanity.Text)
Note: If the value of a control is being used in the Formula of the other control, then both controls must be located in the same panel. If controls belong to different panels then neither can include a reference to the other in their respective formulas.
Referencing Other Controls on the Page
All parent or sibling controls on the page can be referred to directly within the Property Sheet. For example, if the page contains a Customer record (parent), an Order table (child) and Order Details table inside the Order table (grandchild, table-inside-table), then your formula can reference each of these controls based on where the formula is specified.
|
Formula Specified At |
Available References |
|
Customer (Parent or Master record control) |
|
|
Order Table (Child or Detail) – Table level controls (e.g., search or filter controls) |
|
|
Order Table (Child or Detail) – Row level controls (e.g., fields within the order table row) |
|
|
Order Details (Grandchild / Table-in-table) – Table level controls (e.g., search or filter controls) |
|
|
Order Details (Grandchild / Table-in-table) – Row level controls (e.g., fields within the order details table row) |
|
All parent controls can be referenced, but access to child controls is limited since a specific row number is required.
Formula Evaluation Order
Formula evaluation occurs within the Set methods for each control. The Set method is called from within the DataBind right after the LoadData function is called to load the data from the database. Microsoft .NET Framework does not guarantee the order in which the DataBind will be called for each control within the page. For example, if there are two record controls side-by-side, there is no guarantee that the top-left record control’s DataBind will be called prior to the DataBind of the lower-right control on the page. Specifying a formula in the second record control that uses a value from the first record control may or may not work based on the order in which Microsoft .NET Framework calls the DataBind method.
If the controls have a parent-child relationship, then the parent will always be loaded first prior to the child since the Id of the parent is required to load the children. This ensures that child controls can access the parent controls without any problem.
If there is a need to initialize the value of a control from a value of another control, the best approach is to use the database record value instead of the control value, since the LoadData will have loaded the database record prior to the DataBind method being called. Since the database record is loaded as a whole, there is no problem using other fields within the database record. Similarly, parent database records are also available for use in initializing other user interface controls.
Indexing
All functions use zero-based indexing in order to be consistent with Microsoft .NET Framework languages such as Visual Basic .NET and C#. (Microsoft Excel uses one-based indexing). This example returns all remaining characters from position 2 onwards of a zero-based index - “st”:
= SUBSTRING(“Test”, 2)
|
Position |
0 |
1 |
2 |
3 |
|
String |
T |
e |
s |
t |
This example returns two characters from position 5 onwards of a zero-based index - “Sp”:
= SUBSTRING(“Iron Speed Designer”, 5, 2)
|
Position |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
|
String |
I |
r |
o |
n |
|
S |
p |
e |
e |
d |
|
D |
e |
s |
i |
g |
n |
e |
r |
Using Table and Record Control Functions in Formulas
The Table and Record controls have a number of functions available that operate on these controls.
Table control functions
These functions are available for all Table controls on a page:
-
SUM
-
MAX
-
MIN
-
COUNT
-
COUNTA
-
MEAN
-
AVERAGE
-
MODE
-
MEDIAN
-
RANGE
In some cases, to make functions easier to use, there is an alternative interface provided for the functions. For example, the SUM() function requires a table control and the name of a literal, textbox or label user interface control. This function then goes through all of the literal controls within the table control and sums the values in each row. Normally, this function is called as:
= SUM(OrderDetailsTableControl, “ExtendedPrice”)
To provide an alternative implementation, the SUM() function can also be called as follows:
= OrderDetailsTableControl.SUM(“ExtendedPrice”)
The recommend approach is to use the second, alternative implementation to make it easier to understand.
Record control functions
The following functions are implemented for the Base Application Record Control and are available for all Record Controls and Table Control Rows on a page:
-
RANK
-
ROWNUM
-
RUNNINGTOTAL
Using .NET Framework Functions in Formulas
A number of Microsoft .NET functions are also available for use in formulas. In particular, all shared and static properties and functions from the String, Math, DateTime and Convert classes in the Microsoft .NET Framework are available.
The first argument is the type and the second argument is the prefix (or namespace) used to refer to the function. For example, to get a string of all roles separated by commas, you can use the Join() static function on the String class:
= String.Join(“,”, Roles())
In addition to using the shared or static functions from the above classes, all non-shared functions can be used if the formula already uses an instance of the given data type. For example, by calling the PARSEDATE function on a user interface control returns a DateTime object instance. Non-shared DateTime functions can be called on this instance if required. For example,
= PARSEDATE(OrdersRecordControl.OrderDate.Text).AddDays(-10)
will return a date 10 days earlier than the value entered in the OrderDate textbox control.
String static function examples
= String.Empty
= String.Compare(“this”, “that”)
= String.Format(“This is a {0} idea.”, “GREAT”)
Reference: http://msdn.microsoft.com/en-us/library/system.string_members.aspx
DateTime static function examples
= DateTime.DaysInMonth(2010, 4)
= DateTime.IsLeapYear(2010)
= DateTime.MaxValue
Reference: http://msdn.microsoft.com/en-us/library/system.datetime_members.aspx
Convert static function examples
= Convert.ToDecimal(10)
= Convert.ToString(10)
Reference: http://msdn.microsoft.com/en-us/library/system.convert_members.aspx
Math static function examples:
= Math.Abs(-55)
= Math.Log(45)
= Math.Truncate(10.45)
Reference: http://msdn.microsoft.com/en-us/library/system.math_members.aspx
Using Custom Functions in Formulas
You can use your own custom functions in formulas in your application. There are three types of custom functions you can add:
-
Local extensions: Functions specific to the page, record or table control.
-
Application-wide extensions: Functions available for the entire application to use.
-
Importing .NET Framework classes and functions: You can use any .NET Framework class by importing the class in the formula functions.
Local Extensions
For localized extensions, you can add functions at the page, table control or row control levels. Specifically, the formula language exposes variables for page object as well as the table control, record control and row objects. So you can call any function you want on these classes as well.
For example, on the ShowCategoriesTable.aspx displaying a Categories table page, you can define functions on the ShowCategoriesTable page class, on the CategoriesTableControl class, or on the CategoriesTableControlRow class as needed.
As an example, if you defined the following non-shared (VB.NET) or non-static (C#) functions:
-
In the ShowCategoriesTable class, define the GetPageValue() function.
-
In the CategoriesTableControl class, define the GetTableValue() function.
-
In the CategoriesTableControlRow class, define the GetRowValue() function.
On the Formula tab, these formulas can be used to access each of these functions respectively:
= Page.GetPageValue()
= CategoriesTableControl.GetTableValue()
= CategoriesTableControlRow.GetRowValue()
Each of these functions can return a constant, another value local to their respective objects, call a function in another class, call another shared function on another class, another DLL or any other function that is possible to implement in the application.
Note that these formulas are context sensitive. The Row function can only be called from within a row because the CategoriesTableControlRow variable is only accessible from the row. Similarly, the Table function can be called from within the row or when within the table area (e.g., filters or buttons area). The Page function can be called from anywhere on the page.
Application-wide Extensions
The formula functions for an application are implemented in two source code files within the application’s source code. New formula functions can be added to these files, or existing functions can be modified as desired.
The formula functions are split into two files because some functions cannot be used within the WHERE clause of a query. For example, the SUM, RANK or ROWNUM functions are specific to the presentation layer since they operate on textbox, literal and label controls on the web page. These functions can only be used in the Property Sheet and cannot be used in the WHERE clause of a query. Most functions however are generic and can be used in either the Property Sheet or the WHERE clause of a query.
-
BaseFormulaUtils.vb or BaseFormulaUtils.cs: Located in the \Data Access Layer\Shared folder, this file contains all the formula functions that are available in common to both the data access layer and the presentation layer.
-
FormulaUtils.vb or FormulaUtils.cs: Located in the \Shared folder, this file contains all the formula functions that are available only at the presentation layer. These functions cannot be used in the WHERE clause of a query.
Importing .NET Framework Classes and Functions
Any shared or static functions from either the .NET Framework or your own classes can be used by importing their types in the FormulaUtils.vb or FormulaUtils.cs class and registering them via the Evaluator.Imports.AddType() function, e.g.:
Evaluator.Imports.AddType(GetType(Math), "Math")
Evaluator.Imports.AddType(GetType(DateTime), "DateTime")
Evaluator.Imports.AddType(GetType(Convert), "Convert")
Evaluator.Imports.AddType(GetType(String), "String")
The first argument is the type and the second argument is the prefix (or namespace) used to refer to the function.
Formula Error Reporting
The formula editor reports all parser and evaluation errors within the value returned. This is similar to Microsoft Excel displaying #VALUE! in a cell when there is an error in the formula. Iron Speed Designer similarly displays an ERROR: <error message> instead of the calculated value in the event of an error to make sure it is easy to identify and fix any parsing or evaluation errors. No exceptions are thrown in the event of an error of a formula.