Thursday, May 1, 2008

Carl Backstrom has created a monster

Carl Backstrom has created a monster with his forum post on "Enhancement Request Thread : Post 3.1"

The quality of the requests coming through really is testament to the growing maturity of Oracle Apex.
It is obvious that many of the respondents have the depth of understanding of the product that is only developed over time.

Interactive report regions (IRR), was a huge step forward in the 3.1 release.
It really enhanced and simplified implementing query-by-example functionality.
What's more by using AJAX functionality to only refresh the region, rather than the whole page, the user experiences better performance and a more interactive experience.

A particular feature to note is the column value filtering, which performs a lookup based on:
- column values (select distinct)
- pre-defined lov
- datatype specific, e.g. a variety of date based alternatives  

So what next for future releases post 3.1?

To my mind the focus should now be on enhancing the data entry side of Oracle Apex.
With the reporting side so much improved, data entry is looking a little tired.

Two quick wins here:
- javascript dates instead of popups
- AJAX lookups.

Implementing javascript dates is already in the statement of direction, so enough said.

AJAX lookups are not currently mentioned, but I'm sure that internally its very much on the radar.
Why am I so confident of this?
Well, mostly because so much of it is already implemented in interactive report regions.

- allows lookups to be performed on large datasets
- allows "auto-complete" functionality i.e. reducing list of values as you type
- better user experience than popup search forms

To see a working example, look at my demo site

My implementation is based on a holiday table, which includes a customer_id.

The AJAX functionality calls the following procedure:  

PROCEDURE customer_lookup
(p_callback varchar2
,p_query varchar2 default null
,p_limit number default 20
,p_start number default 0
,p_customer_id number default null );

The procedure works as described in the following pseudo-code:  
if p_customer_id is not null then  
-- lookup customer details and return in JSON format  
-- lookup customers where name like p_query||'%' 
-- return n records in JSON format (configurable by p_limit), starting at record p_start  

For Apex to implement this:
1. New item type (perhaps AJAX lookup?)

2. Developers would need to be able to define a 3 column lov query against the item
   (Nice to have, but 2 column lov OK also)

   select value, short_display_value, long_display_value from some_table

3. Mudge existing IRR lookup functionality for query and display on page.

Worthwhile? Whats your opinion?



Anonymous said...

Nice Post! I too agree having an auto-complete item type would be a big win. It's all about the data!
Thanks, Todd

Dean Attewell said...


So.. I like the autocomplete feature.. I am loking for a solution for one of my fields.. which needs a autocomplete, saving the ID to database.. not just the text itself..
I have tried Denes K solution.. but his version does save ID to database..

So do you sell your version or.. are you giving it away for free?
Or is it best to wait until APEX 3.2? or apex 4.0.
Don't really want to wait .. would like a solution..

can you advise


Anonymous said...

it is very good bloch,
can you help me about the dteps
regard sihem

Anonymous said...

it is very good block,
can you help me about the steps
regard sihem

Anonymous said...

i resolved the probleme
you should beginning the return data json with(stccallback)exactelly we should create in pl/sql:
so the p_callback is the parameter of procedure (customer_lookup)

Good luck for evry when , i hope that help yuo
thank you very much for this post Marck_Lancaster
you are the baest.....
regerd anonymous