Thursday, November 29, 2012
Calculating Read-Only Fields with SQL

Let’s create a Full Name calculated field in the Employees controller. This field will display a concatenation of First Name and Last Name field values.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Employees / Fields node, and press New Field.

New Field context menu option in Code On Time web application Project Explorer.

Give this field the following settings:

Property Value
Name FullName
Type String
Length 80
Allow Null Values true
The value is calculated by a business rule expression true
Label Full Name
Values of this field cannot be edited true

Press OK to save the field.

In the Project Explorer, drag Employees / Fields / FullName (String(80), read-only) field node and drop it onto Employees / Views / createForm1 to create a data field.

Dropping Fullname field node onto 'createForm1' view.      FullName data field has been created in 'createForm1' view.

Right-click on Employees / Fields / FullName (String(80), read-only) node, and press Add SQL Converter.

Add SQL Converter context menu option in the Project Explorer.

Double-click on Employees / Fields / FullName node.

FullName field node in Employees controller.

Change the Context Fields property:

Property Value
Context Fields FirstName, LastName

Press OK to save the field. Double-click on Employees / Business Rules / FullName_Converter – Calculate|Insert|Update (Sql / Before) - r100 node.

'FullName_Converter' business rule node in Employees controller in the Project Explorer.

Replace the default script with the following:

Property New Value
Script
if @FirstName is not null and @LastName is not null
begin
    set @FullName = @FirstName + ' ' + @LastName
end

Press OK to save the business rule. On the Project Designer toolbar, press Browse.

Navigate to the Employees page, and create a new employee. Enter values in First Name and Last Name fields.

Values entered in LastName and FirstName fields.

When the user shifts focus away from the field, Full Name will be calculated.

When user shifts focus, the FullName field is calculated.