Thursday, December 18, 2008

Using "Prompt LOV" to replace "Popup Key LOV"

Another post about jQuery in APEX! :) I am really happy to see the interest the APEX Community has for jQuery.

After reading Dan McGhan's blog post concerning jQuery Impromptu, I decided to try this jQuery extension. For my demo, I decided to replace a "Popup Key LOV (Displays description, returns key value)" with a "Prompt LOV".
I was able to reuse code from previous demos to accelerate the development.

Here is a working example of the Prompt LOV & Calendar demo.

How does it works?

1)I assume you check and understand these demos:
A)Popup in Report
B)AJAX request Page Report
C)jQuery Datepicker

Now... we can continue to the next step. :)

2)Replace the existing Popup LOV with a Prompt. The prompt will display a report from another page(in this case, it's the page 42). The report is requested using AJAX.

This is the JavaScript code you need:

$().ready(function() {
//Replace existing POPUP-LOV
$("#P41_DEPTNO_fieldset a")

//request DEPT Report
var startTag = '<apex:ajax>';
var endTag = '</apex:ajax>';
var start = data.indexOf(startTag);

if (start > 0) {
data = data.substring(start+startTag.length);
var end = data.indexOf(endTag);
data = data.substring(0,end);

//Prompt the user with the LOV
$.prompt(data,{callback: function(v,m){
//Put the original pFlowStepId value to submit the page and process it

//Workaround to make the report "pagination" and "order by" work

3)The LOV is a Report Region using an SQL Query.

This is the query used to populate the report:

SELECT '<a href="#" onclick="select_lov_entry(this,'''||dname||''','''||deptno||''');">'||dname||'</a>' AS lov_entry
FROM dept

This is the JavaScript code you need for selecting a value:

function select_lov_entry(pNd,x,y) {

//Copy an existing function used by the original POP-UP LOV
//Small modifications are required (we are not referencing an opener window)
function passBack(x,y){
if (document.forms["wwv_flow"].p_t09.length > 1){
var l_field = document.forms["wwv_flow"].p_t09[8];
l_field.value = x;
document.forms["wwv_flow"].p_t08[8].value = y;
var l_field = document.forms["wwv_flow"].p_t09;
l_field.value = x;
document.forms["wwv_flow"].p_t08.value = y;
if(l_field.getAttribute('onchange') || l_field.onchange){
if(!(l_field.disabled || l_field.type == 'HIDDEN')){

function highlight_selection(pNd) {
jQuery(pNd).parent().css({'background-color' : '#55AAFF'});

I have to rethink about how to passBack values from the Prompt LOV to the "P41_DEPTNO_fieldset" items. This is for another blog post. ;)

I hope you enjoy this demo!

Friday, December 12, 2008

how to build a Google Gadget with data fetched from an APEX Application Process

Last week, I release a Google Gadget to publish the weekly release of Insum's Web 2.0 Demos.
I'll explain how I did it.

Google Gadget... what's that?!
It's an XML file defining every aspect of your gadget(module). The words "gadget" and "module" can both be used to describe a Google Gadget.

The gadget definition contains 3 sections:

1)Module Preferences
The general information of my gadget is defined in this section. Here is the list of attributes for which I set values.

I put a default value for the height. Users can update this value using the gadget's settings (build using the "users preferences"). It's also possible to auto-adjust the gadget height.

2)Users Preferences
The settings of my gadget are defined in this section. Here's the list of settings available to the users.

The "itemCount" defines how many items to display. This can be useful if my gadget displays many demos and you don't want the gadget to take too much space on your web page.

The "fHeight" is a flag to set the auto-adjust height to "true" or "false".

The "nHeight" defines the height of the gadget if the auto-adjust height is set to "false".

The HTML/JavaScript code is written in this section.

Load the required APIs:

<script src=""></script>
// Load jQuery
google.load("jquery", "1.2.6", {uncompressed:true});

// Load Google Visualization API
google.load('visualization', '1', {packages: ['table']});

I use Google Visualization API to display the data in a data grid.

Instantiate an "_IG_Prefs" object:
var prefs = new _IG_Prefs(__MODULE_ID__);

We can access the values of the users preferences using the "getInt" and "getString" functions.

Declare the setHeight function

function setHeight() {
_IG_AdjustIFrameHeight(prefs.getInt('fHeight') ? -1 : prefs.getInt('nHeight'));
This function will be called each time the data is refreshed.

Declare the draw function

function draw() {
var url = ""+prefs.getString('itemCount')+"";

_IG_FetchContent(url, function(data) {
data = eval(data);
if ((nb_demo = data.length) > 0) {
var table_data = new google.visualization.DataTable();
table_data.addColumn('string', 'Title');

for (i = 0; i < nb_demo; i++) {
table_data.setCell(i, 0, data[i].url, data[i].title);

table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(table_data, {showRowNumber: true});

// Add our selection handler., 'select', function() {
var row = table.getSelection()[0].row;, 0));

{refreshInterval: 0});

The "_IG_FetchContent" function will call the GET_WEEKLY_RELEASE Application Process. The callback function handle the data returned by the Application Process. I am using JSON to format the data and I use jQuery to parse it. I set the "refreshInterval option" to 0 because I don't want the results to be cached. This is not recommended!!!... I'll put it to 360(6hrs) when I am done modifying my gadget.

To build my first gadget, I was using an iframe to display the page 204 of my application. Because I didn't like the look and feel, I decided to build it using the Google Visualization API and use an APEX Application Process to feed the data.

I hope I'll see other Google Gadget using APEX Application Processes or MOD_PLSQL.

Friday, December 5, 2008

Google Visualization API : Organizational Chart

It's possible display the relationship of your company employees using the Google Visualization: Organizational Chart.

How it works:
I use jQuery AJAX to call an application process called GET_EMPLOYEES_RELATIONSHIP.
The response is in JSON. I use jQuery to handle the JSON object.
You need to store your chart data inside a "google.visualization.DataTable()" object.
I use the same data table to feed the Table Chart and the Org. Chart.

I created 2 examples.

1) Single Tree View

Your can see a working example of the employees relationship Organizational Chart demo (1).

This is how I define a data table cell:

for (i = 0; i < emp_count; i++) {
l_data_table.setCell(i, 0, data[i].emp_ename);
l_data_table.setCell(i, 1, data[i].mgr_ename);

2) Multiple Trees View

Your can see a working example of the employees relationship Organizational Chart demo (2).

This is how I define a data table cell:

for (i = 0; i < emp_count; i++) {
l_data_table.setCell(i, 0, data[i].emp_empno, data[i].emp_ename);
l_data_table.setCell(i, 1, data[i].mgr_empno, data[i].mgr_ename);

Enjoy! :)

I'm not done yet with the Google Visualization API... there's still plenty of charts to try out.

Google Gadget - "Insum's Web 2.0 Demos - Weekly Release"

I decided to try the Google Gadget API after reading Bradley's post. I end up creating a notification gadget to publish "Insum's Web 2.0 Demos - Weekly Release". It's now possible to stay up-to-date if you add this gadget to your blog, website, and/or iGoogle page.

I'll write an article about the creation of such gadget.