Wednesday, 21 September 2011

Apex Tutorial - Form & Report sharing the same page

During some training recently I walked through the "Form on a Table with Report" wizard to create two pages - one report listing records from a table, which then linked to a form page allowing edits on that table.

I was asked how you could amalgamate the two pages into one. Here is a tutorial that completes this exercise from scratch.
Note: if you have existing pages already defined, I would recommend you copy the report region onto your Form page, and modify the relevant attributes - then remove your report page.

1) First, create a form on page on a table - I use the sample EMP table
* When prompted during the create form wizard, set branch for apply/cancel to the same page you're creating. Note in my case it's page 6.

Now we have a basic form page, where most of the hard work has been done by the wizard, such as defining page processes, branches and item definitions.

You form will look something like this to start with:
2) Now create a report region listing employees from EMP
 This will show a simple report underneath your form:
3) Create item P6_MODE
This will be the catalyst for showing/hiding certain components on the page
4) Create a button called NEW on the Employee List report region
This will allow you to create a new employee record
Action details:
* redirect to 6
* set parameter P6_MODE = E
* clear cache => 6
* condition => COALESCE(:P6_MODE,'V') = 'V'

5) Modify the Cancel button action to set P6_MODE = V
This ensures your page returns to status quo when the cancel button is pressed

6) Modify the branch defined by the create form wizard and pass parameter P6_MODE = V
Whenever you submit your page, it will now return it to status quo

7) Add condition on HTML region that contains your form items to check :P6_MODE = 'E'
Now your form region will only appear when in "Edit mode", such as when the New button is pressed or the user edits an existing record (next step)
8) Modify EMPNO column in the report to become a link
This will allow your user to edit an existing employee record. 
While in the report attributes page, I set the column headings to custom and cleared the heading for EMPNO - just to tidy up the display a touch.
* redirect to 6
* set parameters P6_EMPNO = #EMPNO# and P6_MODE = E

You should now have report page that displays the form region when the New button is pressed, or when you click to edit an existing record. The form region will disappear again once you Apply/Delete/Cancel.
Above is "status quo"; below while editing an existing record
I hope that helps, and I also hope it made sense. I made certain presumptions about you finding the relevant settings - otherwise there would be a real flood of screenshots!

Here is a screen grab of the page builder after all the modifications have been made (the row fetch page render process is off-screen, and shared components section not displayed)

Run the demo to see it in action.
There are other techniques demonstrated on this page.

Check out this tutorial to extend this functionality by adding a read-only step.



Learco Brizzi said...

If you use the wizard when creating a 'Form on a Table with Report', you have the possibility of setting the page for the form and for the report. Just fill in the same page number for both regions and you're ready.

Scott Wesley said...

Of course! That will save a step or two. After the conversation I was having with the group, I completely forgot about that. Cheers

glueckry said...

You have mentioned "if you have existing pages already defined, I would recommend you copy the report region onto your Form page, and modify the relevant attributes - then remove your report page". How do you put an existing region on a page on another page? Can you please help me understand this.

Thank you

Scott Wesley said...

Ryan - there are a number of methods for copying components within Application Express. In the component view, there is a copy icon that allows you to select a region to copy to another page.

Does this point you in the right direction?

glueckry said...

Yes. Also, is there a way to program a filter based on user login. I am running into a problem... If I log into the program I should see page A with rows assigned to my user_id. furthermore, say someone else logs in and they should see page A with rows assigned to there user_id. Can this be accomplished and if so how?


Scott Wesley said...

Ryan - The APP_USER substitution string informs you who is logged in, you should be able to use this to map to any custom user table you may have.

Ultimately, you can write queries with a where clause such as:

WHERE your_table.user_id = :APP_USER

to restrict to only those rows related to your user.

These sorts of questions can be a bit of a can of worms, though.