Working with Master/Detail Data

Exercising the Master/Detail Grid Behavior

If you choose the Stacked pattern in the Master/Detail wizard, the resulting page has two editable, coordinated Interactive Grids. As shown below, clicking on a row in the master grid refreshes the detail grid to show only rows that reference the selected master row's primary key.

Notice the two Interactive Grid toolbars. Each has an (Add Row) button, but no (Save) button. Users can work with departments and employees, but cannot save each grid separately. Instead, the page-level (Save) button submits the page so changes from both grids get processed in the same transaction. If validation fails, no edits are saved.

The Interactive Grid's client-side tracking of rows to display and edit automatically handles the master/detail relationship as well. Users can add, update, and delete departments and employees, accumulating changes to many departments and many employees, then save all the changes together. As shown below, the user can even add a new department DESIGN and multiple new employees in the new department as part of their changes. The user can continue to navigate through master and detail rows to complete necessary changes, then submit them all by clicking the page-level (Save) button.

Understanding the Settings that Drive Master/Detail Grids

Understanding the Side by Side Master/Detail Page

A Side by Side Master/Detail page features a compact master-row search experience in the left column. In the wizard, you specify a Primary Display Column and Secondary Display Column for the master table. Users see both master column values and can search on them. They click any master row in the left column to see more information about it in the body of the page, along with related detail table rows for the selected master.

As shown in the figure below, the page uses the Left Side Column template. Its Left Column slot has a Search Static Content region containing a P4_SEARCH field and a Master Records Classic Report below it. This report uses the Media List theme template to show the primary and secondary display column values of the master rows as a formatted set of links.

The Body slot has a master Departments Classic Report using the Value Attribute Pairs - Column theme template. This shows all of the columns of the master row in a label/value layout. Below it, an Employees Classic Report details region uses the Standard theme template for a tabular view of the selected department's employees.

The (+ Create) button redirects to a Departments modal drawer Form page to enter a new department, and the (+) button redirects to an Employees modal drawer form page to enter a new employee in the selected department.

Refreshing Master Records When User Enters a Search

The P4_SEARCH field lets users enter text to filter the list of master rows. It uses a Perform Search Dynamic Action handler on the Key Press event to react when the user presses the [Enter] key. The handler only fires for this key by using the following JavaScript expression for its Client-side Condition:

this.browserEvent.which === apex.jQuery.ui.keyCode.ENTER

The handler contains two Dynamic Action steps to:

• Refresh the Master Records region, then
• Cancel Event

The query in the Master Records region uses :P4_SEARCH in its WHERE clause below to match on the DNAME and LOC columns if a search is provided. The region mentions P4_SEARCH in its Page Items to Submit property as well. This ensures APEX sends the latest value of the search field to the server when refreshing the Master Records region.

select "DEPTNO",
    ⋮
from "DEPT" x
where (:P4_SEARCH is null
        or upper(x."DNAME") like '%'||upper(:P4_SEARCH)||'%'
        or upper(x."LOC") like '%'||upper(:P4_SEARCH)||'%'
    )
order by "DNAME"

Using a Media List Classic Report

As shown below, the Master Records Classic Report region uses a Media List report template to format the master rows' primary and secondary display fields as links.

Whenever you're curious to learn more about what a particular template does, seek it out in App Builder's Shared Components > Templates page as shown below. Searching for Media List reveals it's a single Report template of Named Column type.

A Named Column report template specifies the HTML markup that repeats for each row in the data source, referencing specific column names using #NAME# substitutions. On the Row Templates tab, you find the HTML Markup as shown below.

Notice the LIST_CLASS, LINK, LIST_TITLE, and LIST_TEXT column substitutions in the row template HTML markup below. The LIST_CLASS is used to style each item in the list. The LINK provides the href URL of the anchor (<a>) tag, to define a link users can click on to select a particular master row. The LIST_TITLE and LIST_TEXT provide the visible text in the list of links.

<li class="t-MediaList-item #LIST_CLASS#">
  <a href="https://docs.oracle.com/en/database/oracle/apex/26.1/apxdc/showing-master-detail-data-side-side.html#%3Cspan%20class="bold">LINK#" class="t-MediaList-itemWrap #LINK_CLASS#" #LINK_ATTR#>
    <div class="t-MediaList-iconWrap" aria-hidden="true">
      <span class="t-MediaList-icon u-color #ICON_COLOR_CLASS#"
      ><span class="t-Icon #ICON_CLASS#"></span></span>
    </div>
    <div class="t-MediaList-body">
      <h3 class="t-MediaList-title">#LIST_TITLE#</h3>
      <p class="t-MediaList-desc">#LIST_TEXT#</p>
    </div>
    <div class="t-MediaList-badgeWrap">
      <span class="t-MediaList-badge">#LIST_BADGE#</span>
    </div>
  </a>
</li>

When using a Named Column template in a Classic Report, the query needs to contain columns whose names match the template's substitutions you want to supply. The Master Records region's query appears below. Notice it selects data from the master DEPT table, but aliases columns to LINK, LIST_CLASS, LIST_TITLE, and LIST_TEXT.

select "DEPTNO",
    ⋮
    apex_page.get_url(
        p_items  => 'P4_DEPTNO',
        p_values => "DEPTNO") LINK,
    ⋮
    case
      when coalesce(:P4_DEPTNO,'0') = "DEPTNO"
      then 'is-active'
      else ' '
    end                       LIST_CLASS,
    (substr("DNAME", 1, 50)||
       (case when length("DNAME") > 50
        then '...'
        else '' end ))        LIST_TITLE,
    (substr("LOC", 1, 50)||
       (case when length("LOC") > 50
        then '...'
        else '' end ))        LIST_TEXT,
    ⋮
from "DEPT" x
where (:P4_SEARCH is null
        or upper(x."DNAME") like '%'||upper(:P4_SEARCH)||'%'
        or upper(x."LOC")   like '%'||upper(:P4_SEARCH)||'%'
    )
order by "DNAME"

Tracking the Selected Master Row

The page contains a hidden P4_DEPTNO page item that tracks the primary key of the selected master row. Its Session State > Storage property is set to Per Session (Persistent) so the selected master key value remains available throughout the user's session. Notice the Master Records classic report query above includes a CASE expression that returns is-active for the LIST_CLASS column value when the current DEPTNO equals the :P4_DEPTNO value of the selected master row. Otherwise, the CASE statement returns NULL. Including this LIST_CLASS value in the Media List row template markup renders the currently selected master row with a different style than the other rows.

Notice the LINK column in the query as well. It calls apex_page.get_url to generate the URL to link to the current page, by omitting a p_page parameter, passing the current master row's DEPTNO value as the value of the P4_DEPTNO page item. In this example, the Side by Side Master/Detail page is page 4. So, when the user clicks one of the links in the Master Records list, the link redirects to the current page passing the clicked row's primary key. The page assigns the passed value to the hidden P4_DEPTNO field and re-renders the current page to show the newly selected master record, along with its detailed information.

Using a Value Attribute Pairs - Column Classic Report for Master Row

The Departments region in the Body slot displays the full information about the selected master row. Its WHERE clause references the hidden page item containing the primary key of the selected row:

DEPTNO = :P4_DEPTNO /* Filters on DEPT.DEPTNO */

It's also configured with a Server-side Condition that hides the region if :P4_DEPTNO is null.

To format the master row's column data in a Label/Value table, as shown in the figure below, the region uses the View Attribute Pairs - Column theme template and sets Pagination to No Pagination since this region will display a single row.

The Value Attribute Pairs - Column template is a Generic Columns type template. For each row in the report, it repeats the Column Template for each column in the row, with the ability to include appropriate HTML markup before and after each row. The column template uses generic substitutions #COLUMN_HEADER# and #COLUMN_VALUE# to include the header and value at an appropriate place in the markup. The result is a Label/Value row for each column in the report's data source.

Using a Standard Template Classic Report for Detail Rows

The Employees detail Classic Report uses the Standard template for a tabular result. It also includes the hidden :P4_DEPTNO in its WHERE clause. But in this case, it's filtering on the foreign key DEPTNO column of the detail region's EMP table.

DEPTNO = :P4_DEPTNO /* Filters on EMP.DEPTNO */

Creating and Editing a Master Row

The (Create) button in the breadcrumb bar redirects to a Departments form page, passing no parameters so it functions as a "Create New Department" page. The (Edit) button in the Departments region redirects to the same modal drawer page, but passes the value of the selected master record for its primary key page item. This results in presenting the edit page shown below.

Creating a Detail Row for the Selected Master

The (+) button in the details Employees region redirects to an Employees modal drawer form page to create a new employee, as shown below. Notice the create form opens with the Employees form's Deptno field already set to the currently selected master row. This happens because the (+) button's link target passes the hidden P4_DEPTNO value to the foreign key page item P7_DEPTNO in the target form page.

Enabling Edit of a Detail Row

By default, the wizard does not configure the page to edit detail rows, only to create them. However, doing so takes just a few clicks since you can repurpose the detail form page for editing. For example, you can enable clicking the Employees region's Ename column value to access the Employees edit page for the selected employee row. First, study the target of the existing create button for the detail region to identify the page number of the wizard-generated modal detail form page. Then, change the ENAME column type from Plain Text to Link instead, and configure its Link > Target to redirect to the same page. Pass the value of the detail row's primary key column as the value of the modal page's primary key page item. Now the page can also edit detail rows.

Reviewing the Drill Down Master/Detail Pattern Pages

When choosing the Drill Down Master/Detail pattern, as shown below the wizard creates an Interactive Report page showing master rows, each of which has an edit icon the user can click to navigate to an edit page.

The edit page contains a Form region for the master row, and an editable Interactive Grid for the detail rows related to the current master row. From this page, the user can edit the current Department (ACCOUNTING) and all its employees in a single place. Notice the grid's (Save) toolbar button is turned off, so the (Apply Changes) button that submits that page saves master and details in a single transaction. As shown below, the page also contains next and previous navigation buttons to visit other master rows in department name order.

For any master row, edit any combination of master and detail data and click (Apply Changes) or one of the navigation buttons to save the changes.

Studying the Minimal Detail Grid Configuration Required

The grid uses the detail EMP table as its data source, and by default would display all EMP rows. So, the first detail to notice is its WHERE clause. It's configured to filter for only rows whose DEPTNO foreign key matches the primary key of the current master DEPT row.

DEPTNO = :P9_DEPTNO

Since its WHERE clause references this master record page item as a bind variable, it also lists P9_EMPNO in its Page Items to Submit property.

The second detail involves the Employees grid's DEPTNO foreign key column. It is configured to use the current master row's primary key page item P9_DEPTNO as its default value as shown in the figure. This ensures that any new employees added to the grid while editing the ACCOUNTING department, are correctly associated with its DEPTNO value 10.

Understanding How to Configure Form Record Navigation

In combination with configuring an Order By Clause on the Form region itself, the Form - Initialization page process computes these three navigation-related values at page render time and assigns them to the configured page items. The first two are the primary key of the next and previous row to edit. Either or both of these might evaluate to null, depending on the current row and how many rows are in the data source. The third property is a value like "3 of 4" representing the current row index and total row count. As shown in the figure above, the previous and next row keys go into hidden page items, while the Count/Total indicator goes into a Display Only page item P9_DEPTNO_COUNT.

The GET_NEXT_DEPTNO and GET_PREVIOUS_DEPTNO buttons use a Server-side Condition to show only when applicable, based on testing whether the values of the P9_DEPTNO_PREV and P9_DEPTNO_NEXT hidden page items are not null. Similarly, the branches associated to these buttons through When Button Pressed conditions decide whether to pass P9_DEPTNO_NEXT or P9_DEPTNO_PREV as the value of P9_DEPTNO when redirecting to the current page to rerender with the correct next or previous department number.

Creating New Master and Details at Once

The wizard configures the Employees edit page to hide the details Employees grid when P9_DEPTNO is null. So, by default, when the user clicks the (Create) button from the master Interactive Report page, the edit page shows only the Form region to create a new department. The user returns to the master page and has to edit the newly added department to add employees to the department in a second step.

To enable the generated Employees page to accept both master and detail data when creating a new department, only two small changes are required. First, remove the Server-side Condition on the Employees grid so that it always displays.

Then, add an Assign Deptno Foreign Key page process in the Processing tab as shown below, sequenced after the Process form Form on DEPT process and before the Employees - Save Interactive Grid Data process. This sequence is important so the form process can insert the new department and return its new system-assigned DEPTNO value into P9_DEPTNO. Then, your new Execute Code process can assign the DEPTNO foreign key column of any new employees being created. The code is very simple. It checks the value of APEX$ROW_STATUS to see if an Employees row is being created ('C'), then assigns the DEPTNO foreign key to the new master row's primary key in P9_DEPTNO. If the row being processed has row status of update ('U') or delete ('D'), then the assignment is skipped.

-- If Employee row being (C)reated, assign DEPTNO foreign key
if :APEX$ROW_STATUS = 'C' then
    :DEPTNO := :P9_DEPTNO;
end if;

Notice in the figure that the Editable Region property of the new process is set to the Employees grid. Since the region is a grid that can save multiple rows at once, APEX runs your process once per row that the Employees grid inserts, updates, or deletes. The APEX$ROW_STATUS check ensures the assignment is only performed for Employees grid rows being created.

After making this change, clicking the (Create) button from the master Interactive Report page of Departments now shows the page below with both an empty department form and an empty Employees grid. You can enter a new department and multiple new employees and click (Create) to save them all in one transaction.