Business Rules: RowBuilder Attribute and Existing Rows

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Thursday, February 26, 2009PrintSubscribe
Business Rules: RowBuilder Attribute and Existing Rows

Northwind database has a cross-reference table EmployeeTerritories that link together an employee and a territory. Here is a user interface generated for this table by Data Aquarium premium project.

List of employee territories is filtered by a region.

image

Employee territory is displayed in edit form.

image

While completely function this type of user interface will still benefit if we display a collection of all territories right on the employee screen and allow user to check mark territories that is user is responsible for.

Creating a Placeholder Field For a List of Territories

Open ~/Controllers/Employees.xml data controller and modify the text of command1 as shown below. Notice the new field Territories just before the from clause. This field is used just as placeholder and an actual value is going to be provided by a business rule.

        <command id="command1" type="Text">
            <text>
                <![CDATA[
select
    "Employees"."EmployeeID" "EmployeeID"
    ,"Employees"."LastName" "LastName"
    ,"Employees"."FirstName" "FirstName"
    ,"Employees"."Title" "Title"
    ,"Employees"."TitleOfCourtesy" "TitleOfCourtesy"
    ,"Employees"."BirthDate" "BirthDate"
    ,"Employees"."HireDate" "HireDate"
    ,"Employees"."Address" "Address"
    ,"Employees"."City" "City"
    ,"Employees"."Region" "Region"
    ,"Employees"."PostalCode" "PostalCode"
    ,"Employees"."Country" "Country"
    ,"Employees"."HomePhone" "HomePhone"
    ,"Employees"."Extension" "Extension"
    ,"Employees"."Photo" "Photo"
    ,"Employees"."Notes" "Notes"
    ,"Employees"."ReportsTo" "ReportsTo"
    ,"ReportsTo"."LastName" "ReportsToLastName"
    ,"Employees"."PhotoPath" "PhotoPath",
    ,null "Territories"
from "dbo"."Employees" "Employees"
    left join "dbo"."Employees" "ReportsTo" on "Employees"."ReportsTo" = "ReportsTo"."EmployeeID"
]]>
            </text>
        </command>

Add new field to the list of fields.

<field name="Territories" type="String">
    <items style="CheckBoxList" dataController="Territories" 
        dataTextField="TerritoryDescription"/>
</field>

Modify editForm1 to include a reference to the field in the user interface of the form right after the FirstName field. We have specified 3 as number of columns for the field, which is supposed to be displayed as a check box list.

<dataField fieldName="LastName" columns="20" />
<dataField fieldName="FirstName" columns="10" />
<dataField fieldName="Territories" columns="3"/>

Run the sample application and start editing any employee record in form mode. Here is what you will likely see.

image

None of the check boxes is checked. We will add a business rule to populate the check boxes.

Creating a Business Rule to Populate an Existing Row

Open business rules class ~/App_Code/Class1.cs(.vb) that was created as described in the RowBuilder attribute introduction. Add the following method to the class.

C#:

[RowBuilder("Employees", "editForm1", RowKind.Existing)]
protected void PrepareExistingEmployeeRow()
{
    int employeeId = Convert.ToInt32(SelectFieldValue("EmployeeID"));
    List<EmployeeTerritories> territories = 
        EmployeeTerritories.Select(employeeId, null, null, null, null);
    StringBuilder sb = new StringBuilder();
    foreach (EmployeeTerritories et in territories)
    {
        if (sb.Length > 0)
            sb.Append(",");
        sb.Append(et.TerritoryID);
    }
    UpdateFieldValue("Territories", sb.ToString());
}

VB:

Protected Sub PrepareExistingEmployeeRow()
    Dim employeeId As Integer = Convert.ToInt32(SelectFieldValue("EmployeeID"))
    Dim territories As List(Of EmployeeTerritories) = _
        EmployeeTerritories.Select(employeeId, Nothing, Nothing, Nothing, Nothing)
    Dim sb As StringBuilder = New StringBuilder()
    For Each et As EmployeeTerritories In territories
        If sb.Length > 0 Then
            sb.Append(",")
        End If
        sb.Append(et.TerritoryID)
    Next
    UpdateFieldValue("Territories", sb.ToString())
End Sub

The name of the business rule method is irrelevant. The rule will be automatically invoked when data controller Employees is preparing data for editForm1 view and the form will be displaying an existing row. This method is called for each row returned to the client. Data Aquarium Framework only returns the exact number of rows that are requested by a client view.

Method SelectFieldValue is inherited from the base class BusinessRules and allows to access values that will be returned for a row that is being built at this moment. We are obtaining a list of employee territories via business object EmployeeTerritories.

Next we are creating a comma-separated list of territory IDs and update the value of the Territories field with the result accumulated in an instance of System.Text.StringBuilder. Please make sure to add System.Text namespace in the list of imported namespaces.

Lookup item style CheckBoxList is designed to automatically handle comma separated lists of values.

Here is how editForm1 looks when we select a row. Text corresponding to each territory of selected employee is automatically matched to an ID of each employee territory.

image

Here is how eidtForm1 is transformed when user clicks on Edit button.

image

Conclusion

RowBuilder attribute allows providing calculated field values for new and existing rows returned to a client script running in a browser. You can create fields that don't actually exist in your database and figure their values on-the-fly.

There is still work to do when you need to save values of calculated fields. We will review this in the next post dedicated to ControllerAction attribute.