Project requirements may dictate that conversion should be performed on field values.
For example, the Customer# field of the Customers table in the Northwind database may need to be converted to uppercase.
![Customer# field value is uppercase. Customer# field value is uppercase.](/blog/2012/06/value-conversion/image02.png)
However, there is no mechanism preventing users from saving lowercase letters in the field.
![Customer# field has no validation to prevent lowercase letters. Customer# field has no validation to prevent lowercase letters.](/blog/2012/06/value-conversion/image04.png)
Let’s create an SQL Business Rule to perform field value conversion.
Validate on Insert or Update
Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Customers / Business Rules node, and select New Business Rule.
![New Business Rule for Customers controller. New Business Rule for Customers controller.](/blog/2012/06/value-conversion/image06.png)
Use the following properties:
Property | Value |
Command Name | Insert|Update |
Type | SQL |
Phase | Before |
Script | set @CustomerID = UPPER(@CustomerID)
|
Press OK to save the business rule. On the toolbar, press Browse.
Navigate to the Customers page, and edit a record. Insert some mixed-case letters in the Customer# field.
![Value of Customer# with lowercase letters. Value of Customer# with lowercase letters.](/blog/2012/06/value-conversion/image08.png)
Save the record. The field value will be converted to uppercase.
![Value of Customer# has been converted to uppercase. Value of Customer# has been converted to uppercase.](/blog/2012/06/value-conversion/image10.png)
Validate on Calculate
You can also perform just-in-time value conversion, when the user moves focus away from the field.
Switch back to the Project Designer. In the Project Explorer, double-click on Customers / Business Rule / Insert|Update node.
![InsertUpdate business rule for Customers controller. InsertUpdate business rule for Customers controller.](/blog/2012/06/value-conversion/image12.png)
Change the Command Name property:
Property |
New Value |
Command Name |
Calculate |
Press OK to save the business rule. In order for the calculation to be performed, the field needs to cause a server request.
In the Project Explorer, double-click on Customers / Fields / CustomerID field node.
![CustomerID field of Customers controller. CustomerID field of Customers controller.](/blog/2012/06/value-conversion/image14.png)
Make the following changes:
Property |
New Value |
The value of this field is calculated by a business rule expression. |
True |
Context Fields |
CustomerID |
Press OK to save the field. On the toolbar, press Browse.
On the Customers page, edit a record. Insert mixed-case characters into the Customer# field.
![Lower case characters inserted into CustomerID field. Lower case characters inserted into CustomerID field.](/blog/2012/06/value-conversion/image16.png)
Press Tab on your keyboard or click on another field. The client library will convert the value in Customer# field into uppercase.
![CustomerID value converted to uppercase. CustomerID value converted to uppercase.](/blog/2012/06/value-conversion/image18.png)
Expanding the Business Rule
The user may still click on the OK button and save the lowercase characters in the field. Therefore, you may need to combine both Insert|Update and Calculate command handling. The following Command Name will cover all possible methods of changing the field.
Property |
New Value |
Command Name |
Calculate|Insert|Update |
The same business logic can be implemented with the help of C#/Visual Basic Business Rules.