Tutorial 39: How to use Row & Cell Level Grid Attributes

This tutorial describes how to use row level and cell level grid attributes.

Row level grid attributes are data-driven attributes which allow you to assign a specific attribute value to every single row existing in the grid, affecting all components existing in that row. Since row level attributes are data-driven, you should bind a datasource column in order to use these attributes.

Row level grid attributes are listed below;

Attribute Definition
editableColumn editable state of all components existing in the row
fontSizeColumn font size of all components existing in the row
fontColorColumn font color of all components existing in the row
fontFamilyColumn font family of all components existing in the row
fontStyleColumn font style of all components existing in the row
rowHeightColumn height of the row
backgroundColorColumn background color of the row (Attention, it’s different from component’s (cell’s) background color)

Cell level grid attributes are also data-driven attributes which allow you to assign a specific attribute value to every single cell existing in a specific column of the grid, affecting only the component existing in that column. Since cell level attributes are data-driven, you should bind a datasource column in order to use these attributes.

Cell level attributes are listed below;

Attribute Definition
tooltipColumn tooltip of the component existing in the cell
backgroundColorColumn background color of the component existing in the cell

This tutorial explains how to build the grid presented on the screenshot above using row and cell level grid attributes.

You may click here to run the completed application.

Open Formspider IDE and click “New Application” under the “File” menu. The “New Application” dialog shows up. Enter “rowAndCellLevelGridAttributes” as the name of the application and “HR” as its Datasource Schema name.  If you are using Formspider Online, you do not need to enter database schema name since your account is already configured to use your online database. Leave other fields empty and click “OK”. This creates the “rowAndCellLevelGridAttributes” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

Creating Model

Create the necessary Datasource Definition for your grid. Expand the Datasource Definitions accordion, select the “Datasource Definitions” node and click the “+” button. Alternatively, you may right click the “Datasource Definitions” node and select “New” from the pop up menu. The “New Datasource Definition” Dialog shows up. Enter “EMPLOYEES” as “Name” and select “Query” radio button from “Based On” section, this selection means that you will enter a SQL query for retrieving data from the HR schema.

Creating “EMPLOYEES” datasource definition

Select the “Query” node in the navigation tree on the left. Enter the following “SQL Statement” to the datasource definition, note that this query retrieves employees information hierarchically:

select e.first_name
      ,e.last_name
      ,e.salary
from employees e
start with e.manager_id is null
connect by e.manager_id = prior e.employee_id
order siblings by e.first_name, e.last_name

Entering Query for “EMPLOYEES” datasource definition

Click “OK” to save and close the “New Datasource Definition” dialog. This creates the “EMPLOYEES” datasource definition and the “EMPLOYEES1″ datasource. You will use the “EMPLOYEES1” datasource as the datasource of your grid.

Creating User Interface

Double click the “mainPanel” under the “Panels” node of the “Containers” navigation tree to open it in the editor. Add a grid displaying “FIRST NAME”, “LAST NAME” and “SALARY” information of the employees using “EMPLOYEES1” datasource that you have created recently. The panel XML looks like;

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Press “Run on Web” button to run the application. Note that employees data is displayed hierarchically as expected.

Employees data hierarchically displayed

Using editableColumn attribute

Assume that as a business requirement, data belonging to managers must be uneditable while the rest of the employees data should remain editable. To satisfy this requirement, use editableColumn attribute of the grid component.

Add a new column named “editable” to the “EMPLOYEES” datasource definition with ‘N’ or ‘Y’ as value depending whether the row belongs to a manager or not. To achieve this, expand the “Datasource Definitions” node and double-click “EMPLOYEES” node, datasource definition edit dialog shows up. Select the “Query” node in the navigation tree on the left and enter the following statement:

select e.first_name
      ,e.last_name
      ,e.salary
      ,decode(connect_by_isleaf, 1, 'Y', 'N') as editable
from employees e
start with e.manager_id is null
connect by e.manager_id = prior e.employee_id
order siblings by e.first_name, e.last_name

Click “OK” to save your changes. Edit “mainPanel” XML and assign the newly created “editable” column to the “editableColumn” attribute of the grid component.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1" editableColumn="EDITABLE">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, note that the grid rows belonging to managers cannot be edited anymore.

Grid rows belonging to managers cannot be edited

Using fontSizeColumn, fontColorColumn, fontFamilyColumn and fontStyleColumn attributes

Use the fontSizeColumn, fontColorColumn, fontFamilyColumn and fontStyleColumn attributes to improve the visual distinction between the manager employees and other employees by providing different font attributes for each separate group.

Add four new columns named “fontSize”, “fontColor”, “fontFamily” and “fontStyle” to the “EMPLOYEES” datasource definition with 12 or 10 as “fontSize”, Navy or Green as “fontColor”, Courier or Arial as “fontFamily” and Bold or Italic as “fontStyle” values depending whether the row belongs to a manager or not. To achieve this, change the SQL statement of the “EMPLOYEES” datasource definition as the following:

 select e.first_name
      ,e.last_name
      ,e.salary
      ,decode(connect_by_isleaf, 1, 'Y', 'N') as editable
      ,decode(connect_by_isleaf, 1, 10, 12) as fontSize
      ,decode(connect_by_isleaf, 1, 'Green', 'Navy') as fontColor
      ,decode(connect_by_isleaf, 1, 'Arial', 'Courier') as fontFamily
      ,decode(connect_by_isleaf, 1, 'Italic', 'Bold') as fontStyle
from employees e
start with e.manager_id is null
connect by e.manager_id = prior e.employee_id
order siblings by e.first_name, e.last_name

Click “OK” to save your changes. Edit “mainPanel” XML and assign the newly created “fontSize”, “fontColor”, “fontFamily” and “fontStyle” columns to the “fontSizeColumn”, “fontColorColumn”, “fontFamilyColumn” and “fontStyleColumn” attributes of the grid component.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1" editableColumn="EDITABLE" fontSizeColumn="FONTSIZE" fontColorColumn="FONTCOLOR" fontFamilyColumn="FONTFAMILY" fontStyleColumn="FONTSTYLE">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, note that the font attributes of the grid rows belonging to managers and to other employees are set as excepted.

Different font attributes are used for managers and other employees

Using rowHeightColumn and row level backgroundColorColumn attributes

rowHeightColumn attribute changes the height of the grid rows. It’s commonly combined with backgroundColorColumn attribute to decorate the grid component.

Add two new columns named “rowHeight” and “bgColor” to the “EMPLOYEES” datasource definition with 40 or 25 as “rowHeight” and Navy or Gray as “bgColor” values depending whether the row belongs to a manager or not. To achieve this, change the SQL statement of the “EMPLOYEES” datasource definition as the following:

select e.first_name
      ,e.last_name
      ,e.salary
      ,decode(connect_by_isleaf, 1, 'Y', 'N') as editable
      ,decode(connect_by_isleaf, 1, 'Italic', 'Bold') as fontStyle
      ,decode(connect_by_isleaf, 1, 'Arial', 'Courier') as fontFamily
      ,decode(connect_by_isleaf, 1, 10, 12) as fontSize
      ,decode(connect_by_isleaf, 1, 'Green', 'Navy') as fontColor
      ,decode(connect_by_isleaf, 1, 25, 40) as rowHeight
      ,decode(connect_by_isleaf, 1, 'Gray', 'Navy') as bgColor
from employees e
start with e.manager_id is null
connect by e.manager_id = prior e.employee_id
order siblings by e.first_name, e.last_name

Click “OK” to save your changes. Edit “mainPanel” XML and assign the newly created “rowHeight” and “bgColor” columns to the “rowHeightColumn” and “backgroundColorColumn” attributes of the grid component.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1" editableColumn="EDITABLE" fontSizeColumn="FONTSIZE" fontColorColumn="FONTCOLOR" fontFamilyColumn="FONTFAMILY" fontStyleColumn="FONTSTYLE" rowHeightColumn="ROWHEIGHT" backgroundColorColumn="BGCOLOR">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, note that the height of the rows are changed and the background of the rows are painted with the specified colors.

Height and background color of the rows are changed

Using tooltipColumn

tooltipColumn allows you to display a different tooltip on every cell belonging to a specific column of the grid.

Add a new column named “hierarchyTooltip” holding the path representing employee hierarchy using last_name column of the employees table. To achieve this, change the SQL statement of the “EMPLOYEES” datasource definition as the following:

select e.first_name
      ,e.last_name
      ,e.salary
      ,decode(connect_by_isleaf, 1, 'Y', 'N') as editable
      ,decode(connect_by_isleaf, 1, 'Italic', 'Bold') as fontStyle
      ,decode(connect_by_isleaf, 1, 'Arial', 'Courier') as fontFamily
      ,decode(connect_by_isleaf, 1, 10, 12) as fontSize
      ,decode(connect_by_isleaf, 1, 'Green', 'Navy') as fontColor
      ,decode(connect_by_isleaf, 1, 25, 40) as rowHeight
      ,decode(connect_by_isleaf, 1, 'Gray', 'Navy') as bgColor
      ,'Employee Hierarchy: ' || sys_connect_by_path(last_name, '/') as hierarchyTooltip
from employees e
start with e.manager_id is null
connect by e.manager_id = prior e.employee_id
order siblings by e.first_name, e.last_name

Click “OK” to save your changes. Edit “mainPanel” XML and assign the newly created “hierarchyTooltip” column to the “tooltipColumn” attribute of the textField component holding the “LAST_NAME” values.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1" editableColumn="EDITABLE" fontSizeColumn="FONTSIZE" fontColorColumn="FONTCOLOR" fontFamilyColumn="FONTFAMILY" fontStyleColumn="FONTSTYLE" rowHeightColumn="ROWHEIGHT" backgroundColorColumn="BGCOLOR">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME" tooltipColumn="HIERARCHYTOOLTIP"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application, note that the tooltip is displayed whenever the cursor hovers over the textField holding LAST NAME value of the employee.

A tooltip is displayed whenever the cursor hovers over the textField holding LAST NAME value

Using cell level backgroundColorColumn attribute

backgroundColorColumn allows you to change background color of the cells belonging to a specific column of the grid. It’s commonly used to highlight specific cells of a grid column.

Add a new column named “salaryBGColor” with Red or null as value depending if the employee salary is below 3000 or not. Note that null value is used to conserve the default behavior (default background color) of the cell. To achieve this, change the SQL statement of the “EMPLOYEES” datasource definition as the following:

select e.first_name
      ,e.last_name
      ,e.salary
      ,decode(connect_by_isleaf, 1, 'Y', 'N') as editable
      ,decode(connect_by_isleaf, 1, 'Italic', 'Bold') as fontStyle
      ,decode(connect_by_isleaf, 1, 'Arial', 'Courier') as fontFamily
      ,decode(connect_by_isleaf, 1, 10, 12) as fontSize
      ,decode(connect_by_isleaf, 1, 'Green', 'Navy') as fontColor
      ,decode(connect_by_isleaf, 1, 25, 40) as rowHeight
      ,decode(connect_by_isleaf, 1, 'Gray', 'Navy') as bgColor
      ,'Employee Hierarchy: ' || sys_connect_by_path(last_name, '/') as hierarchyTooltip
      ,case when salary >= 3000 then null else 'Red' end as salaryBGColor
from employees e
start with e.manager_id is null
connect by e.manager_id = prior e.employee_id
order siblings by e.first_name, e.last_name

Click “OK” to save your changes. Edit “mainPanel” XML and assign the newly created “salaryBGColor” column to the “backgroundColorColumn” attribute of the textField component holding “SALARY” value.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1" editableColumn="EDITABLE" fontSizeColumn="FONTSIZE" fontColorColumn="FONTCOLOR" fontFamilyColumn="FONTFAMILY" fontStyleColumn="FONTSTYLE" rowHeightColumn="ROWHEIGHT" backgroundColorColumn="BGCOLOR">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME" tooltipColumn="HIERARCHYTOOLTIP"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY" backgroundColorColumn="SALARYBGCOLOR"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run your application again, note that salary cells containing a value below 3000 are highlighted with a red background color.

Salary cells containing a value below 3000 are highlighted

Updating the value of row & cell level attributes

It’s always possible to change the value of row and cell level attributes by setting related datasource column values. Formspider automatically applies any change made on this attributes to the grid component by updating visual aspects of the necessary rows and cells.

Assume that when the user changes the salary value existing in a cell, if the new salary value is below 3000, it’s required to update the background of this cell and set as Red. To achieve this, you have to update the “salaryBGColor” column value of the “EMPLOYEES1” datasource.

In your datasource schema, create a package called  “rowAndCellLevelAttributes_pkg” and open your newly created “rowAndCellLevelAttributes_pkg” package in your favorite PL/SQL Editor. Add a procedure named “salaryChanged” and ensure that it’s exposed in the package specification. This procedure checks the new salary value and sets the value of the “salaryBGColor” column for the datasource row bound to the grid row which contains the updated cell.

procedure salaryChanged is
  v_newSalary_nr number;
begin
  v_newSalary_nr := api_datasource.getcolumnvaluenr('EMPLOYEES1.SALARY');
  -- check new value and set grid cell background color
  api_datasource.setcolumnvalue(
                                'EMPLOYEES1.SALARYBGCOLOR'
                                ,case
                                   when v_newSalary_nr >= 3000 then
                                     null
                                   else
                                     'Red'
                                  end
                                );
end salaryChanged;

In Formspider IDE expand the “Actions” accordion, select the “Actions” node, click the “+” button to create a new action. Alternatively you may right click the “Actions” node and select “New” from the pop-up menu. The “New Action” dialog shows up. Enter “salaryChanged” as the action name and “rowAndCellLevelAttributes_pkg.salaryChanged” as the procedure. Click “OK” to save your action.

Creating “salaryChanged” action

Open the “mainPanel”, add a “valueChanged” event triggering “salaryChanged” action to the textField holding “SALARY” value.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1" editableColumn="EDITABLE" fontSizeColumn="FONTSIZE" fontColorColumn="FONTCOLOR" fontFamilyColumn="FONTFAMILY" fontStyleColumn="FONTSTYLE" rowHeightColumn="ROWHEIGHT" backgroundColorColumn="BGCOLOR">
          <column headerLabel="FIRST NAME">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="LAST NAME">
            <textField column="LAST_NAME" tooltipColumn="HIERARCHYTOOLTIP"/>
          </column>
          <column headerLabel="SALARY" width="20%">
            <textField column="SALARY" backgroundColorColumn="SALARYBGCOLOR">
              <events>
                <valueChanged action="salaryChanged"/>
              </events>
            </textField>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application again, note that when you change the salary value of a cell, its background color changes depending the new value.

You may click here to run the application.

Cell's background color changes depending the new salary value