The Formulas tab allows data validation before saving. On the “Validate when, Saving record” event, a formula can be specified that checks the values entered by the application user and report an error message. The formula can also use values from any of the other controls on the page including controls from parent record. The formula must return a blank string if the validation is successful, or return an error message.
A validation formula must use the IF() function:
= IF(expression, value1, value2)
The returned value from the IF function is considered an error message and reported to the user in a message box. A successful validation must return a blank string.
Validation is performed before the data is saved into the data source (generally the database). As such, only user interface controls can be used in the validation formula; data source fields cannot be used. User interface controls typically are text values and may contain number or date formatting separators. Call the respective Parse function to return a value of the appropriate type for comparison, such as PARSEDATE or PARSEDECIMAL.
The Validation expression can be combined with other Boolean expressions using AND, OR, NOT, and can also be nested to any level.
|
BirthDate |
= IF(PARSEDATE(EmployeeRecordControl.BirthDate.Text) > TODAY(), "", "Birth Date cannot be in the future.") If the birth date is in the future, report an error message, otherwise return an empty string to indicate a valid value. Use the value from the textbox user interface control on the page since Validation is performed before the values are retrieved into the DataSource. Use the Parse function to ensure that a proper DateTime object is returned for comparison with today’s date. |
|
OrderDate |
= IF(PARSEDATE(OrdersRecordControl.OrderDate.Text) < TODAY(), "Order date cannot occur in the past.", "") If the order date is in the past, report an error message. |
|
PostalCode |
= IF (OrdersRecordControl.ShipCountry.Text = "USA" AND OrdersRecordControl.ShipPostalCode.Text.Trim().Length = 0, "Please specify a ZIP Code for US Customers", "") If the Country is USA and the Postal Code field is left blank, report an error message to the user to specify the ZIP code. |
If a formula is specified for multiple controls, each formula will be evaluated, and the combined error message will be displayed. For example, if both the order date is in the past and the ZIP code is not specified for US customers, the following validation message will be displayed to the user.
Dropdown Filtering with Formulas
Cross-site Scripting and SQL Injection Attacks