Easy Master/Detail

Build master/detail forms for business applications with Oracle Application Express 5.1 interactive grids.

May/June 2017

Master/detail relationships are omnipresent in business applications. From department/employees to country/cities, data is often categorized and presented with this one-to-many type of relationship. With the interactive grid, a new component in Oracle Application Express 5.1, you can now easily create editable grids that reflect master/detail relationships. And you can easily extend your application to represent any number of relationships—parent/child/grandchild—or even a master table and multiple detail tables.

In this Oracle Magazine article, you’re going to build a web application on top of a set of three tables: REGIONS, COUNTRIES, and LOCATIONS. COUNTRIES is a child table of REGIONS, and LOCATIONS is a child table of COUNTRIES. These tables are excerpted from the Oracle Database Sample Schemas, available on GitHub. Using Oracle Application Express 5.1, you will quickly create a single page to manage the data of all these tables, using interactive grids.

This article’s sample application is built in Oracle Application Express 5.1. If you’re not already running Oracle Application Express 5.1 or later locally, you can request a free workspace at apex.oracle.com. Alternatively, you can download from the Oracle Technology Network the Database App Development Virtual Machine, which includes a preconfigured Oracle Database 12c Release 2 Enterprise Edition database, Oracle Application Express 5.1, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler. You will also need to download and unzip the SQL script for this article to create the sample database objects.

Begin your exploration of Oracle Application Express 5.1 interactive grids by first creating the sample database objects of your application.

1. In a web browser, log in to Oracle Application Express, click the SQL Workshop icon, and then click the SQL Scripts icon.
2. Click the Upload button, choose the sample_tables_master_detail.sql file from your local computer, and click the Upload button.
3. Click the Run icon in the same row as the sample_tables_master_detail.sql script, and then click the Run Now button.

You’ve now created the REGIONS, COUNTRIES, and LOCATIONS tables in your schema and populated them with data.

Begin your exploration of master/detail support in Oracle Application Express 5.1 by creating the initial application.

1. In Oracle Application Express, click the App Builder tab.
2. Click the Create icon, and then click the Desktop icon.
3. Enter Data Management for Name, and click Next.
4. Click the x in the Home Page row to remove the default home page.
5. Click the Add Page button.
6. Click the Master Detail icon, select REGIONS for Master Table Name, select COUNTRIES for Detail Table Name, and click Add Page.
7. Click the Create Application button, and then click the Create Application button again.

You’ve now created a responsive Oracle Application Express application with a fully functional, multiuser editable grid on the REGIONS table, along with a child editable grid on the COUNTRIES table, all accessible from a web browser.

Click the Run Application icon to run your application. Log in with the same credentials you used to log in to Oracle Application Express. Click each of the rows in the Region Name column of the Regions interactive grid to refresh the child interactive grid with the related rows. Enter text in the search field of the Regions and Countries interactive grids, and press the Enter key to filter the results.

Your application should look similar to Figure 1.


Figure 1: The initial master/detail application page

Improve the display of the page by refining the layout and visual appearance of the interactive grid regions.

With very wide columns and only one or two columns displayed per interactive grid, a significant amount of space on the application page is wasted. You can easily adjust the properties of the grid regions to dramatically improve the display.

1. In the Developer toolbar at the bottom of the page, click the Quick Edit link.
2. Move your cursor over the Save button, and when an outline appears over the button, click the button. Do not click the wrench icon in the upper right of the outline. (The wrench icon will be used in a later step.)
3. The tab running Application Builder will be displayed, and the Save button in the Regions interactive grid will be selected in Page Designer. In the visual page layout section of Page Designer, drag and drop the SAVE button from the Regions region to the EDIT position of the Breadcrumbs region.
4. In the visual layout area of Page Designer in the middle of the page, select the Countries region. In the properties of the Countries region on the far right, change Start New Row to No.
5. Click the Run icon in the upper right of Page Designer to run your page.

Your application page now presents the interactive grid regions side by side, with the Save button in the breadcrumbs bar. You’re getting there, but this application can be improved further.

Template options are declarative modifiers for the display of content on a page. The new live template options feature in Oracle Application Express 5.1 enables you to modify the look and feel of your content and see the results in real time.

1. In the Developer toolbar at the bottom of the page, click the Quick Edit link again. Hover your cursor over the Regions region, but this time, click the wrench icon in the upper right of the highlighted region. This will display the live template options for your region.
2. Select the checkbox for Remove Body Padding. Change Header to Hidden but accessible. Change Body Height to 320px, and then click the Save button.
3. Make the same changes to the Countries region. Click the Quick Edit link, hover the cursor over the Countries region, and click the wrench icon in the upper right. Select the checkbox for Remove Body Padding. Change Header to Hidden but accessible. Change Body Height to 320px, and then click the Save button.
4. Click the Run icon in the upper right of Page Designer to run your page.

The functionality of the interactive grids in your application page remains as before, but the presentation of information is much more condensed on the page. Your application should look similar to Figure 2.


Figure 2: The master/detail application page, condensed

Oracle Application Express supports an unlimited number of interactive grids on a single page. You will now add a third interactive grid, this time for the LOCATIONS table, and then manually adjust the properties so it is associated as a child of the COUNTRIES table.

1. In the Developer toolbar at the bottom of the page, click the Edit Page 1 link. This will present the tab in your browser running Application Builder.
2. In the Component Gallery at the bottom of the Page Designer page, select the Interactive Grid component and drag it below the Regions region in the Page Layout area, and drop it. The new region will be labeled New, and it will be highlighted in red, because you must supply one or more values to make it valid.
3. In the properties on the far right of Page Designer, for Title enter Locations.
4. In the SQL Query property, enter
   Copy code snippet

   Copied to Clipboard

   Error: Could not Copy

   Copied to Clipboard

   Error: Could not Copy

   select location_id, street_address, postal_code,
          city, state_province, country_code
     from locations

5. Click Attributes in the Locations region in the Component Tree on the left side of Page Designer.
6. In the Property Editor on the right, in the Edit region, change Enabled to Yes. This will change the interactive grid from a read-only reporting component to a fully editable grid.
7. Scroll down the list of properties, and in the Toolbar section, for Buttons, uncheck Save. You won’t need an additional Save button in this interactive grid, because you have one at the top of the page.
8. In the Component Tree, expand the Columns list below the Locations region. Select LOCATION_ID in the Component Tree.
9. In the Properties section on the right side of Page Designer, change Type to Hidden and change Primary Key to Yes. This instructs Oracle Application Express to not display this column in the interactive grid and to use the value of the column in this row for any data manipulation language (DML) operations (data modification actions).
10. Click the Run icon in the upper right.

Your application should look similar to Figure 3. You now have a page with three editable grids and can freely add, update, and delete rows in each of them. But the last remaining step for building this application is to specify the master/detail relationship between the Countries interactive grid and the Locations interactive grid.


Figure 3: The master/detail application with three editable grids

1. Click the Quick Edit link in the Developer toolbar. Hover your cursor over the Locations interactive grid and click. This will take you into Application Builder with the Locations region selected.
2. In the Property Editor on the right side of Page Designer, in the Master Detail section, select Countries for Master Region.
3. In the Component Tree, expand the Columns list below the Locations region and select COUNTRY_CODE. In the Properties section on the right side of Page Designer, in the Master Detail section, select COUNTRY_CODE for Master Column. This creates the association between the COUNTRY_CODE column of your Locations interactive grid and the COUNTRY_CODE column of the Countries interactive grid.
4. Click the Run icon in the upper right.

In the updated application page, click various rows in the Regions interactive grid. Select a row in the Countries interactive grid, and use the arrow keys on your keyboard to navigate up and down the list of rows. You will see the rows in the Locations interactive grid automatically refresh.

The new interactive grid component in Oracle Application Express 5.1 makes it very easy to represent a variety of data relationships in a responsive web application. With very little code, you are able to create editable grids directly on top of your tables, preserving the parent/child/grandchild relationships. Although there are alternative ways to present tables with master/detail relationships in Oracle Application Express, multiple interactive grids on a single page will be very recognizable to users of legacy client/server applications. A future Oracle Magazine article will discuss other powerful interactive grid features in depth.

DOWNLOAD

• Oracle Application Express 5.1.
• the SQL script for this article.

READ more about Oracle Application Express.

TRY Oracle Database Cloud Services.

REQUEST a free Oracle Application Express workspace.

Photography by Ricardo Gomez, Unsplash