Validation rules you assign to a column in the Database painter are used by default when you place the column in a DataWindow object. You can override the validation rule in the DataWindow painter by defining an ad hoc rule for one specific column.
To specify a validation rule for a column in the DataWindow painter
-
In the DataWindow painter, select View>Column Specifications from the menu bar.
The Column Specification view displays.
-
Create or modify the validation expression. To display the Modify Expression dialog box, display the pop-up menu for the box in which you want to enter a Validation Expression and select Expression. Follow the directions in Specifying the expression.
-
(Optional) Enter a string or string expression to customize the validation error message.
For more information, see Customizing the error message.
-
(Optional) Enter an initial value.
Used for current column only
If you create a validation rule here, it is used only for the current column and is not saved in the extended attribute system tables.
Since a user might have just entered a value in the column, validation rules refer to the current data value, which you can obtain through the GetText DataWindow expression function.
Using GetText ensures that the most recent data entered in the current column is evaluated.
PowerBuilder does the conversion for you
If you have associated a validation rule for a column in the Database painter, PowerBuilder automatically converts the syntax to use GetText when you place the column in a DataWindow object.
GetText returns a string. Be sure to use a data conversion function (such as Integer or Real) if you want to compare the entered value with a datatype other than string.
For more on the GetText function and text patterns, see the section called “GetText” in DataWindow Reference and the section called “Match” in DataWindow Reference.
Referring to other columns
You can refer to the values in other columns by specifying their names in the validation rule. You can paste the column names in the rule using the Columns box.
Here are some examples of validation rules.
Example 1. To check that the data entered in the current column is a positive integer, use this validation rule:
Integer(GetText( )) > 0
Example 2. If the current column contains the discounted price and the column named Full_Price contains the full price, you could use the following validation rule to evaluate the contents of the column using the Full_Price column:
Match(GetText( ),"^[0-9]+$") AND Real(GetText( )) < Full_Price
To pass the validation rule, the data must be all digits (must match the text pattern ^[0-9]+$) and must be less than the amount in the Full_Price column.
Notice that to compare the numeric value in the column with the numeric value in the Full_Price column, the Real function was used to convert the text to a number.
Example 3. In your company, a product price and a sales commission are related in the following way:
-
If the price is greater than or equal to $1000, the commission is between 10 percent and 20 percent
-
If the price is less than $1000, the commission is between 4 percent and 9 percent
The Sales table has two columns, Price and Commission. The validation rule for the Commission column is:
(Number(GetText( )) >= If(price >= 1000, .10, .04)) AND (Number(GetText( )) <= If(price >= 1000, .20, .09))
A customized error message for the Commission column is:
"Price is " + if(price >= 1000, "greater than or equal to","less than") + " 1000. Commission must be between " +\ If(price >= 1000,".10", ".04") + " and " + If(price >= 1000, ".20.", ".09.")
