Pages

Thursday, July 31, 2008

Manually Creating a Tabular Form

When I first started developing in APEX (back when it was called HTMLDB) I had a requirement for a tabular form. I tried to use the standard tabular forms but it was very limited and I couldn't customize it to meet my requirements. I had poked around on the APEX forum, but wasn't able to find a reasonable solution.

After several iterations, I have come up with a process that works extremely well. It is slightly labor intensive but it has met all the requirements each time. Please note that Patrick Wolf, http://www.inside-oracle-apex.com/, has developed an open source framework to handle customized tabular forms. Though it is very good it does not allow for complete access. Marcie Young did a presentation at ODTUG 2008 which outlined a very similar method, this example is taking it a step further. A working example is available here: http://apex.oracle.com/pls/otn/f?p=20195:200

The process below will not only build a customized tabular form, but also handle the errors etc.

OverviewHere's an overview of the overall methodology- Create 2 collections. One will handle data: DATA_COLLECTION and one will handle the errors: ERROR_COLLECTION.- The DATA_COLLECTION will be loaded on the first viewing of the page and will only be refreshed from the database when the changes have been sent to the database.- The ERROR_COLLECTION will contain errors specific for the corresponding entry in the DATA_COLLECTION. We could keep this in the same collection, but I like to keep them separate. It makes things easier if we need to add or remove columns.- In the ERROR_COLLECTION I keep column 50 reserved for the row error. An example of when you'd need a row error is when you have a start and end date and the end date is before the start date.- This application will allow users to modify the emp.ename and emp.salary fields- In the data collection I keep column 1 reserved for the sequence id (seq_id). Note though I won't use it in this example, it has come in handy (especially when hiding/delete rows in the front end then submitting the page)

Getting Started

Step 1: Create a PL/SQL Process On Load Before Header called Create Collection

All items/buttons should be added to the report regionCreate Buttons:ADD, submit page. Branch to &APP_PAGE_ID.SUBMIT, submit page. Branch to 201Note: Page 201 is a simple sql report for

SELECT *FROM EMP

Create Hidden and Protected Items:P200_DISPLAY_ROW_ERROR_FLAG- Source value or expression: N- Comment: Used to determine if the error column should be displayedP200_NUM_EXTRA_ROWS- Source value or expression: 1- Comment: Number of extra rows to add to the tabular formP200_RELOAD_FLAG- Comment: If Y then we won't refresh the collection with database values

How do i create a delete button that deletes a row from the collection?I was thinking about adding an extra ID column, displaying it as a checkbox, with an 'onclick' javascript, but from there i do not get any further....

I wonder if you could help. How would I add a dynamic LOV to the screen that allows the employee manager to be selected but ensures that the LOV select does not include the employee of the current row? i.e. the employee cannot be their own manager.

Thanks, Martin. Do you have an example or description of the logic that is used to determine if an update has occurred in the form before performing the update to the DB? Would you do this during the validation?

You can do a simple comparison for the value in the form vs the value in the collection.

To see if a row in the collection has been updated, APEX collections include an MD5 checksum which is used to see if value has changed. For more information please read: http://download.oracle.com/docs/cd/E23903_01/doc/doc.41/e21676/apex_collection.htm#BAGGAIFD The API also contains some other functions which you may find useful.