Wednesday, November 28, 2012

Using the Modal Page plugin as a modal popup list in a tabular form

Props

I use a plugin by SkillBuilders, the Modal Page, which is free by the way. Pay them a visit here: Skillbuilders.com

Example application

Can be found over at http://apex.oracle.com/pls/apex/f?p=69293
You can download the application aswell. It comes with supporting objects (2 tables and some seeding data) and is pretty much selfcontained. The application is developed in 4.1 and imports into 4.2 without problems.

Diving straight in

Page 1

Tabular form: wizard generated and based on table DEMO_BOOK_STORE. I added in a column to be used as a link column (popuplist):
select 
"ROWID",
"BOOK_ID",
"SUBJECT",
"PRICE",
"AUTHOR",
"TOPIC",
NULL popuplist
from "#OWNER#"."DEMO_BOOK_STORE"
I hid the book_id and topic columns. I specified link column properties for column popuplist
  • Link Text: Select Item
  • Link Attributes: onclick="return false;" class="modalshow"
  • Target: Page in this Application
  • Page: 2
Add Button: I changed the button action from "Redirect to URL" to "Defined by Dynamic Action". This is because an extra action needs to happen when a row is added. In page attributes: Javascript:
var gaInputMapping = new Array();
var gRowSelect, gTriggerTable;
$().ready(function(){
   $("input[name='fmap']").each(function(index){ 
      var lsHeader = $(this).val(),
          lsName = 'f'+('0'+(index+1)).slice(-2),
          lsNName = $("[name='"+lsName+"']")[0].nodeName,
          lMap = {"header":lsHeader,"name":lsName,"nodeName":lsNName};
      gaInputMapping.push(lMap);
   });
});
function getNameWithHeader(pHeader){
   var lsName;
   $.each(gaInputMapping, function(index){
      if(gaInputMapping[index].header==pHeader.toUpperCase()){
         lsName= gaInputMapping[index].name;
      };
   });
   return lsName;
};
function getHeaderWithName(pName){
   var lsHeader;
   $.each(gaInputMapping, function(index){
      if(gaInputMapping[index].name.toUpperCase()==pName.toUpperCase()){
         lsHeader= gaInputMapping[index].header;
      };
   });
   return lsHeader;
};
function getSelector(pHeader){
   var lsSel;
   $.each(gaInputMapping, function(index){
      if(gaInputMapping[index].header==pHeader.toUpperCase()){
         lsSel= gaInputMapping[index].nodeName + "[name='" + gaInputMapping[index].name + "']";
      };
   });
   return lsSel;
};
function getObject(pHeader){
   var lRet;
   $.each(gaInputMapping, function(index){
      if(gaInputMapping[index].header==pHeader.toUpperCase()){
         lRet= gaInputMapping[index];
      };
   });
   return lRet;
};
function setRowDetails(pTable, pRow, pItemId){
   $.post('wwv_flow.show', 
          {"p_request"      : "APPLICATION_PROCESS=get_item_details",
           "p_flow_id"      : $v('pFlowId'),
           "p_flow_step_id" : $v('pFlowStepId'),
           "p_instance"     : $v('pInstance'),
           "x01"            : pItemId},
           function(data){              
              var lItem = $.parseJSON(data);
              $.each(lItem, function(){
                 var lSelector = getSelector(this.COLUMN);
                 if(lSelector){
                    $(lSelector, pTable.find('tbody > tr').eq(pRow)).val(this.VALUE).change();
                 }
              })
           });
};
HTML Header and Body Attribute: HTML Header:
<script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8.14/ui/minified/jquery.ui.button.min.js"></script>
4 Dynamic actions:
"Button Icons"
  • Event: After Refresh
  • Selection Type: Region
  • Region: tabular form region
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: checked
  • Code:
$(this.triggeringElement).find("a.modalshow").button({icons:{secondary:'ui-icon-circle-arrow-n'}});
"Modal closed: fetch details"
  • Event: Manual Close [SkillBuilders Modal Page]
  • Selection Type: DOM Object
  • DOM Object: document
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: unchecked
  • Code:
if(this.data.modalPageId=='selectitem'){
   lItemId = this.data.modalPageCloseValue.item_id;
   if(lItemId){
      setRowDetails(gTriggerTable, gRowSelect, lItemId);
   };
};
"Show modal"
  • Event: Click
  • Selection Type: jQuery Selector
  • jQuery Selector: .modalshow
  • Event Scope: live
True action: Execute javascript code
  • Fire on page load: unchecked
  • Code:
gTriggerTable = $(this.triggeringElement).closest('table');
gRowSelect = $(this.triggeringElement).closest('table').find('tbody > tr').index($(this.triggeringElement).closest('tr'));
True action: SkillBuilders Modal Page [Plug-in]
  • Fire on page load: unchecked
  • Dialog Title: Select an item
  • URL Location: Statically Defined
  • Static URL: f?p=&APP_ID.:2:&APP_SESSION.:::2:::
  • Dialog Height/Width Mode: Static by pixels
  • Height: 700
  • Width: 600
  • Modal Page ID: selectitem
"Add Row"
  • Event: Click
  • Selection Type: Button
  • Button: ADD (Add Row)
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: unchecked
  • Code:
addRow();
$(this.affectedElements[0]).find("a.modalshow:last").button({icons:{secondary:'ui-icon-circle-arrow-n'}});
  • Affected Elements:
    • Selection Type: Region
    • Region: tabular form region
Page Process:
  • Type: PL/SQL anonymous block
  • Process Point: On Demand
  • Code:
declare
   lJSON VARCHAR2(1000);
begin
   select '[{"COLUMN":"BOOK_ID", "VALUE":"' || book_id ||'"},'||
           '{"COLUMN":"SUBJECT", "VALUE":"' || subject ||'"},'||
           '{"COLUMN":"PRICE",   "VALUE":"' || price   ||'"},'||
           '{"COLUMN":"AUTHOR",  "VALUE":"' || author  ||'"},'||
           '{"COLUMN":"TOPIC",   "VALUE":"' || topic   ||'"}]'
     into lJSON
     from demo_book_list
    where book_id = apex_application.g_x01;

   htp.p(lJSON);
end;

Page 2

Page template: I made a copy of the popup page template in the theme. In my example I am using theme 23 and since the popup template has a table layout with a useless right column, I removed the table markup and just replaced it with a div. This is my “Body” code:
#SUCCESS_MESSAGE##NOTIFICATION_MESSAGE##GLOBAL_NOTIFICATION#
<div>
#REGION_POSITION_01#
#REGION_POSITION_02#
#BOX_BODY#
#REGION_POSITION_04#
#REGION_POSITION_05#
#REGION_POSITION_06#
#REGION_POSITION_07#
</div>
Standard report based on DEMO_BOOK_LIST, with 3 columns: book_id, subject, author. Edit column BOOK_ID and change heading to 'Select'. Specify Column Link attributes:
  • Link Text: Select me!
  • Link Attributes: onclick="return false;" class="modalclose"
  • Target: URL
  • URL: #BOOK_ID#
1 Dynamic Action:
"Button Icons"
  • Event: After Refresh
  • Selection Type: Region
  • Region: report region
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: checked
  • Code:
$(this.triggeringElement).find("a.modalclose")
.button({icons:{secondary:'ui-icon-circle-arrow-e'}})
.click(function(){
   var retval = {"item_id":$(this).attr('href')};
   parent.$(parent.document).apex_modal_page('close', retval);
});

Some Clarifications

javascript in page 1:
Input mappings:
var gaInputMapping = new Array();
var gRowSelect, gTriggerTable;
$().ready(function(){
   $("input[name='fmap']").each(function(index){ 
      var lsHeader = $(this).val(),
          lsName = 'f'+('0'+(index+1)).slice(-2),
          lsNName = $("[name='"+lsName+"']")[0].nodeName,
          lMap = {"header":lsHeader,"name":lsName,"nodeName":lsNName};
      gaInputMapping.push(lMap);
   });
});
This builds of the input map that apex generates for wizard generated tabular forms. This will read the map and store it in a global javascript array variable. The type of the referenced item will also be stored, meaning that whether the item is a select list or an input item.
The columns in the map are also generated in the same order as the assigned f##-arrays for each column. So the first item in the fmap array will be array f01, the second will be f02,...
<input id="fmap_001" type="hidden" value="CHECK$01" name="fmap">
<input id="fhdr_001" type="hidden" value="Select Row" name="fhdr">
<input id="fmap_002" type="hidden" value="BOOK_ID" name="fmap">
<input id="fhdr_002" type="hidden" value="Book Id" name="fhdr">
<input id="fmap_003" type="hidden" value="TOPIC" name="fmap">
<input id="fhdr_003" type="hidden" value="Topic" name="fhdr">
<input id="fmap_004" type="hidden" value="PRICE" name="fmap">
<input id="fhdr_004" type="hidden" value="Price" name="fhdr">
<input id="fmap_005" type="hidden" value="AUTHOR" name="fmap">
<input id="fhdr_005" type="hidden" value="Author" name="fhdr">
<input id="fmap_006" type="hidden" value="SUBJECT" name="fmap">
<input id="fhdr_006" type="hidden" value="Subject" name="fhdr">
The several functions below that are functions allowing easy retrieval of a column or item through either the headers or the array name.
Understand that this is a different approach from retrieving input items through the headers on a TD! In particular, hidden items can ruin your plans of referencing items. In my sample app i have 3 hidden columns: rowid, book_id and topic. Apex will generate these hidden items in the last column:
<td headers="SUBJECT">
<label class="hideMeButHearMe" for="f06_0001">Subject</label>
<input id="f06_0001" type="text" value="Easy Oracle" maxlength="2000" size="40" name="f06" autocomplete="off">
<input id="f02_0001" type="hidden" value="2" name="f02" autocomplete="off">
<input id="f03_0001" type="hidden" value="5" name="f03" autocomplete="off">
<input id="fcs_0001" type="hidden" value="2C9BDC219934F8ED33AF9602262E2391" name="fcs" autocomplete="off">
<input id="frowid_0001" type="hidden" value="AAAqsGAAjAAAWQrAAA" name="frowid" autocomplete="off">
<input id="fcud_0001" type="hidden" value="U" name="fcud" autocomplete="off">
</td>
So when you try to reference the input items through columns headers: $("td[headers='SUBJECT'] input") you'll be greeted by an array of 6 inputs. Of course, you could then add the :visible filter to only target the visible text item. But how will you deal with the hidden items then? Assign something to them? You will need to hardcode the arrays somewhere.
By reading the fmap array this can be avoided. And items which are hidden are still targetable through the headers, which is a lot more intuitive than using the f## arrays (which change when you change the column ordering or add or remove a column...). Retrieve details:
function setRowDetails(pTable, pRow, pItemId){
   $.post('wwv_flow.show', 
          {"p_request"      : "APPLICATION_PROCESS=get_item_details",
           "p_flow_id"      : $v('pFlowId'),
           "p_flow_step_id" : $v('pFlowStepId'),
           "p_instance"     : $v('pInstance'),
           "x01"            : pItemId},
           function(data){              
              var lItem = $.parseJSON(data);
              $.each(lItem, function(){
                 var lSelector = getSelector(this.COLUMN);
                 if(lSelector){
                    $(lSelector, pTable.find('tbody > tr').eq(pRow)).val(this.VALUE).change();
                 }
              })
           });
};
This will call the application process and process the data. A book id is passed in and it will receive the details. The process will return a json string:
   select '[{"COLUMN":"BOOK_ID", "VALUE":"' || book_id ||'"},'||
           '{"COLUMN":"SUBJECT", "VALUE":"' || subject ||'"},'||
           '{"COLUMN":"PRICE",   "VALUE":"' || price   ||'"},'||
           '{"COLUMN":"AUTHOR",  "VALUE":"' || author  ||'"},'||
           '{"COLUMN":"TOPIC",   "VALUE":"' || topic   ||'"}]'
     into lJSON
     from demo_book_list
    where book_id = apex_application.g_x01;
This will parse to an array of objects. Each object holds the column name and the value. I did this simply because it is easier to loop over and generalize. The javascript will loop over the returned array, and then for each column it'll find the matching input mapping and return a selector. With this selector the correct input in the same row can be manipulated. Take note of the jQuery selector:
$(lSelector, pTable.find('tbody > tr').eq(pRow)
This will find the input item within the given context: the clicked row in the clicked table. pTable and pRow are set when the 'select item' button is clicked in global variables. Dynamic action "Show Modal"
gTriggerTable = $(this.triggeringElement).closest('table');
gRowSelect = $(this.triggeringElement).closest('table').find('tbody > tr').index($(this.triggeringElement).closest('tr'));
Again, this allows for avoiding the tedious item_id parsing which includes the 'rownum'. And i prefer this way over targeting the item array and picking out one at the row index. Again, take not of the triggering of the change event on each column. When no change event is triggered, a new row will not be marked as a changed row but rather remain marked as deleted, and no insert will occur (same logic for updating a row of course).
Basically, in sequence:
  1. click the button
  2. table and row are saved in js
  3. modal page is opened
  4. item is selected
  5. modal page is closed and a return value is specified (book id)
  6. the book details are retrieved through ajax
  7. in the ajax success callback the book details are filled out into the items of the (saved) clicked row

So

It might look a little daunting, but it doesn't have to take too long. Some of the javascript can easily be put in a file and then included. The jquery buttons are also a bit extra-curricular, but i like them for being easy to implement and how they improve look and feel.
The modal page requires some setup, and you will need to create a page to be used in the modal popup. However, i consider this a strength as it allows for maximum flexibility. I'm providing this code and example as an example of how you could use the modal popup and javascript to greatly enhance a tool. This could be a tabular form, an item on a page, a button in an interactive report,...
I also implemented the ajax retrieval of the book details as part of the example. You could skip ajax and return all details from the modal page through its return value object! But this is cooler ;-)
And last but not least, you can just create and maintain a standard wizard generated tabular form. No need to grasp for apex_item generated forms this way, which is a real strength in my opinion.

3 comments:

  1. I would like to call a form (job history need to enter start date and end date for each assignment) instead of a report from my tabular form (a form that maps to student job assignments).
    Can I use the same concept, but send parameters to a regular form to enter additional info for each job assignment, or it can only work with a report ?

    ReplyDelete
  2. You can use the same technique of course. The modal page plugin after all is just a url that is opened up in a stylised iframe.

    ReplyDelete
  3. Great Stuff...Learnt Something New :)

    ReplyDelete