Tutorial 26: How to Create a Master/Detail Form

This tutorial explains how to create a master/detail form in Formspider. You will discover how to display multiple master rows and populate detail rows belonging to the selected master.

In this tutorial you will create a sample application containing two grids. The first grid will display departments information (master). The second grid will list employees (details) belonging to the selected department from the first grid.

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 “masterDetail” 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 “masterDetail” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

Creating the Model

As the first step you will create the datasource definition and datasource which will be used for displaying departments data. 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 a New datasource definition

The “New Datasource Definition” dialog will show up. Enter “DEPARTMENTS” 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 “DEPARTMENTS” datasource definition

For entering your SQL query click the “Query” node in the navigation tree on the left. Enter the following SQL Statement to the datasource definition:

select d.department_id
      ,d.department_name
      ,l.city
      ,l.state_province
      ,c.country_name
      ,e.first_name ||' '||e.last_name as manager
from departments d
    ,locations l
    ,employees e
    ,countries c
where d.location_id = l.location_id(+)
  and l.country_id = c.country_id(+)
  and d.manager_id = e.employee_id(+)
order by d.department_name

Entering Query for “DEPARTMENTS” datasource definition

Click “OK” to save and close the “New Datasource Definition” dialog. This will create the “DEPARTMENTS” datasource definition and the “DEPARTMENTS1″ datasource. You will use the “DEPARTMENTS1” datasource for listing departments information, meaning your master data.

As the second step you will create the datasource definition and datasource which will be used for populating employees data belonging to a single department.

Create a new datasource definition. This time 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”. Please note that the Formspider IDE automatically assigned the name “EMPLOYEES” to your datasource definition and picked the “EMPLOYEE_ID” as its primary key.

Creating “EMPLOYEES” datasource definition

Click the “Query” node in the navigation tree on the left. Enter the following “Where Clause” to the datasource definition:

DEPARTMENT_ID = :department_id

Notice that this  “Where Clause” establishes the master/detail relationship between departments and employees tables.

Next, enter the following “Order By Clause” to your datasource definition:

FIRST_NAME,LAST_NAME

Entering Where and Order By clauses for “EMPLOYEES” datasource definition

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

Creating “department_id” bind variable

Click “OK” to save and close the “New Datasource Definition” dialog. This will create the “EMPLOYEES” datasource definition and the “EMPLOYEES1″ datasource. You will use the “EMPLOYEES1” datasource for populating employees data belonging to the department specified by the “department_id” bind variable.

Creating the User Interface

As mentioned at the beginning of the tutorial your application will contain two grids displaying departments and its employees information. For this purpose you will create two separate panels, first one containing the grid displaying departments information and a second one listing employees belonging to the selected department from the first.

Expand the “Containers” accordion, select the “Panels” node in the navigation tree and click the “+” button to create a new 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 “departmentsPanel” as the name of the panel. Then, select “Titled Border” from “Border Type” combo box and enter “Departments” as “Border Title”. Click “OK” to create and open the panel in the editor.

Creating “departmentsPanel”

This panel will contain a grid displaying “Department Name”, “Manager Name”, “Country”, “State” and “City” information of all departments existing in HR schema using “DEPARTMENTS1” datasource that you have created earlier. Your panel XML should be as the following;

<panel borderType="TitledBorder" borderTitle="Departments">
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="DEPARTMENTS1">
          <column headerLabel="Department">
            <textField column="DEPARTMENT_NAME"/>
          </column>
          <column headerLabel="Manager">
            <textField column="MANAGER"/>
          </column>
          <column headerLabel="Country">
            <textField column="COUNTRY_NAME"/>
          </column>
          <column headerLabel="State">
            <textField column="STATE_PROVINCE"/>
          </column>
          <column headerLabel="City">
            <textField column="CITY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Next you will create a second panel for displaying employees of the selected department. Create a new panel, enter “employeesPanel” as the name of the panel. Then, select “Titled Border” from “Border Type” combo box and enter “Employees” as “Border Title”.

This panel will contain a grid displaying “First Name”, “Last Name”, “Email”, “Salary” and “Phone Number” information of the employees using “EMPLOYEES1” datasource. Your panel XML should be as the following;

<panel borderType="TitledBorder" borderTitle="Employees">
  <tableLayout>
    <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="Email">
            <textField column="EMAIL"/>
          </column>
          <column headerLabel="Salary">
            <textField column="SALARY"/>
          </column>
          <column headerLabel="Phone Number">
            <textField column="PHONE_NUMBER"/>
          </column>
          <column headerLabel="Hire Date">
            <dateField dateFormat="DD/MM/YYYY" column="HIRE_DATE"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

As the next step you will include these two newly created panels into “mainPanel” for displaying them on the “mainFrame”. Double click the “mainPanel” under the “Panels” node of the “Containers” navigation tree to open it in the editor and include the “departmentsPanel” and the “employeesPanel” panel to the “mainPanel”:

<panel>
  <tableLayout>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <include panelName="departmentsPanel"/>
      </cell>
    </row>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <include panelName="employeesPanel"/>
      </cell>
    </row>
  </tableLayout>
</panel>

Expand the “Windows” node of the “Containers” navigation tree and double click the “mainFrame” node under the “Mainframe” node. Enter “800” as “width”;

<mainframe title="masterDetail" width="800" height="500">
  <borderLayout>
    <cell docking="Center">
      <include panelName="mainPanel"/>
    </cell>
  </borderLayout>
</mainframe>

The last step of the tutorial is populating accurate employees data when a department is selected. To achieve this, you will create a Formspider action which will be fired when selected department row is changed.

In your datasource schema, create a package called “masterdetail_pkg” and open your newly created “masterdetail_pkg” package in your favorite PL/SQL Editor. Add a procedure named “populateEmployees” and ensure that the procedure is exposed in the package specification. This procedure uses  api_datasource.setBindvar API to set the value of “department_id” bind variable of the “EMPLOYEES1” datasource to the selected department’s id. Then, it uses api_datasource.executeQuery to populate employees data.

procedure populateEmployees is
  v_department_id number;
begin
  -- get id of the currently selected deparment
  v_department_id := api_datasource.getColumnValueNR('DEPARTMENTS1.DEPARTMENT_ID');
  -- populate employees belonging to the selected department
  api_datasource.setBindvar('EMPLOYEES1.department_id', v_department_id);
  api_datasource.executeQuery('EMPLOYEES1');
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 “populateEmployees” as the action name and “masterdetail_pkg.populateEmployees” as the procedure. Click “OK” to save your action.

Creating “populateEmployees” action

You will populate employees data when the selected(current) department row is changed. To accomplish this, you will use Current Row Changed event of the “DEPARTMENTS1” datasource.

Expand “Datasource Definitions” root node from “Datasource Definitions” accordion. Expand “DEPARMENTS” and “Datasources” nodes respectively and double-click “DEPARTMENTS1” node to edit “DEPARTMENTS1” datasource, the edit dialog is opened. Select “Events” tab and choose “populateEmployees” from “Current Row Changed” combobox. Click “OK” to save your change.

Assign “populateEmployees” action to “Current Row Changed” event of the “DEPARTMENTS1” datasource

Press “Run on Web” button for running your application. Please notice that when you change selected department by clicking or using arrow keys the employees data belonging to the selected department are listed.

You may click here to run the application.

Master/Detail relationship established

  • George

    Sir,

    I am seeing two problems here.

    1. The focus is lost at the first and last rows. When you are at the last row and if you try to go down to the next row the focus is lost. The same thing happens with the first row when you try to go to the previous record.

    2. When the focus is lost and if you try to navigate to the other rows the scrolling is taking place but the yellow row selection is missing. Focus once lost means always lost.

    Both the above problems can be solved by reassigning focus by clicking on any row.

    This problem exists in the DE as well. But that is not a big issue because a developer can understand it and secondly in the DE there are not many grids to work with. But the end-user almost always work with the grid and we cannot just say, we are sorry and escape. They will just stop using our system.

    George Rajan from Dubai, UAE

    • İbrahim Sandallı

      Hi George,

      Thanks for the feedback. I opened a ticket in our issue management system regarding these issues. I will update this thread when they are fixed.

      Regards,
      Ibrahim