A GUI intensive ApEx Application

This blog will start out as a laundry list of GUI-type issues that I run into while developing a very GUI intensive. My initial impression is that I should be able to address these issues with either normal HTML DB page design or JavaScript. We are running HTML DB version 2. My background in screen development is Oracle Forms in which one can develop very “client-heavy” GUI applications.

I will briefly describe the application. I cannot specify exactly what type of data the application is based on, but I can describe the application at a high enough level that the topics can apply to many other sets of data.

The application is basically a “user reporting criteria gathering” application. The user assembles a study which is a collection of one or more types of reports, specifies the criteria for the reports, and clicks the run button. The user then receives one or more emails with the reports in Excel format as attachments. The HTML DB application handles the study definitions, report criteria, and run button. The data gathering is done as a DMBS_JOB in PL/SQL. It takes data from the large source tables, summarizes it, and saves it to results tables. An Excel process is alerted. Excel places the summarized data into a worksheet, attaches the worksheet to an email, and sends the email to the requester. This is the second application I have done using this same design. I worked on another application a couple years ago that was based on Oracle Forms.

Issue 1: A Generic User Prompter

As part of the application the user will have the ability to copy a study or a report in a study. So the screen will have to prompt the user for the new name of the study or report. I decided to utilize the JavaScript prompt method to prompt the user. I created a function that you can pass prompt text to display and the name of the page item that should receive the user's response as well as a default value.

function prompter(p_prompt, p_destination, p_default) {
var reply = prompt(p_prompt, p_default);
if (reply != ' ' || reply != null) {
html_GetElement(p_destination).value = reply;
}
else {
html_GetElement(p_destination).value = null;
}
}


Issue 2: Cascading Select Lists in a From Region

There are two Select Lists in a form (data entry page) region. The values in the second Select List are dynamically based on the value that the user selects in the first Select List. The first Select List is the type of report the user wants. The second Select List is the format in which the report should be rendered. You could have two rendering formats for the same report. One format could be a graphical chart. The other could be just a numerical dump of the detail data.

I did not want to force a page submit to populate the second Select List because it would fire the Automatic Row Processing which would attempt to commit to the database. I tried playing with the request values upon submit of the page, but could not get that to work. So after playing around with a couple other options I decided to implement an AJAX JavaScript solution provided by Carl Backstrom. It is pretty slick.


Issue 3: Cascading Select Lists in a Tabular From Region

This is a little harder because you have multiple Select Lists in the region. In the development process I ended coding this region 4 different ways before deciding on one way. I used both wizard generated tabular form regions and manually created form regions. In trying to implement an AJAX solution I read a method that Vikas demonstrates of being able to access particular items in particular rows in a tabular form. I was able to get AJAX solution to work, but I decided to “silently” submit the page for the user once they made a selection in the first Select List. The rendering of the page then populated the dependent Select List with the appropriate values.


Issue 4: Show Parent (Study) in a Tree of Reports

I wanted to display the structure of the study and its reports in one tree structure. I was easily able to display a tree of parent and child reports, but wanted the root of the tree to be the study name. So I created a view which UNIONed in the study name as the root of the tree and all the parent reports and their children as children of the root. The tree item in the page region then listed the study as the root with the reports underneath it like:



Issue 5: Allow User to Save Partial Studies

We needed the ability to allow the users to partially save a study. In other words allow the user to define part of a study and at a later time come back and edit the report. I used normal wizard generated HTML DB screens. There was nothing difficult here. I allowed screens to save the data to the database. The tricky part was not allowing the user to run partially entered study. Also we needed a way to communicate to the user what parts of the study need to be fixed or corrected. I handled this with a Study Validater function and a Report Validater function.

At the top of the “edit study” page I put a region that displays which reports in the study the user needs to correct or do something to. This region calls the Study Validator passing in the study ID of the study the user is editing. The function returns a table of records. Each record indicates which report in the study the user needs to modify.
We created a table type which allows us to display the rows returned by the function. So our region SELECT statement was as simple as:

SELECT ROW_NUMBER() OVER (ORDER BY NULL) task, text description
FROM TABLE(check_study(:P3_STUDY_ID))

See technical details

The run study button calls the Study Validater. If the validater returns no rows, then the study is submitted to be run. If the validator returns rows then a message pops up indicating that the user has report modifications to make.

Similarly I have a Report Validator. It provides the details of what the user has to do for a given report before they can submit the study to run.


Issue 6: Checkbox in a Tabular Form

The tabular form was wizard generated. I wanted the last item in each row of data to be a checkbox resulting in a “Y” for yes or an “N” for no to be inserted into the column in the database. I could have done this as a checkbox and coded an extra page process to assign the column’s value to each row in the database, but I decided to use a Select List based on a Yes/No list of values. It is not as fancy or flashy as a checkbox, but I was able to leave all the inserts, updates, and deletes to the wizard generated code.

I will add more as I encounter them.