Blog: Posts from October, 2008

Oracle database server provides support for sequences of unique values that are typically used to generate unique IDs for primary key columns or new records. Data Aquarium Framework and Aquarium Express are supporting automatic execution of static SQL statements just before a table record insertion takes place.

Consider the application created from HR demo schema that comes with Oracle database servers. The DEPARTMENTS table has a matching DEPARTMENTS_SEQ sequence that can be used to supply unique values for the primary key DEPARTMENT_ID.

Form createForm1 is displayed below. You can see that the primary key field is visible on the form and can be changed by user.

Let's modify the data controller descriptor ~/Controllers/HR_DEPARMENTS.xml to use the sequence for primary key column values.

The first step is a definition of command that executes just before a new record is inserted into database.

<command id="GetSequence" type="Text" event="Inserting">
  <text><![CDATA[select departments_seq.nextval from dual]]></text>
  <output>
    <fieldOutput fieldName="DEPARTMENT_ID"/>
  </output>
</command>

Attribute event tells the framework about the timing of execution, element text defines the actual SQL statement, and element fieldOutput directs the framework to copy the first column in the first row of command result set to the DEPARTMENT_ID field.

The second and last step of modification is to hide the DEPARMENT_ID field from user interface.

<view id="createForm1" type="Form" commandId="command1" 
  label="New Hr Departments">
  <headerText>Please fill this form and click OK button to create 
    a new hr departments record. Click Cancel to return to the 
    previous screen.</headerText>
  <categories>
    <category headerText="New Hr Departments">
      <description>Complete the form. Make sure to enter all 
        required fields.</description>
      <dataFields>
        <dataField fieldName="DEPARTMENT_ID" columns="15" hidden="true"/>
        <dataField fieldName="DEPARTMENT_NAME" columns="30" />
        <dataField fieldName="MANAGER_ID" aliasFieldName="MANAGER_LAST_NAME" />
        <dataField fieldName="LOCATION_ID" aliasFieldName="LOCATION_CITY" />
      </dataFields>
    </category>
  </categories>
</view>

Attribute hidden on the field DEPARMENT_ID does the job.

Here is how the form is changed.

The new department is displayed in the list below.

Data Aquarium Framework pr0vides a variety of presentation styles for lookup items in your grid and form views.

Consider the following screen shot fragment of products form view in application generated by Code OnTime Generator from Northwind database.

Supplier company name and category names are allowing user to select SupplierID and CategoryID in a products table row. If user clicks on a link with category name then the following lookup control is presented.

The eraser icon will clear up the selected category.

This advanced lookup control is displayed thanks to the following configuration of SupplierID and CategoryID fields in ~/Controllers/Products.xml data controller descriptor.

<field name="SupplierID" type="Int32" label="Supplier#">
  <items style="Lookup" dataController="Suppliers" 
    newDataView="createForm1" />
</field>
<field name="SupplierCompanyName" type="String" readOnly="true" 
  label="Supplier Company Name" />
<field name="CategoryID" type="Int32" label="Category#">
  <items style="Lookup" dataController="Categories" newDataView="createForm1" />
</field>
<field name="CategoryCategoryName" type="String" readOnly="true" 
  label="Category Name" />

Style of items is set to Lookup.

The source of items is identified by dataController attribute that refers to a corresponding data controller descriptor.

You can change the style of items for CategoryID field by replacing Lookup with DropDownList.

This will translate into the following presentation.

Notice that N/A option is automatically added to the list since CategoryID field allows null values to be stored in the table column.

Here is how ListBox item presentation style is rendered.

Another popular option is to have lookup items displayed as a radio button list.

You can provide a more compact radio button list by adding columns property to the data field in editForm1 form view as shown in the snippet.

<dataField fieldName="CategoryID" aliasFieldName="CategoryCategoryName"  
  columns="3"/>

This is how the presentation of categories has changed.

Grid view is also sensitive to the item style and automatically displays the data as prescribed in data controller file. Here you can see a DropDownList style of the lookup items of CategoryID field.

A common requirement is to have a custom fixed list of values for certain fields in the database tables.

Redefine the items of CategoryID field as shown in the markup example below.

<field name="CategoryID" type="Int32" label="Category#">
  <items style="RadioButtonList">
    <item text="One" value="1"/>
    <item text="Two" value="2"/>
    <item text="Three" value="3"/>
  </items>
</field>

The presentation of the field will change to the following.

If you retain the dataController attribute then both static and dynamic items will show up in a list.

Note that dynamic and static items are always sorted in alphabetical order regardless of their nature.

Lookup style is not supported for static items.

Additional display style CheckBox is provided to support fields with boolean data type only.