Form Regions

A form region is similar to a report region, in that both have a source query. Their difference is in how they display their output records. A report displays several output records at a time tabularly, whereas a form displays one record at a time, with each value in an item. Reports are read-only, but forms can modify the database.

Looking back at Chapter 7, the Emp Info region of the Single Row View page (refer to Figure 7-17) is used as a form, as are the Insert Employee and Update/Delete Employee regions of the Single Row Update page (refer to Figure 7-24). Those regions had the type static content and used PL/SQL code to implement their functionality. This section introduces the Form region type and shows how the same functionality can be obtained without any PL/SQL coding.

Figure 10-1 shows page 29 of the Employee Demo application, titled Form View. This page has a single region, called Browse Employees, that lets a user browse the EMP table one record at a time. The region has five items. The first four items display the values of four fields of an EMP record. Selecting an employee name populates the other three items with values from the selected record. Clicking the Previous (or Next) button moves to the previous (or next) record in the table. The fifth item displays the string 6 of 14, indicating the record’s position in the table.

To build this page, create a new blank page titled Form View. Then create a region named Browse Employees, assign it the type Form, and give it the source query shown in Figure 10-2.

When you create a Form region, APEX will automatically create an associated rendering process for it, plus an item for each column in the source query. Figure 10-3 shows the rendering tree immediately after creating the Browse Employees region and setting its source.

The name and type of each item are taken from the corresponding column definitions. For example, the item corresponding to the column EmpNo is named P29_EMPNO, and its type is Number Field. The item type for a character field is Text Field, the type for a date field is Date Picker, and so on.

An item’s Source section has properties that specify the correspondence between the item and its column. Figure 10-4 shows the Source section for P29_EMPNO.

These items and their property values were created for you when you specified the source of the form region. However, there is one property value that you must specify yourself, and that is the Primary Key property shown in Figure 10-4. The rendering process that comes with the form needs to know which item corresponds to the primary key. So you should turn on the Primary Key property for P29_EMPNO. If you forget to specify a primary key item, APEX will display an error message to remind you.

You are now able to run the page. Figure 10-5 shows what gets displayed. Admittedly, this is not very impressive. To get this form to do anything, you need to make use of its rendering process.

Figure 10-6 shows the Identification section for the rendering process. The default name for the process is Initialize form P, where P is the name of the page. The type of the process is Form-Initialization, which suggests that its purpose is to initialize the form’s items. In fact, the initialization process has similar functionality to the GetEmpInfo process of Listing 7-9. If the session state value for P29_EMPNO is not null when the page is rendered, then the process will fetch the EMP record having that employee number and use it to initialize the other items.

I then set the action of the select list to Redirect and Set Value. Consequently, when an employee name is chosen from the list, the corresponding employee number will be assigned to the item P29_EMPNO. You should verify this in your page. Select an employee name from the list and watch the corresponding employee values appear in the items.

The initialization process also contains previous/next functionality. It works the same as the FindPreviousNext process of Figure 7-18, with the added feature that it also keeps track of the record’s position in the table. To configure it, you need to perform the following steps:

• Create two hidden items to hold the previous and next employee numbers. I called these items P29_PREV and P29_NEXT.

• Create a text field item to hold the string that displays the position of the current record. I called this item P29_COUNT.

• Go to the Settings section for the initialization process, and set its Next Primary Key Item, Previous Primary Key Item, and Current Row/Total Item properties as shown in Figure 10-7.

• Create two buttons, called Previous and Next. The Previous button should redirect, setting the value of P29_EMPNO to &P29_PREV., as shown in Figure 10-8. Configure the Next button similarly.

• Optionally, go to the Server-side Condition section for the Previous button, and set its condition to the SQL expression :P29_PREV is not null. Configure the Next button similarly. This causes the buttons to be rendered only when appropriate.

Form Update

A form can also use its item values to change the database. Page 30 of the Employee Demo application will illustrate the issues. This page is named Form Update and appears in Figure 10-9. The page has two form regions: one to delete/modify an existing record and one to insert a new record. The following subsections discuss each region.

Updating Records

The Update Employees region works as follows: a user chooses an employee from the select list. The EMP record for that employee will then be fetched and its values used to initialize the region’s items. The user can then modify the item values, as desired. Clicking the Update button will modify that record to have those values. Clicking the Delete button will delete the record.

To create this region, begin by creating a new blank page, named Form Update and having page number 30. Then create a new region of type Form and name it Update Employees. Set the source to the SQL query

select EmpNo, Job, Sal, DeptNo

from EMP

Look at the resulting rendering tree. APEX has created an item for each of the fields in the query, as well as an initialization process. This is the same situation that you encountered in the Form View page. Before you forget, go to the Source properties for P30_EMPNO (as in Figure 10-4) and turn on its Primary Key property.

You should now customize the form to match Figure 10-9. In particular, change the template of P30_EMPNO to be Optional and the template of the other three items to be Optional-Above. Change the type of P30_EMPNO, P30_JOB, and P30_DEPTNO to be Select List. The list-of-values query for P30_EMPNO should be

select EName, EmpNo

from EMP

The query for P30_JOB should be

select distinct Job as DisplayVal, Job as ResultVal

from EMP

And the query for P30_DEPTNO should be

select DName, DeptNo

from DEPT

You should also create two buttons, named Delete and Update. Their position is Edit, and their action is Submit.

Your form should now be able to successfully retrieve the information for a selected employee. Your remaining task is to get the buttons to perform their intended database modifications. This turns out to be surprisingly easy.

A form region’s ability to modify the database is disabled by default. To enable it, go to the rendering tree for the form region, click its Attributes node, and look at the Enabled property in the Edit section. By default, this property is turned off. When you turn it on, additional properties will be displayed, as shown in Figure 10-10. The checkboxes allow you to specify which editing operations the region should allow. The Update Employees region performs updates and deletes, so you should ensure that only Update Row and Delete Row are checked.

As an aside, note the Lost Update Type property at the bottom of Figure 10-10. This property lets you specify how the process should implement lost update detection. Its default value is Row Values, which corresponds to the lost update detection algorithm of Listing 7-10.

When you enable editing in a form region, APEX adds a process to the Processing tab. This process has the type Form-Automatic Row Processing (DML) and is known as an ARP process. The default name of the process is Process form R, where R is the name of the form region. Figure 10-11 shows the Identification properties for the Update Employees process.

An ARP process can execute an insert, delete, or update operation in response to a submit action. But given a particular submit request, how does it know which operation to execute?

The desired operation is determined by the Behavior properties of the button performing the request. Figure 10-12 shows these properties for the Update button. The Database Action property specifies which of the three operations to execute. In the figure, this value is SQL UPDATE action. The specification of the Delete button is similar—its Action value should be Submit Page and its Database Action value should be SQL DELETE action.

An ARP process can be conditionally executed, just like any other process. The ARP process for the Update Employees region should fire when the Update or Delete button is clicked. Figure 10-13 shows its Server-side Condition properties, in which the specified SQL expression is

:REQUEST = 'Update' or :REQUEST = 'Delete'

Clearing Session State

Take some time to run your Form Update page . Choose a record, modify its values, and click the Update button. Everything should work. However, you will discover a problem with the Delete button. When you click it, APEX will display an error message.

The problem is that after the deletion, the item P30_EMPNO still contains the deleted employee number. So during page rendering, the initialization process will attempt to fetch values from this nonexistent record, thereby generating the error. The solution is to set P30_EMPNO to null after the deletion occurs. In fact, this is exactly what you did in the PL/SQL code for the DeleteEmp process shown in Listing 7-21.

But how to get the Delete button on page 30 to set the value of an item? Since you cannot touch the built-in ARP process, your only option is to create another process that will execute after the deletion occurs. Let’s call this process ClearEmpNo.

One way to implement ClearEmpNo is as a PL/SQL process having the following code:

begin

    :P30_EMPNO := null;

end;

Those of us familiar with PL/SQL coding realize that this is a straightforward, simple solution. But APEX has another solution for those unfamiliar with PL/SQL, namely, the Clear Session State process type. Here is how to use it.

Change the type of your process ClearEmpNo to Clear Session State. Figure 10-14 shows its initial properties. The Type property in the Settings section specifies which items to clear. By default, its value is Clear all items on the Current Page. Other values for the property let you clear individual items, all items on a specified page, or all items on all pages.

Although the default value is perfectly fine, it is also reasonable to change the type to Clear Items. If you do, then you will be able to enter explicit item names for the Item(s) property, as shown in Figure 10-15.

No matter how you create the ClearEmpNo process , you must perform the following two steps to ensure that it only executes after a delete operation:

• You must set the value of its When Button Pressed property to Delete.

• You must set its sequence value (shown in Figure 10-14) to be larger than that of the ARP process.

Inserting Records

The second region of the Form Update page of Figure 10-9 is called Insert Employees. It works as follows: A user enters values into the five items of the region and clicks the Insert button. APEX will then insert a record into the EMP table, using default values for the unmentioned fields.

An ARP process can only insert values appearing in page items. Thus, it is important that the Insert Employees region have an item for each field of EMP. In other words, the source of the form should be the query select * from EMP. You can then hide the items that are to receive default values.

When you create a form region having the preceding source, APEX will immediately inform you of a problem. The issue is that several of its generated items have duplicate names. The name of a generated item is based entirely on the name of its corresponding field, so a field name that appears in multiple forms on the page will have multiple items with the same name.

Fortunately, the solution is simple; just rename the problematic items. Figure 10-16 shows my strategy, which was to add “INSERT” to all items in the Insert Employees region, duplicate or not.

The two initialization processes also have duplicate names. To resolve the problem, I renamed each one to have the name of their region, as shown in Figure 10-17.

You have now resolved all of the issues with the region, so APEX will finally let you save your changes. Your remaining tasks are similar to what you did for the Update Employees form:

• Turn on the Primary Key property for P30_INSERT_EMPNO in its Source section.

• Turn on the Enabled property in the Edit section of the Insert Employee Attributes. Then ensure that only the Add Row operation is checked.

• Create a button named Insert. Configure its Action property to be Submit Page and its Database Action property to be SQL INSERT action.

• Go to the property editor for the ARP process named Process form Insert Employees, and give its When Button Pressed property the value Insert.

• Format the type and layout of the items according to your taste.

If you try out your new region, you will discover one further problem, namely, that the form expects you to always have values for P30_EMPNO and P30_INSERT_EMPNO. This requirement was appropriate when Update Employees was the only region, because the Delete and Update operations make no sense without an employee number. But P30_EMPNO can (and should) be null when the Insert button is pressed. Similarly, P30_INSERT_EMPNO should be null when the Delete and Update buttons are pressed. Thus, you need to disable the Value Required property in the Validation section of both items.

The Insert operation places the new employee number into P30_INSERT_EMPNO. Although the item is hidden, its value can be part of the Success Message property of the ARP process, as shown in Figure 10-18.

Computations

The Insert Employees region shown in Figure 10-9 only displays items for the columns EName, Job, Salary, and DeptNo; the items for HireDate, Comm, and Offsite are hidden. The idea is these hidden items will contain default values—in particular, P30_INSERT_HIREDATE will contain the current day, P30_INSERT_COMM will be 0, and P30_INSERT_OFFSITE will be 'N'. The ARP process will use the values of these items when it performs its insertion operation.

The question, then, is how to get default values into the hidden items. The answer is to use an APEX computation. A computation is a page component that assigns a value to one item. The idea is to create a computation for each item that needs a default value and to arrange for these computations to be executed before the ARP process executes. In that way, the computed values will wind up as part of the session state, and the ARP process will use them when it creates a new record.

The Form Update page has a computation for each of the three items that needs a default value. Figure 10-19 shows the Processing tab of the page designer with the three computations listed. Note that the execution point of the computations is After Submit, which means that they will execute before any of the processes.

To create a new computation, right-click the After Submit node of Figure 10-19 and select Create Computation. You will then need to specify properties for its Identification and Computation sections. Figure 10-20 shows the property values for the P30_INSERT_HIREDATE computation. The Identification section has the property Item Name, in which you specify the target item. The Computation section has the property Type, in which you specify how the item’s value is computed. In Figure 10-20, the type is SQL Expression, and the expression is simply a call to the SQL function current_date.

The other two computations are created similarly. The simplest way to specify a constant is to use the type Static Value, but you can also specify it using an SQL expression.

I intentionally hid the items P30_INSERT_HIREDATE, P30_INSERT_COMM, and P30_INSERT_OFFSITE in Figure 10-9 to keep users from changing their default values. Another design option is to display these items with their default values already filled in. The user can then treat these values as suggestions and modify them if desired.

This option implies that the computed values should be assigned to the items during page rendering, instead of after submission. Implementing this option is straightforward. All you need to do is unhide the items and change the execution point of the three computations (or their equivalent PL/SQL process) to After Header.

Sending Email

Recall from Chapter 7 that the Revised Employee Data Entry page has a process SendSuspiciousEmail, whose job is to send email messages when suspicious database deletions and updates occur. The code for this process appeared in Listing 7-14. It is written in PL/SQL and makes use of the apex_mail.send library function.

APEX has the process type Send E-Mail, which gives you the ability to send email without having to write PL/SQL code. This section will show how to use this process to achieve the same functionality as in Listing 7-14.

Create a new process for page 30. Call it SendSuspiciousEmail, and give it the type Send E-Mail. In the property editor, look for the Settings section. You will need to specify values for the properties From, To, Subject, and Body Plain Text. Figure 10-21 shows how I entered these values. (If you want to test the process, be sure to use your own email address as the recipient.)

The body text makes use of the APP_USER variable and the item P30_EMPNO to identify the user and the affected employee number. This behavior is the same as in Listing 7-14, except for one feature—the body of the email does not indicate whether the operation was a delete or an update. Listing 7-14 implemented this feature by an if-statement inside the PL/SQL code. How can you do the same thing when you use a process of type Send E-Mail?

For example, a possible email message arising from this expression could be

[email protected] deleted employee 7839's record.

Finally, you need to specify when the SendSuspiciousEmail process executes. Its server-side condition should specify what is meant by a “suspicious” change to the database. I chose to use the same condition shown in Figure 7-16. In addition, the sequence number of the process must be chosen such that it executes after the database operation but before the ClearEmpNo process clears the deleted employee number from P30_EMPNO.

Report with Form Pages

An APEX Form region automatically generates items for you and provides two powerful built-in processes, which save you time over having to write comparable items and PL/SQL processes. However, you still need to create and configure additional buttons and items. To simplify your work further, APEX provides wizards for creating prebuilt form pages. You enter your information into the wizard and it creates the necessary regions, items, buttons, and processes. Such wizards can be effective shortcuts if you want what they produce.

A list of these wizards can be found starting from the initial Create Page screen of Figure 2-9. Instead of clicking the usual Blank Page icon, select the Form icon. APEX will display the screen shown in Figure 10-23, which contains icons for the form-related wizards. The Report with Form wizard is a good illustrative example, so let’s begin with that.

A Report with Form page is actually two pages—a report page and a form page. It works similarly to the Single Row Update page in Figure 7-24, but the report and form are on two separate pages instead of being on separate regions of the same page.

Clicking the Next button in Figure 10-23 displays the first Report with Form wizard screen, shown in Figure 10-24. The Report Type property asks you to specify whether the report should be an interactive report, an interactive grid (to be discussed in Chapter 11), or a classic report; I chose Classic Report. The next four properties ask you to provide a page number and name for the report and form pages. In my application, the report is on page 31 and the form is on page 32. Finally, you are asked to specify whether the form page should display normally or as a modal dialog box. (Modal pages were discussed at the end of Chapter 2; see Figure 2-15.) I chose Normal in the figure, but Modal Dialog is also interesting and worthwhile.

Clicking the Next button brings you to the second wizard screen, shown in Figure 10-25. This screen asks you to supply a data source to be used for both the report and the form. If you are planning to use the form for insertion then the source will need to contain the entire table, as shown in the figure. In this case, the wizard screen also lets you specify the columns that you want displayed in the report.

The third screen (not shown) is where you specify the navigation menu entry for the report page. Note that the form page does not get a menu entry because it should only be accessed via its report page.

The fourth screen, shown in Figure 10-26, lets you specify the source columns you want displayed in the form. It also forces you to specify the primary key column.

When at last you click the Create button, APEX will create two fully functional pages. Figure 10-27 shows the result of running the report page. (I chopped off the bottom to save space.)

There are two ways to get from the report page to the form page. Clicking the CREATE button brings up the form page in insert mode, as shown in Figure 10-28. Alternatively, clicking the link at the beginning of a row brings up the form page in update/delete mode, populated with the values for the selected employee. Figure 10-29 shows the form page after clicking the link for WARD.

Note that the buttons on the form page differ according to its mode. In insert mode, the page displays a Create button, which inserts a new record into the EMP table. In update/delete mode, the Delete button deletes the selected record and the Apply Changes button updates it. All three buttons return to the report page after changing the table. The Cancel button is common to both modes; it simply returns to the report page without changing the table.

The following subsections investigate how the wizard implemented these pages.

The Report Page

The report page (Figure 10-27) has one region, which I chose to be a classic report. Its implementation is completely straightforward. The only interesting feature is the EMPNO column, which has the type Link (as described in Chapter 3). Figure 10-30 shows its Link Builder screen. It specifies that the link will redirect to page 32 (the form page) and will set the item P32_EMPNO to the employee number of the selected row.

The action of the Create button is to redirect to the form page and clear its session state, as shown by the Link Builder screen of Figure 10-31. Compare these two figures. They both redirect to the form page; their difference is that a row link assigns a value to P32_EMPNO, whereas the Create button clears the value of P32_EMPNO (as well as all other items on the page).

The value of P32_EMPNO therefore determines whether the form page is in insert mode or update/delete mode. In insert mode, the values of the page items will all be null because the Create button cleared them. In delete mode, the values of the page items will all have a value, due to the action of the initialization process.

The Create button is rendered in blue to indicate that it is a “hot” button. The relevant property is named Hot and is in the button’s Appearance section, as shown in Figure 5-29.

The Form Page

The form page (Figures 10-28 and 10-29) has a single region, of type Form. Figure 10-32 shows its rendering tree. As with all form regions, it has an initialization process, as well as an item for each source value. (Those items are not shown in the figure to save space.) The main difference from a standard form region is that it also has four buttons. The CANCEL button redirects to the report page. The other three buttons are configured to have a Database Action value of SQL INSERT action, SQL DELETE action, and SQL UPDATE action.

Recall that the DELETE and SAVE buttons are rendered only when the page is in update/delete mode and the CREATE button is rendered only in insert mode. The form uses the value of P32_EMPNO to determine the conditional rendering. If it is null, then CREATE is rendered; otherwise, DELETE and SAVE are rendered. The form page also contains a branch that redirects to the report page after the submit processing has finished.

The action of the CREATE and SAVE buttons is Submit Page. Interestingly, the action of the DELETE button is Redirect to URL, where the URL is

javascript:apex.confirm(htmldb_delete_message,'DELETE');

Figure 10-33 shows the Link Builder screen for this action. The JavaScript function apex.confirm displays a confirmation window; clicking Yes causes the page to submit using the request named DELETE. In other words, the DELETE button actually does perform a submit, albeit indirectly through JavaScript.

The Processing tab of the form page contains two components: the ARP process common to all form regions and a branch. The process fires when the CREATE, SAVE, or DELETE button is clicked. Since these buttons are the only components on the page that perform a submit action, the process does not need to be conditional. The branch unconditionally redirects to the report page following the submit processing, which explains why every button click from the form page causes a return to the report page.

Form Pages

The two pages created by the Report with Form wizard are very loosely connected. Their only interaction is when a link on the report page or its Create button is clicked, both of which set the value of the item P32_EMPNO on the form page. Consequently, the form page can be used in conjunction with other kinds of page, the only requirement being that the other page provides the form page with an employee number.

In this section, instead of connecting a report with a form, I want to connect a tree with a form. A tree depicts a table’s records hierarchically, which is possible when the table has a field that can be the basis of the hierarchy. For example, the Mgr field of EMP determines a hierarchy, known in business as the management hierarchy. The president, who has no manager, is the root of the hierarchy. Every other employee is the child of its manager.

Page 33 of the Employee Demo application displays the employee records as a tree. This page is named Management Hierarchy Tree and appears in Figure 10-34. Each node of the tree displays an employee name. Clicking a name redirects to a form page, named Management Hierarchy Form, that displays additional values for that employee and gives you the opportunity to update them or to delete the employee’s record. Figure 10-35 shows the form page that results from clicking ADAMS.

Implementing the Tree Page

Although APEX has a page wizard to create trees, it is more instructive (and simpler!) to create the page yourself. So create page 33 as a blank page named Management Hierarchy Tree, and create a region of type Tree. Its source should be the EMP table.

The properties to configure the tree are in the region’s Setting section, which you get to from the Attributes node in the rendering tree. Figure 10-36 shows the Settings properties for the tree of Figure 10-34.

The property named Hierarchy should have the value Computed with SQL, which tells APEX to compute the hierarchy from the following settings:

• Node Label Column is the field whose values are the labels of the tree nodes.

• Node Id Column is the primary key field.

• Parent Key Column is the field that defines the hierarchy. A record’s value for this field specifies the primary key of its parent.

• Start Tree With specifies the root of the hierarchy. The setting Value is NULL asserts that the root of the hierarchy is the record whose parent key column value is null.

Run your page at this point, and explore the tree. If a node has children, there will be an arrow to its left. Clicking on these arrows will expand or contract their list of children.

The final configuration step is to specify what happens when a user clicks a node in the tree. The property you need is Link, which is further down the Settings section. Clicking its box brings up the Link Builder wizard. Figure 10-37 shows how I configured the link. It specifies that clicking the node redirects to page 34 and sets the value of P34_EMPNO to the node’s EMPNO value. Note that substitution string syntax is used to reference the value of a node’s field.

Implementing the Form Page

Page 34 of the Employee Demo application is the form page shown in Figure 10-35. It is easy enough to implement it by creating a blank page and adding a Form region to it. However, it is even easier to use a wizard to create the form page itself.

Looking back at the Create Page screen of Figure 10-23, you can see an icon for a wizard named Form. Clicking it brings up the Create Form screen of Figure 10-38.

After asking for the page number and name, the wizard requests two things: the page that the form should branch to after a submit (i.e., the Delete and Apply Changes buttons) and after a redirect (i.e., the Cancel button). The remaining screens are similar to what you’ve seen before—a screen asking about the navigation menu, a screen requesting the source, and a final screen requesting the primary key.

The form page generated by the wizard should work seamlessly with the tree page, without any additional configuration on your part—the tree redirects to the form when a tree node is clicked, and the form redirects to the tree when a button is clicked. My form page in Figure 10-35 is the result of simply changing the type and layout of the items generated by the wizard.

I want to stress that the form page generated by the Form wizard is exactly the same as the form page generated by the Report with Form wizard—the only difference between the two wizards is that Report with Form also generates a page containing a Create button and a report. Moreover, the page generated by the Form wizard is exactly the same as a blank page with a Form region, plus a few buttons and property settings. The other form-based wizards in Figure 10-23 are similar to Report with Form. Knowing this can help you decide, for any given task, whether to use one of the wizards or to write the pages yourself.

Master-Detail Forms

Chapter 6 discussed the creation of master-detail reports. In a master-detail report, two reports are linked such that selecting a row of the master report causes the detail report to display only those rows associated with the selected master row. For example, the Employees by Department page in Figure 6-8 shows an EMP report linked to the master DEPT report; clicking a DEPT record causes the EMP report to show only those employees in the selected department.

In this section, I want to consider how to expand a master-detail report to include updates. A straightforward approach is to create two sets of Report with Form pages, one for DEPT and one for EMP, and then link the DEPT and EMP reports as in Chapter 6. Let’s see how much effort this requires.

Begin by using the Report with Form wizard to create report and form pages for DEPT. The report page is named Master Report and is page 35; the form page is named Master Form and is page 36. The page source is select * from DEPT.

Then use the wizard to create report and form pages for EMP. The pages are numbered 37 and 38; the report page is named Detail Report and the form page is named Detail Form. Their source is select * from EMP.

Figures 10-39 and 10-40 show these two reports. Figure 10-40 shows the result of clicking “view emps” for the ACCOUNTING department. Note that I changed the region titles on both pages from what the wizard generated. In particular, the title of the detail region is customized with the department name.

The only change you need to make to the master report is to create the additional “view emps” column. To do that, you should create and configure a virtual column for the report, as follows:

• Right-click the Columns node of the rendering tree, and select the Create Virtual Column menu item. APEX will add a column named DERIVED$01 to the report.

• Set the type of this column to Link.

• Go to the Settings section, and click the box for the Target property. This brings up the link builder.

• Configure the link builder as shown in Figure 10-41.

• Set the Link Text property for the column to be View Emps.

That’s it! The advantage of using the page wizard is that you can quickly construct fully functional web pages. You will probably want to configure the look and feel of the regions, but that shouldn’t be too onerous.

The only problem with using the page wizard is that it forces you into a particular page architecture—in this case, the use of four single-region pages. There are many other possible designs. In fact, APEX has a Create Page wizard that constructs master-detail forms quite differently. Figure 10-42 shows its primary page.

The page has five regions: a region across the very top, named Master-Detail and having two buttons; a region in the left column containing the search bar; a report region down the left column listing the master rows; a report region (named Dept) at the top, listing the selected master row; and a report region (named Emp) at the bottom, listing the associated detail rows.

The rows of the master region in the left column are links. When you click a row, it becomes the selected row and the two regions to its right adjust themselves accordingly.

Clicking a button or link displays a form in a modal dialog page. For example, Figure 10-43 shows the result of clicking the Edit button. The user can modify the master record, delete it, or return to the master-detail page without doing anything.

From a design point of view, it is worth comparing this master-detail page with the previous solution. This page displays the master and detail reports on the same page, similar to the Employees by Department page of Figure 6-8. This page is primary, and the user never loses contact with it. When an edit is required, the appropriate form is displayed modally and then disappears when the edit is complete. In comparison, the previous solution consisted of four pages. The user switches from one page to the other according to the task at hand (“view the master report,” “edit a master row,” “view the detail report,” or “edit a detail row”), and no page is more important than any other page.

The point is not that one approach is better than the other, but that there are many ways to approach a problem. You need to be facile enough with APEX to produce the pages you envision, instead of being forced into a design based on the pages that the wizards produce.

With that in mind, let’s use the wizard to create these master-detail pages and then examine their implementation. Start from the initial Create Page screen of Figure 2-9. When you click the Master Detail icon, APEX displays the screen shown in Figure 10-44. From there, clicking the Side by Side icon results in the screen of Figure 10-45.

This screen asks you for the name and page number of the primary page (i.e., the page shown in Figure 10-42). The wizard will also create two more pages to hold the master and detail forms, but you don’t get to choose their numbers. Clicking the Next button takes you to the screen where you specify the navigation menu entry. You’ve seen this screen before, so I am omitting it here. The final screen, shown in Figure 10-46, asks you for the master and detail tables. The properties for Primary Display Column and Secondary Display Column denote the two values to be displayed in the master records. Clicking the Create button produces the three fully functioning pages. In my application, those pages are 39, 40, and 41.

Pages 40 and 41 are form pages and are the same as what you would get by calling the Create Form wizard. So the main issue is how the report page is implemented.

The function apex_page.get_url returns a URL to an APEX page. By default, the URL redirects to the current page. The parameters p_items and p_values specify how the session state should be changed.

The region titled Dept in Figure 10-42 has the template Value Attribute Pairs – Column. This template displays each value on its own line, prepended by the column name. In the figure, the region’s source has one row with two columns, so its report displays two rows.

The region titled Emp has the Standard template, so its report is displayed in the standard way.

Apart from their different appearances, the Dept and Emp reports interact with their form pages in the usual way, by having their buttons and links redirect to the appropriate form page.

The bottom line is that even a sophisticated wizard like Master-Detail is just creating a page containing three report regions, two of which are linked to form pages. If you wish, you can then customize those regions to fit your needs.

Summary

The first part of this chapter examined APEX form regions. A form region contains two processes—an initialization process and an ARP process—that allow your pages to access the database without having to write PL/SQL code. APEX also provides built-in processes for some common tasks, which work smoothly with the two form processes. Several examples demonstrated both the advantages and limitations that these processes have over corresponding PL/SQL processes.

The second part of the chapter examined some APEX page-creation wizards that create forms. These varied from the single-page Form wizard, which creates a page that is intended to be used in conjunction with other pages, to the Report with Form wizard, which pairs the Form page with a simple report page, to the sophisticated Master-Detail wizard, which matches two Form pages with a page containing three report regions. In each case, the focus was to uncover the mystery of these wizards, so that you feel empowered to create the pages you need, with or without them.