vrijdag 9 december 2011

Dynamic quick picks

Quick picks are those little red links with predefined values you see under (mostly) listboxes in the APEX builder.


They offer the user a flexibilty because the user can fill an item with a single click.Since APEX 4.0, you can easily add quick picks to your items by selecting "Yes" in the Show quick picks listbox in the Quick picks section of the item.


However, you can only add static data to the quick picks. Sometimes it would be very handy to make the quick picks dynamic. Let's say you have a CV application where you can add language skills to employees and suppose you want to have a language selectlist with quick picks of the 4 most used languages.

In 2008, long before APEX 4.0 was released, Anthony Rayner already wrote a blogpost about creating your own dynamic quick picks. Now, since APEX 4.0, you can create a plug-in for these kind of user-interface enhancements. In this blogpost I will show you how to do that.

Preparations:

We are going to use the following tables:

Table CBR_LANGUAGES:

LNG_ID           NUMBER(5)
LANG_DESCRIPTION VARCHAR2(20)

Table CBR_EMP_LANG:

ELG_ID NUMBER(5)
LNG_ID NUMBER(5)
SPEAK  VARCHAR2(30)
WRITE  VARCHAR2(30)
EMP_ID NUMBER(5)

sequence CBR_ELG_SEQ

Steps:

- Go to your application
- click on shared components
- in the user interface section, click plug-ins
- click the create button
- in the name section, enter a name for the plug-in, ie Dynamic quick pick, in the name text field
- in the internal name text field, enter a unique name, ie com.ciber.dynamic.quick.pick. This will be used internally by APEX
- in the type selectlist, make sure item is selected
- in the source section, enter the following code:

function render_quickpick (p_item                in apex_plugin.t_page_item
                          ,p_plugin              in apex_plugin.t_plugin
                          ,p_value               in varchar2
                          ,p_is_readonly         in boolean
                          ,p_is_printer_friendly in boolean )
return apex_plugin.t_page_item_render_result
is
  TYPE query_type  is REF CURSOR;
  TYPE query_type1 is REF CURSOR;
  c_query_cursor query_type;
  c_topn_query   query_type1;
  l_result       apex_plugin.t_page_item_render_result;
  l_query        apex_application_page_items.attribute_01%type := p_item.attribute_01;
  l_topn_query   apex_application_page_items.attribute_02%type := p_item.attribute_02;
  l_return       varchar2(20);
  l_display      varchar2(20);
  l_name         varchar2(30);
begin
  --
  -- get an APEX input name
  l_name := apex_plugin.get_input_name_for_page_item (p_is_multi_value => true );
  sys.htp.p('<select name="'||l_name||'" id="'||p_item.name||'" size="1" class="selectlist">');
  --
  -- create the selectlist
  open c_query_cursor for l_query;
  loop
    fetch c_query_cursor into l_display, l_return;
    exit when c_query_cursor%notfound;
    if l_return = p_value
    then
      sys.htp.p('<option value="'||l_return||'" selected="selected">'||l_display||'</option>');
    else
      sys.htp.p('<option value="'||l_return||'">'||l_display||'</option>');
    end if;
  end loop;
  sys.htp.p('</select>');
  sys.htp.p('<br/>');
  --
  -- The top-n query is optional, if empty, use the l_query
  if l_topn_query is null
  then
    l_topn_query := l_query;
  end if;
  --
  -- create the quick picks
  open c_topn_query for l_topn_query;
  loop
    fetch c_topn_query into l_display, l_return;
    exit when c_topn_query%notfound;
    sys.htp.p('<a href="javascript:$s('||''''||p_item.name||''''||','||''''||l_return||''''||','||''''||l_display||''''||');" class="itemlink">'||'['||l_display||']'||'</a>');
  end loop;
  --
  return l_result;
end render_quickpick;



- in the callbacks section, enter render_quickpick in the render function name text field
- in the standard attributes check the following attributes:

is visible
has element attributes
session state changeable
has source attributes
has width attributes

- click the create button but stay in the page.
- in the custom attributes section, click the add attribute button
- fill in the items with the following data:

scope: component
attribute: 1
display sequence: 10
Label: query
type: sql query
required: yes
minimum columns: 2, maximum columns:2


- click create and create another
- enter the following data:

scope: component
attribute: 2
display sequence: 20
Label: Top-N query
type: sql query
required: no
minimum columns: 2, maximum columns:2

- click create

The plug-in is ready. Now we will create a page that makes use of the plug-in.

- Go to the application builer and click the create page button.
- select form
- select form on a table or view
- in the table/view name selectlist, select cbr_emp_lang. click next
- click next
- select do not use tabs and click next
- click the radio button "select primary key" and in the primary key column 1 selectlist, select elg_id. click next
- select existing sequence and in the sequence selectlist, select cbr_elg_seq. click next
- transfer all columns to the right and click next
- click next
- enter pagenumber where APEX should navigate to when submitting or cancelling. You can also enter the same pagenumbers you are creating. click next
- click finish
- click edit page

Now we will include the plug-in into the application

- Click on the PXX_LNG_ID item (where XX is the page number)
- In the display as selectlist, select Dynamic quick pick [Plug-in]
- In the query text area (in the settings section), enter the following query:

SELECT LANG_DESCRIPTION D
,      LNG_ID R
FROM   CBR_LANGUAGES


- In the top-N query text area, enter the following query:

SELECT LNG.LANG_DESCRIPTION D
,      TOP_LNG.LNG_ID R
FROM   (SELECT LNG_ID
        ,      COUNT(LNG_ID) ORDERING
        FROM   CBR_EMP_LANG
        GROUP BY LNG_ID
        ORDER BY 2 DESC) TOP_LNG
,      CBR_LANGUAGES LNG
WHERE  TOP_LNG.LNG_ID = LNG.LNG_ID
AND    ROWNUM < 5


- click apply changes.

The page and the plug-in are ready. Click run to see the result.

11 opmerkingen:

  1. Very cool! Excellent plugin example too.

    BeantwoordenVerwijderen
  2. Everything is fine, am happy about your blog. Thanks admin for sharing the unique content, you have done a great job I appreciate your effort and I hope you will get more positive comments from the web users.
    Hadoop Training in Chennai
    Hadoop Training
    Best Hadoop Training in Chennai
    Best Hadoop Training Institute in Chennai

    BeantwoordenVerwijderen
  3. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    iOS Training in Chennai

    BeantwoordenVerwijderen
  4. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
    Digital Marketing Company in India
    seo Company in India

    BeantwoordenVerwijderen
  5. Very nice post here and thanks for latest smartphone applications it .I always like and such a super colors of phone for these post.Excellent and very cool idea and great models and different kinds of the more information's.

    Digital Marketing Course in Chennai

    BeantwoordenVerwijderen
  6. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Android App Development Company

    BeantwoordenVerwijderen
  7. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    iOS App Development Company
    iOS App Development Company

    BeantwoordenVerwijderen
  8. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...
    Fitness SMS
    Fitness Text
    Salon SMS
    Salon Text
    Investor Relation SMS
    Investor Relation Text
    Mobile Marketing Services
    mobile marketing companies
    Sms API

    BeantwoordenVerwijderen
  9. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Mobile Marketing Service
    Mobile Marketing Companies
    Sms API
    Texting API

    BeantwoordenVerwijderen
  10. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...
    Texting API
    Text message marketing
    Digital Mobile Marketing
    Mobile Marketing Services
    Mobile marketing companies
    Fitness SMS

    BeantwoordenVerwijderen