There are typically three types of variables available in formulas: database record variables, database default variables and user interface control 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).
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 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.
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.
Variables Available in Formulas
Using Table and Record Control Functions in Formulas
Using .NET Framework Functions in Formulas
Using Custom Functions in Formulas