Sunday, 10 February 2008

Dynamic Quick Picks - APEX style!

Introduction...
Have you ever admired the quick pick links that appear on some of the APEX pages under select lists. For example editing the 'Display As' property of a page item you can quick pick common display types, as shown here:


So, I wanted to implement this same feature in my own applications, only dynamically generating the quick pick options based on data. For example, in the standard EMP table there is a column called Job. I wanted to display 2 quick picks for this field, based on the top 2 occurences of values for jobs in the EMP table.


How...
Step 1) Firstly we need a generic database function to handle rendering the links. Here is the code:
create or replace
PROCEDURE QUICK_PICK
( p_query VARCHAR2
, p_item VARCHAR2) AS
  TYPE cur_type IS REF CURSOR;
  cur cur_type;
  v_display VARCHAR2(4000);
  v_return VARCHAR2(4000);
  l_names DBMS_SQL.VARCHAR2_TABLE; 
  l_query VARCHAR2(1000);
BEGIN
  l_query := p_query;
  --get any binds and replace with the value from session
  l_names := WWV_FLOW_UTILITIES.GET_BINDS(l_query);
  FOR i IN 1..l_names.COUNT LOOP
  l_query := REPLACE( LOWER(l_query),
                      LOWER(l_names(i)),
                      '(select v('''|| 
                        LOWER( LTRIM(l_names(i), ':')) ||
                        ''') from dual)');
  END LOOP;
  HTP.P('<div>');
  OPEN cur FOR l_query;
  LOOP
    FETCH cur INTO v_display, v_return;
    EXIT WHEN cur%NOTFOUND;
    HTP.ANCHOR('javascript:setValue(''' || 
                p_item || ''',''' || 
                v_return || ''');',
    '[' || v_display || ']',
    null,
    'class=''itemlink''');
  END LOOP;
  CLOSE cur;
  HTP.P('</div>');
  HTP.P('<br/>');
END QUICK_PICK;
So this procedure takes a SQL query (p_query) and a page item ID (p_item) as parameters. The results from the query specified in p_query will be used as the display and setting values of each quick pick link, so the query must contain two columns in the select statement (the first selected column should be the display value to be used and the second the return value, see query below for an example). The item name specified in p_item will be the item that the quick pick link will set.

This procedure just opens a dynamic cursor based on the query specified in the p_query parameter. Then calls the HTP.ANCHOR function to generate the anchor HTML tag. The procedure also pads the lot in a div tag and adds a line break at the end for presentation (you may want to change this if you have another page item on the same line as the select list being set). The following is an example of the HTML generated by this procedure:
<div>
  <a class="itemLink"
     href="javascript:setValue('P2_JOB','SALESMAN');">
        [SALESMAN]
  </a>
  <a class="itemLink" 
     href="javascript:setValue('P2_JOB','CLERK');">
        [CLERK]
  </a>
</div>

Step 2) Secondly, we need to call this function in APEX. You can do this by adding a page item with the following settings:
Name > Name: [whatever you want, P1_QUICK_PICK_JOB would be fine for this]
Name > Display As: Display As Text (based on PL/SQL, does not save state)
(Note: This display type is not available when creating a page item, so it must be set after creation.)
Displayed > Sequence: [just after the select list item you wish the quick pick link to set]
Label > Label: [blank]
Label > Template: No Label
Source > Source Type: PL/SQL Anonymous Block
BEGIN
  QUICK_PICK('SELECT  summed_rows.job display
              ,       summed_rows.job return
                      FROM  (SELECT    job,
                                       SUM(1) qty
                             FROM      emp
                             WHERE     job IS NOT NULL
                             GROUP BY  job
                             ORDER BY  SUM(1) DESC
                             ) summed_rows
                      WHERE  ROWNUM    <= 2'
                    , 'P2_JOB');
END;
This example uses a query that selects the top 2 occurences of job values in the emp table. The second parameter indicates that the page item 'P2_JOB' will be set by the quick pick link.

Step 3) So finally, there is one more thing to do, create the css used by the link. I have copied the inbuilt class used by APEX to render these links and re-specified it in its own 'custom.css' file. You can do this by creating a file with the following contents:
a.itemLink:link {
color:#811919;
font-size:11px;
margin:0pt 5px 5px 0pt;
text-decoration:none;
white-space:nowrap;
}
Then save this as 'custom.css' and upload it to APEX via 'Shared Components > Cascading Style Sheets'. Finally to make it available to the application, we need to reference this in the page template, so add the following line to the 'Definition > Header' code, after the default theme css declaration:
<link rel="stylesheet" href="#WORKSPACE_IMAGES#custom.css" 
      type="text/css" />

The results can be seen here:




Conclusion...
So that's it. Then in order to add more quick pick links to other select items, just create other page items referencing the QUICK_PICK database procedure as detailed in step 2.

Limitation: Currently the css doesn't work in IE, only Firefox, so the links just display as normal text. When I find out why and will post an update.

Hope it helps,
Anthony



Update...
Following a discussion on the APEX forum regarding this post, I have improved the QUICK_PICK procedure so that it can handle a query containing bind variables. This is making use of the in-built function WWV_FLOW_UTILITIES.GET_BINDS(), with a technique proposed in the forums here.


Another update...
Please note, if you are working in APEX 4.0 or above, the item type used to display the quick pick links has changed slightly, as part of a wider item consolidation that took place in the 4.0 release. You should use the 'Display Only' item type, with a 'Setting > Based On' attribute equal to 'Output of PL/SQL Code' and then define the call to the QUICK_PICK procedure in the 'PL/SQL Code which emits HTML' attribute. If you have written this in a release prior to 4.0, then when upgrading you are automatically migrated to use these new settings.

6 comments:

Edu said...

Hi Anthony,

I have follow your example and creat a Dyanmic quick pick!

That's a great examle, very good explained and very useful.

My only problem is that in my case i dont return only a "name" , i return a paragraph, that should contein "enter's" and "spaces".
In my case should return something like this P2_JOB:

SALESMAN

22 year old
Apex Developer

But it return's:

SALESMAN22 year oldApex Developer

What have i to change to display text formated?

Thank's in advance

Anthony Rayner said...

edu,

For an answer to this question, please see the OTN forums here:

http://forums.oracle.com/forums/thread.jspa?threadID=676325&tstart=15

Anthony.

Rachna said...

Hi Anthony,

I am very new in Apex. I need to create a form based on one table (A) which should have only one editable field. If a user updates some thing, the record should insert in table B not in table A.
For ex; If a user updates the value of salary from $10 to $20, Table B should be updated with the new record and the form should show the updated value.

Public said...

Rachna,

Can you email me directly about this, much easier. Are you sure you want a form on table A when it sounds like your not actually updating in table A? What is your exact use case?

Anthony.

Carlos said...

Thanks by this piece of code

I just put it in place and works great

Carlos R

Christian said...

The kind of nifty stuff that takes APEX from good to grrreat.

Regards Christian - Copenhagen - Denmark