Tutorial 2: How to build a search form

This tutorial explains how to build a search form using the Formspider Framework. You will build a Formspider Application that displays Employees based on filter criteria. The Application will present a search area where it will allow the users enter the criteria and a Grid that will display the Employees that match the criteria.

Open Formspider IDE and create a new Formspider Application named Employees. If you are using Formspider Local Installation, use the HR schema as the Datasource Schema. Click OK. If you are using Formspider Online, then you already have a schema assigned to your account. You can connect to this schema by using the connection information under Tools>User Settings in the IDE Menu.

Creating Employees Application

Formspider IDE creates and opens the Employees application for editing.

The Model

As the first step, you will create the necessary Datasource Definition (DSD) for your application.

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.

Creating the Employees Datasource Definition (DSD)

The New Datasource Definition Dialog will show up. Ensure that the Table radio button is selected and pick “EMPLOYEES” 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 “EMPLOYEES” to your DSD and picked the “EMPLOYEE_ID” as its Primary Key.

Filling properties of the Employees DSD.

Click the Query node in the navigation tree on the left. Enter the following Where Clause to the DSD:

first_name like '%'||:firstName||'%' or :firstName is null

Entering Where Clause for Employees DSD.

Click the Bind Variables node to register “firstName” bind variable we just entered to the Where Clause. Click New BindVar. A new row will appear in the Grid. Enter “firstName” to the Name column and select “DefaultVarchar2″ as the Domain value.

Creating a new Bind Variable for Employees DSD.

Click OK to save and close the “New Datasource Definition Dialog”. This will create the “EMPLOYEES” DSD and the “EMPLOYEES1″ Datasource.

The User Interface

At this step, you will create the necessary UI Components for your application; a simple search form and a Grid that will display the results of the search.

Expand the Containers accordion Panel, select the Panels node in the navigation tree and click the “+” button to create a new Simple Panel. Alternatively, you may right click the Panels node and select the “New” menu item from the pop-up menu. The New Panel Dialog will show up. Enter “searchPanel” as the name of the Panel. Select “TitledBorder” as the Border Type and enter “Search” as the Border Title. Click OK to create and open the Panel in the Editor.

Creating a new Panel to be used for searching.

Add a textLabel with the value “First Name: “, a textField component named “firstName” and a buttonlabeled “Search” to the “searchPanel”.

<panel borderType="TitledBorder" borderTitle="Search">
  <tableLayout>
    <row>
      <cell>
        <textLabel label="First Name: " text-align="Right"/>
      </cell>
      <cell hAlign="Left">
        <textField name="firstName"/>
      </cell>
      <cell>
        <button label="Search"/>
      </cell>
    </row>
  </tableLayout>
</panel>

Click “Save” to commit your changes.

Double click the “mainPanel” under the Panels node of the Containers navigation tree to open it in the Editor. Using borderLayout, add a Grid component to the center of the “mainPanel”. Select the “EMPLOYEES1″ as its datasource. In the Grid, display the “FIRST_NAME” and “LAST_NAME” columns from the Datasource. Next, include the “searchPanel” to the “North” of the “mainPanel” with aheight of 40 pixels.

<panel>
  <borderLayout>
    <cell docking="North" height="40">
      <include panelName="searchPanel"/>
    </cell>
    <cell docking="Center">
      <grid dataSource="EMPLOYEES1">
        <column headerLabel="First Name">
          <textLabel column="FIRST_NAME"/>
        </column>
        <column headerLabel="Last Name">
          <textLabel column="LAST_NAME"/>
        </column>
      </grid>
    </cell>
  </borderLayout>
</panel>

Click OK to save.

The Code

Connect to the “HR” schema in your favorite PL/SQL Editor. Create a package called “EMPLOYEES_PKG” with a procedure named “doSearch”. In the procedure, populate the “EMPLOYEES1.firstName” bind variable with the value in the “searchPanel.firstName” component.Then run the query of the Datasource “EMPLOYEES1″.

CREATE OR REPLACE
PACKAGE EMPLOYEES_PKG AS
  procedure doSearch;
END;
CREATE OR REPLACE
PACKAGE BODY EMPLOYEES_PKG AS
procedure doSearch is
begin
  api_datasource.setBindVar('EMPLOYEES1.firstName',
  api_component.getValueTx('searchPanel.firstName'));
  api_datasource.executeQuery('EMPLOYEES1');
end;
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” will show up. Enter “doSearch” as the action name and “employees_pkg.doSearch” as the procedure. Click OK to save your action.

Creating a new Action.

Double-click and open the “searchPanel” in the Editor. Add a buttonPress event to the “Search” button and call the “doSearch” action.

<panel borderType="TitledBorder" borderTitle="Search">
  <tableLayout>
    <row>
      <cell>
        <textLabel label="First Name: " text-align="Right"/>
      </cell>
      <cell hAlign="Left">
        <textField name="firstName"/>
      </cell>
      <cell>
        <button label="Search">
          <events>
            <buttonPress action="doSearch"/>
          </events>
        </button>
      </cell>
    </row>
  </tableLayout>
</panel>

Click Save to commit your changes.

Run the application. Note that the application displays all the employees when it opens. Use the “First Name” field in the “Search Box” to filter the rows.

Created Application is running.

  • Literate Aspects

    It would also be great is a PDF of each lesson were here to download as an ongoing manual to print and make written notes.

  • Literate Aspects

    Excellent tutorial, it jumps from the IDE to the SQL Editor – which was not previously announced that the editor would be needed.