Tutorial 22: How to use LOV

This tutorial explains how to build and use LOV in a Formspider Application. LOVs are input components that can display multiple columns of a datasource row and can optionally allow the user to search for the needed row. Through LOV, you can select multiple column values belonging to a same row and assign these values directly to components, datasource columns or even datasource bind variables.

Suppose you have a panel that allows the user to edit employee information. Instead of having a separate screen where the user first has to find the employee record to edit, LOV‘s search and select functionality can be built into the panel as decribed in this tutorial.

You may click here to run the completed application.

Open Formspider IDE and click “New Application” under the “File” menu. The “New Application” dialog will show up. Enter “LOVTutorial” 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 “LOVTutorial” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

Creating User Interface

As the first step, you will build a panel that allows the user to edit employee information. Double click the “mainPanel” under the “Panels” node of the “Containers” navigation tree to open it in the Editor. Your edit panel will contain “ID”, “First Name”, “Last Name”, “E-mail”, “Phone Number” and “Salary” information of an employee. You will add a textField component for displaying each of these information and a button labeled “Search…” which will open your LOV.

<panel>
  <tableLayout cellSpacing="5">
   <!-- ******************************************** -->
   <!-- Dummy row to adjust column widths -->
    <row height="0">
      <cell width="120"/>
      <cell/>
      <cell width="100"/>
    </row>
    <row height="10"/>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="ID:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_id"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="First Name:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_firstName"/>
      </cell>
      <cell hAlign="Full">
        <button label="Search..."/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="Last Name:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_lastName"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="E-mail:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_email"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="Phone Number:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_phone"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="Salary:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_salary"/>
      </cell>
    </row>
  </tableLayout>
</panel>

Close the editor tab belonging to “mainPanel” by pressing “x” icon on the editor tab.

Creating Model

As the second step, you will create the necessary Datasource Definition (DSD) for your LOV.
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 “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.

Next you will edit the query of your Datasource Definition for providing search functionality of your LOV. Click the “Query” node in the navigation tree on the left. Enter the following “Where Clause” to the DSD:

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

Entering Where Clause for Employees DSD.

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

Creating “first_name” bind variable

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

Creating LOV

As the next step, you will create your LOV. To achieve this, expand the “Miscellaneous” accordion panel, select the “LOVs” root node and click the “+” button from the toolbar of the accordion panel. Alternatively, you may right click the “LOVs” root node in the Navigation Tree and click the “New” menu item from the pop up menu that is displayed. The “New LOV” dialog shows up. Notice that in this dialog, there are three tabs labeled “Preferences”, “Columns” and “Search”. You will start from “Preferences” tab.

“Preferences” tab is the section where you can specify attributes belonging to user interface of your LOV.
Enter “LOV_employees” in the “Name” section, you will use this name later on in PL/SQL API calls.
Select “EMPLOYEES1” as “Datasource”, meaning that the LOV will use the “EMPLOYEES1” datasource for listing rows.
Enter “Employees” in the “Title” section, this title will be displayed as the title of LOV window.
Leave “OK Label”, “Cancel Label” and “Search Label” with their default values, these values are labels of OK, Cancel and Search buttons of your LOV.
Enter 450 as “Width” and 450 as “Height” , these values determine size of the LOV window.

“Preferences” tab

Next, you will navigate to “Columns” tab for specifying which columns of the “EMPLOYEES1” datasource will be used in your LOV.

Notice that when you first navigate to this tab, all columns belonging to “EMPLOYEES1” datasource are automatically listed. You will start by removing the columns which will not be displayed in your LOV or whose values will not be retrieved. Remember that you have to retrieve “ID”, “First Name”, “Last Name”, “E-mail”, “Phone Number” and “Salary” values from your LOV to set related component‘s values in your edit panel, so you need to keep EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER and SALARY columns. Furthermore suppose that you will display FIRST_NAME and LAST_NAME columns in your LOV. Consequently you will remove all columns except EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER and SALARY from the grid.

Buy using the arrow buttons existing at the right side of the dialog you can specify display sequence of the LOV columns, please adjust the order as shown in the screenshot below.
Since you will list only FIRST_NAME and LAST_NAME columns in your LOV uncheck “Visible” checkBox for all remaining columns except of these two to in order to hide them.
Enter “First Name” as label of the FIRST_NAME column and “Last Name” as label of the LAST_NAME column. Since other columns will not be displayed to the user you can leave their label as blank.

For all columns , choose “Component” as “Target Type”. This selection means that the value retrieved from related column will be assigned to a component. Notice that there is two additional option; “Bindvar” and “Datasource” meaning that the value retrieved from related column will be assigned to a datasource binn variable or datasource column.

“Target” is the section where you will specify the reference of your “Target Type”. So when you have selected “Component” as the “Target Type” you have to enter a component reference (in “panelName.componentName” format), when your “Target Type” is “Bindvar”, you have to enter a datasource bind variable reference (in “datasourceName.bindvarName” format) and finally when you have selected “Datasource” as the “Target Type” you have to enter a datasource column reference (in “datasourceName.columnName” format) in this section. Since in this tutorial you have selected “Component” as the “Target Type”, you will enter references to components of your edit panel as presented in the following table;

Column Target
FIRST_NAME mainPanel.txtField_firstName
LAST_NAME mainPanel.txtField_lastName
EMPLOYEE_ID mainPanel.txtField_id
EMAIL mainPanel.txtField_email
PHONE_NUMBER mainPanel.txtField_phone
SALARY mainPanel.txtField_salary

“Columns” tab

As the final step, navigate to “Search” tab for adding search functionality to your LOV. Notice that when you first navigate to this tab, the “first_name” bind variable that you have defined while creating your datasource defitinion has been automatically added to the grid. For each bind variable listed in this grid, a textField will be added automatically to your LOV allowing the user to filter LOV data. If you don’t want to display a search field for your “first_name” bind variable you can simply delete it from the grid.

Enter “First Name” as “Label”, this value will be used as the label of the search textField created for the bind variable.

“Search” tab

Click “OK” to save the changes and create your LOV.

You have to display your LOV when “Search…” button is pressed. To achieve this, you will create a Formspider action which will be fired when “Search…” button is pressed.

In your datasource schema, create a package called “lovtutorial_pkg” and open your newly created “lovtutorial_pkg” package in your favorite PL/SQL Editor. Add a procedure named “showEmployeesLOV” and ensure that the procedure is exposed in the package specification. This procedure uses api_lov.show API to display your LOV.

procedure showEmployeesLOV is
begin
  api_lov.show('LOV_employees');
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 “showEmployeesLOV” as the action name and “lovtutorial_pkg.showEmployeesLOV” as the procedure. Click “OK” to save your action.

Creating “showEmployeesLOV” action

Double click the “mainPanel” to open it in the editor. Add a buttonPress event to the “Search…” button triggering the “showEmployeesLOV” action, the panel XML should look like;

<panel>
  <tableLayout cellSpacing="5">
   <!-- ******************************************** -->
   <!-- Dummy row to adjust column widths -->
    <row height="0">
      <cell width="120"/>
      <cell/>
      <cell width="100"/>
    </row>
    <row height="10"/>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="ID:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_id">
          <targets>
            <targetOf LOV="LOV_employees" column="EMPLOYEE_ID"/>
          </targets>
        </textField>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="First Name:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_firstName">
          <targets>
            <targetOf LOV="LOV_employees" column="FIRST_NAME"/>
          </targets>
        </textField>
      </cell>
      <cell hAlign="Full">
        <button label="Search...">
          <events>
            <buttonPress action="showEmployeesLOV"/>
          </events>
        </button>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="Last Name:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_lastName">
          <targets>
            <targetOf LOV="LOV_employees" column="LAST_NAME"/>
          </targets>
        </textField>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="E-mail:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_email">
          <targets>
            <targetOf LOV="LOV_employees" column="EMAIL"/>
          </targets>
        </textField>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="Phone Number:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_phone">
          <targets>
            <targetOf LOV="LOV_employees" column="PHONE_NUMBER"/>
          </targets>
        </textField>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full">
        <textLabel label="Salary:"/>
      </cell>
      <cell hAlign="Full">
        <textField name="txtField_salary">
          <targets>
            <targetOf LOV="LOV_employees" column="SALARY"/>
          </targets>
        </textField>
      </cell>
    </row>
  </tableLayout>
</panel>

Run your application and press “Search…” button. When “Search…” button is pressed your LOV will be displayed. Note that “First Name” search field is displayed in the LOV. Enter “S” into this field and click “Search”, your data will be filtered as expected.

To select a row from the LOV you can double-click a row from the grid or alternatively you can press “OK” button after selecting a row from the grid. When you select a row from the LOV please notice that the textField components are filled with the values retrieved from your LOV as expected.

You may click here to run the application.

Created Application is running.