Tutorial 42: How to use When Current Row is Deleted and Force Current Row

This tutorial describes how to use When Current Row is Deleted and Force Current Row attributes of the datasource.

Open Formspider IDE and click “New Application” under the “File” menu, the “New Application” dialog shows up. Enter “forceCurrentRow” as the name of the application and “HR” as 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 “forceCurrentRow” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

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 will show up. Ensure that the “Table” radio button is selected and pick “REGIONS” table from the combo box. Note that the combo box shows a list of all tables in your Datasource Schema “HR”. Also ensure that the “Create Datasource” checkbox is ticked. Please note that the Formspider IDE automatically assigned the name “REGIONS” to your datasource definition and picked the “REGION_ID” as its primary key.

Creating “REGIONS” datasource definition

Click “OK” to save and close the “New Datasource Definition” dialog. This creates the “REGIONS” datasource definition and the “REGIONS1″ datasource.

Using When Current Row is Deleted Attribute

The When Current Row is Deleted attribute determines whether the row above or below of the current row will be automatically selected as the new current row of the datasource by Formspider or not when the current row of this datasource is deleted. The valid values for this attribute are listed below;

Value Definition
None Formspider does not select another row as the new current row, it’s the default value of this attribute
Move Up Formspider automatically selects the first row above the deleted row as the new current row of the datasource. If a row above the deleted row does not exist, Formspider selects the first row below the deleted row
Move Down Formspider automatically selects the first row below the deleted row as the new current row of the datasource. If a row below the deleted row does not exist, Formspider selects the first row above the deleted row

Expand the “Panels” node from the “Containers” tree and double click the “mainPanel” to open it in the editor. Add a grid component displaying “Region ID” and “Region Name” using “REGIONS1” datasource that you have created recently. Also, add a button labeled “Delete Current Row” which deletes the currently selected row from the “REGIONS1” datasource. “mainPanel” XML should look like the following;

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full">
        <grid dataSource="REGIONS1">
          <column headerLabel="Region ID">
            <textField column="REGION_ID"/>
          </column>
          <column headerLabel="Region Name">
            <textField column="REGION_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Delete Current Row"/>
      </cell>
    </row>
  </tableLayout>
</panel>

To delete the current row of the “REGIONS1” datasource when the “Delete Current Row” buton is pressed, you have to create a Formspider action which will be fired when this button is pressed.

In your datasource schema, create a package called “forceCurrentRow_pkg” and open your newly created “forceCurrentRow_pkg” package in your favorite PL/SQL Editor. Add a procedure named “deleteCurrentRow” and ensure that the procedure is exposed in the package specification. This procedure uses api_datasource.deleteRow API to delete the current row of the “REGIONS1” datasource.

procedure deleteCurrentRow is
begin
  api_datasource.deleterow('REGIONS1');
end;

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 “deleteCurrentRow” as the action name and “forceCurrentRow_pkg.deleteCurrentRow” as the procedure. Click “OK” to save your “action”.

Creating “deleteCurrentRow” action

Open the “mainPanel” in the editor. Add a buttonPress event to the “Delete Current Row” button triggering the “deleteCurrentRow” action, the panel XML should look like;

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full">
        <grid dataSource="REGIONS1">
          <column headerLabel="Region ID">
            <textField column="REGION_ID"/>
          </column>
          <column headerLabel="Region Name">
            <textField column="REGION_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Delete Current Row">
          <events>
            <buttonPress action="deleteCurrentRow"></buttonPress>
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Press “Run on Web” button to run the application. Select the row where the region name is “Asia” and press “Delete Current Row” button. Note that the current row is deleted and since you have not changed the value of the When Current Row is Deleted attribute, the default value “None” is used and Formspider does not select another row as the new current row.

Since When Current Row is Deleted value is “None”, Formspider does not select another row as the new current row when the current row is deleted

Expand the “Datasource Definitions” node, then “REGIONS” node and “Datasources” node to display the “REGIONS1” node on the tree. Double click the “REGIONS1” node to edit the “REGIONS1” datasource. Select “Move Up” from the “When current row is deleted” combobox and click “OK” to save your change.

Select “Move Up” as When Current Row is Deleted value

Run the application again, select the row where the region name is “Asia” and press “Delete Current Row” button. Note that this time, Formspider automatically selects the first row above the deleted row (with “Americas” as “Region Name”) as the new current row of the datasource.

Since When Current Row is Deleted value is “Move Up”, Formspider automatically selects the first row above the deleted row as the new current row of the datasource

Using Force Current Row Attribute

The Force Current Row attribute guaranties the existence of a current row in the datasource. In order to use Force Current Row attribute, it’s required to set “Move Up” or “Move Down” as the value of the When Current Row is Deleted attribute. The default value of the Force Current Row attribute is “N”, meaning that this property will not be used. When Force Current Row is used, Formspider automatically creates a new row in the datasource and set this row as current in the following cases;

  • when all rows existing in the datasource are deleted
  • when the datasource is cleared through api_datasource.clear API
  • when the datasource‘s query doesn’t retrieve any row
  • when api_datasource.nextRow API is used while the current row of the datasource is the last row existing in this datasource

Run the application again and press “Delete Current Row” button until all rows are deleted. Note that when all rows are deleted, since Force Current Row attribute is not used, there is no row left in the datasource, this is the default behavior.

Since Force Current Row is not used, no row left in the datasource when all rows are deleted

Double click to the “REGIONS1” node to edit the “REGIONS1” datasource. Tick “Force Current Row” checkBox to use the Force Current Row attribute.

Tick “Fprce Current Row” checkBox

Run the application and delete all rows again. Note that this time, since the Force Current Row attribute is used, Formspider automatically creates a new row and sets it as current.

Since Force Current Row is used, Formspider automatically creates a new row and sets it as current

Open “forceCurrentRow_pkg” package and create a procedure named “clear”. This procedure uses api_datasource.clear API to clear the “REGIONS1” datasource.

procedure clear is
begin
  api_datasource.clear('REGIONS1', api_datasource.clearAll);
end;

Create a new action, enter “clear” as the action name and “forceCurrentRow_pkg.clear” as the procedure. Click “OK” to save your action. Open the “mainPanel”, add a button labeled “Clear” and add a buttonPress event to this button, triggering “clear” action;

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full">
        <grid dataSource="REGIONS1">
          <column headerLabel="Region ID">
            <textField column="REGION_ID"/>
          </column>
          <column headerLabel="Region Name">
            <textField column="REGION_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Delete Current Row">
          <events>
            <buttonPress action="deleteCurrentRow"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Clear">
          <events>
            <buttonPress action="clear"/>
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application and press “Clear”. Note that Formspider automatically creates a new row and sets it as current again.

Formspider automatically creates a new row and sets it as current when the datasource is cleared

Double click the “REGIONS” node under the “Datasource Definitions” node to open the edit dialog. Click the “Query” node in the navigation tree on the left. Enter the following “Where Clause” to the datasource definition:

REGION_ID = :region_id or :region_id is null

Entering where clause to the “REGIONS” datasource definition

Click the “Bind Variables” node to register “region_id” bind variable we just entered to the “Where Clause”. Click “New BindVar” button, a new row appears in the grid. Enter “region_id” to the “Name” column and select “DefaultNumber″ as the “Domain” value.

Creating “region_id” bind variable

Click “OK” to save your changes.

Open “forceCurrentRow_pkg” package and create a procedure named “executeQuery”. In order to assure that the “REGIONS1” datasource does not retrieve any row when it’s executed, this procedure sets the value of the region_id bind variable to -1 and then executes the “REGIONS1” datasource using api_datasource.executeQuery API.

procedure executeQuery is
begin
  api_datasource.setbindvar('REGIONS1.region_id', -1);
  api_datasource.executequery('REGIONS1');
end;

Create a new action, enter “executeQuery” as the action name and “forceCurrentRow_pkg.executeQuery” as the procedure. Click “OK” to save your action. Open the “mainPanel”, add a button labeled “Execute Query” and add a buttonPress event to this button, triggering “executeQuery” action.

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full">
        <grid dataSource="REGIONS1">
          <column headerLabel="Region ID">
            <textField column="REGION_ID"/>
          </column>
          <column headerLabel="Region Name">
            <textField column="REGION_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Delete Current Row">
          <events>
            <buttonPress action="deleteCurrentRow"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Clear">
          <events>
            <buttonPress action="clear"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Execute Query">
          <events>
            <buttonPress action="executeQuery"/>
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application and press “Execute Query”. Note that since the datasource query does not retrieve any row Formspider automatically creates a new row and sets it as current again.

Formspider automatically creates a new row and sets it as current when the datasource query does not retrieve any row

Open “forceCurrentRow_pkg” package and create a procedure named “nextRow”. This procedure uses api_datasource.nextRow API to set the first row below the current row as the new current row of the “REGIONS1” datasource.

procedure nextRow is
begin
  api_datasource.nextrow('REGIONS1');
end;

Create a new action, enter “nextRow” as the action name and “forceCurrentRow_pkg.nextRow” as the procedure. Click “OK” to save your action. Open the “mainPanel”, add a button labeled “Next Row” and add a buttonPress event to this button, triggering “nextRow” action;

<panel>
  <tableLayout cellSpacing="5">
    <row>
      <cell hAlign="Full">
        <grid dataSource="REGIONS1">
          <column headerLabel="Region ID">
            <textField column="REGION_ID"/>
          </column>
          <column headerLabel="Region Name">
            <textField column="REGION_NAME"/>
          </column>
        </grid>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Delete Current Row">
          <events>
            <buttonPress action="deleteCurrentRow"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Clear">
          <events>
            <buttonPress action="clear"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Execute Query">
          <events>
            <buttonPress action="executeQuery"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell childWidth="120">
        <button label="Next Row">
          <events>
            <buttonPress action="nextRow"/>
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Run the application, select the last row from the grid (with “Middle East and Africa” as region name) and press “Next Row”. Note that Formspider automatically creates a new row and sets it as current again.

Formspider automatically creates a new row and sets it as current when api_datasource.nextRow is called while the current row is the last row of the datasource