This discussion is archived
12 Replies Latest reply: Jul 31, 2013 10:41 PM by Nicolette RSS

How to use Ajax Get Multiple Values in Tabular form?

LC Newbie
Currently Being Moderated

Hi All-

 

I am trying to use AJAX to get multiple values in tabular form by using Denes Kubicek's example in the following link -

http://apex.oracle.com/pls/otn/f?p=31517:239:9172467565606::NO:::

 

Basically, I want to use the drop down list to populate rest of the values on the form.

 

I have created the example(Ajax Get Multiple Values, application 54522) on Oracle site -

 

 

http://apex.oracle.com/pls/apex/f?p=4550:1:0:::::

Workspace: iConnect

login: demo

password: demo

 

 

I was able to duplicate his example on page 1 (home page).

However, I want to use system generate tabular form to finish this example, and was not able to populate the data correctly.

 

Page 2 (method 2) is the one that I am having trouble to populate the column values. When I checked application item values in Session, and the values seems to be populated correctly.

This is what I have done on this page:

 

1. Create an Application Process On Demand - Set_Multi_Items_Tabular2:

 

DECLARE
  v_subject my_book_store.subject%TYPE;
  v_price my_book_store.price%TYPE;
  v_author my_book_store.author%TYPE;
  v_qty NUMBER;
  CURSOR cur_c
  IS
  SELECT subject, price, author, 1 qty
  FROM my_book_store
  WHERE book_id = :temporary_application_item2;
BEGIN
  FOR c IN cur_c
  LOOP
  v_subject := c.subject;
  v_price := c.price;
  v_author := c.author;
  v_qty := c.qty;
  END LOOP;
  OWA_UTIL.mime_header ('text/xml', FALSE);
  HTP.p ('Cache-Control: no-cache');
  HTP.p ('Pragma: no-cache');
  OWA_UTIL.http_header_close;
  HTP.prn ('<body>');
  HTP.prn ('<desc>this xml genericly sets multiple items</desc>');
  HTP.prn ('<item id="f04_' || :t_rownum || '">' || v_subject || '</item>');
  HTP.prn ('<item id="f05_' || :t_rownum || '">' || v_price || '</item>');
  HTP.prn ('<item id="f06_' || :t_rownum || '">' || v_author || '</item>');
  HTP.prn ('<item id="f07_' || :t_rownum || '">' || v_qty || '</item>');
  HTP.prn ('</body>');
END;




2. Create two application items - TEMPORARY_APPLICATION_ITEM2, T_ROWNUM2

 

3. Put the following in the Page Header:

<script language="JavaScript" type="text/javascript">
function f_set_multi_items_tabular2(pValue, pRow){
    var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
'APPLICATION_PROCESS=Set_Multi_Items_Tabular2',0);
if(pValue){
get.add('TEMPORARY_APPLICATION_ITEM2',pValue)
get.add('T_ROWNUM2',pRow)
}else{
get.add('TEMPORARY_APPLICATION_ITEM2','null')
}
    gReturn = get.get('XML');
    if(gReturn){
        var l_Count = gReturn.getElementsByTagName("item").length;
        for(var i = 0;i<l_Count;i++){
            var l_Opt_Xml = gReturn.getElementsByTagName("item")[i];
            var l_ID = l_Opt_Xml.getAttribute('id');
            var l_El = html_GetElement(l_ID);    
            if(l_Opt_Xml.firstChild){
                var l_Value = l_Opt_Xml.firstChild.nodeValue;
            }else{
                var l_Value = '';
            }
            if(l_El){
                if(l_El.tagName == 'INPUT'){
                    l_El.value = l_Value;
                }else if(l_El.tagName == 'SPAN' && l_El.className == 'grabber'){
                    l_El.parentNode.innerHTML = l_Value;
                    l_El.parentNode.id = l_ID;
                }else{
                    l_El.innerHTML = l_Value;
                }
            }
        }
    }
    get = null;
}
</script>

Add the follwing to the end of the above JavaScript:

<script language="JavaScript" type="text/javascript">
function setLOV(filter, list2)
{
 var s = filter.id;
 var item = s.substring(3,8);
 var field2 = list2 + item;
 
 f_set_multi_items_tabular2(filter, field2);
}

 

 

4. Tabular form query:

select
"BOOK_ID",
"BOOK",
"SUBJECT",
"PRICE",
"AUTHOR",
"QTY",
"BOOK_ID" BOOK_ID_DISPLAY
from "#OWNER#"."MY_BOOK_STORE"




 

5. In Book_ID_DISPLAY column attribute:

Add the following code to element attributes: onchange="javascript:f_set_multi_items_tabular2(this.value,'#ROWNUM#');"

Changed to -> onchange="javascript:setLOV(this,'f03');"

 

Now,  T_ROWNUM2 returns value as f03_0001. But, TEMPORARY_APPLICATION_ITEM2 returns as [object HTMLSelectElement]...

 

Please help me to see how I can populate the data with this tabular form format. Thanks a lot in advanced!!!

 

Ling

 

Updated code in Red..

  • 1. Re: How to use Ajax Get Multiple Values in Tabular form?
    AcrossTheUniverse Newbie
    Currently Being Moderated

    When I work on such problem, I return a JSON (http://json.org) string as a response and then parse it using eval javascript function, that way I get a javascript array and then I fill in the values from array to input fields using jQuery.

  • 2. Re: How to use Ajax Get Multiple Values in Tabular form?
    Nicolette Expert
    Currently Being Moderated

    Ling

     

    By just looking at your code the problem you're facing is probably that the id's of the elements in the html and produced by the ondemand process don't match.


    If apex self is creating the id's of the tabular form input items it lpad's the rownumber with 0 (zero) to 4 characters. Meaning the id is f05_0001 for the first row.

    #ROWNUM# is just the number.

    So your code will produce an id of f05_1 for the first row.

    In your ondemand process use lpad to create an id of f05_0001.


    In the code from Denes Kubicek's he is setting the id for the input elements by using apex_item (with p_id parameter) in the report region source.

    And because he doesn't add any leading zero's to the id's here he doesn't need to add them in the ondemand process either.


    Nicolette

  • 3. Re: How to use Ajax Get Multiple Values in Tabular form?
    LC Newbie
    Currently Being Moderated

    Hi Nicolette

     

    I reviewed the application item, T_ROWNUM2, and it returns a number only as you stated.

    But, I am still not clear how all these work and was wondering if you can give a suggestion on how I may be able to fix this?

     

    Thank you!!

    Ling

  • 4. Re: How to use Ajax Get Multiple Values in Tabular form?
    LC Newbie
    Currently Being Moderated

    Hi,

     

    Does anyone have any ideas and can help me out on this puzzle??


    Thank you,

    Ling

  • 5. Re: How to use Ajax Get Multiple Values in Tabular form?
    Nicolette Expert
    Currently Being Moderated

    Ling

     

    Lets start with looking at what the javascript code is doing.

     

    function f_set_multi_items_tabular(pValue, pRow){
      /*This will initiate the url for the demand process to run*/
      var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
                              'APPLICATION_PROCESS=Set_Multi_Items_Tabular',0);
      if(pValue){
        /*If there is an value than submit item name with value*/
        get.add('TEMPORARY_APPLICATION_ITEM',pValue)
        get.add('T_ROWNUM',pRow)
      }else{
        /*Else set the item TEMPORARY_APPLICATION_ITEM to null*/
        get.add('TEMPORARY_APPLICATION_ITEM','null')
      }
      /*Submit the url and te returned document is of type XML*/
      gReturn = get.get('XML');
      if(gReturn){
        /*There is something returned*/
        var l_Count = gReturn.getElementsByTagName("item").length;
        /*For all elements of the tag item*/
        for(var i = 0;i<l_Count;i++){
          /*Get the item out of the XML*/
          var l_Opt_Xml = gReturn.getElementsByTagName("item")[i];
          /*Get the id of the item*/
          var l_ID = l_Opt_Xml.getAttribute('id');
          /*Get the element in the original page with the same id as
          **the item we have in the XML produced by the ondemand process
          */
          var l_El = html_GetElement(l_ID);
          /*Now get the value of the item form the XML*/
          if(l_Opt_Xml.firstChild){
            var l_Value = l_Opt_Xml.firstChild.nodeValue;
          }else{
            /*There is no value*/
            var l_Value = '';
          }
          if(l_El){
            /*There is an element with the same id as the item we are processing*/
            if(l_El.tagName == 'INPUT'){
              /*The element is an input item just set the value*/
              l_El.value = l_Value;
            }else if(l_El.tagName == 'SPAN' && l_El.className == 'grabber'){
              /*If it is a span elment and has the class grabber
              **Then set the innerHTML of the parent to the value
              **and the id of the parent to the id
              */
              l_El.parentNode.innerHTML = l_Value;
              l_El.parentNode.id = l_ID;
            }else{
              /*Else set the value as innerHTML*/
              l_El.innerHTML = l_Value;
            }
          }
        }
      }
      get = null;
    }
    

    Now where it went wrong in your initial post

    The XML that was returned by your XML process would be something like

    <body>
      <desc>this xml genericly sets multiple items</desc>
      <item id="f02_1">CSS Mastery</item>
      <item id="f03_1">22</item>
      <item id="f04_1">Andy Budd</item>
      <item id="f05_1">1</item>
    </body>
    

    When you don't use apex_item to create your tabular form a item in the table will look like

    <input id="f02_0001" type="text" value="CSS Mastery" maxlength="2000" size="16" name="f05" autocomplete="off">
    

     

    Notice the id's f02_1 and f02_0001 don't match.

     

    So to make it work the XML would have to look like

    <body>
      <desc>this xml genericly sets multiple items</desc>
      <item id="f02_0001">CSS Mastery</item>
      <item id="f03_0001">22</item>
      <item id="f04_0001">Andy Budd</item>
      <item id="f05_0001">1</item>
    </body>
    

    To do that simply use lpad in the ondemand process like

    HTP.prn ('<item id="f02_' || lpad(:t_rownum,4,'0') || '">' || v_subject || '</item>');
    HTP.prn ('<item id="f03_' || lpad(:t_rownum,4,'0') || '">' || v_price || '</item>');
    HTP.prn ('<item id="f04_' || lpad(:t_rownum,4,'0') || '">' || v_author || '</item>');
    HTP.prn ('<item id="f05_' || lpad(:t_rownum,4,'0') || '">' || v_qty || '</item>');
    

     

    Keep in mind that the above is based on your original post and #ROWNUM# not being lpadded with zero's.

     

    Nicolette

  • 6. Re: How to use Ajax Get Multiple Values in Tabular form?
    Nicolette Expert
    Currently Being Moderated

    Ling

     

     

    In reaction on the changes you made on your original post.

     

    this in javascript is an object on submiting there is a to_char done on the object resulting in the string you see in TEMPORARY_APPLICATION_ITEM2.

    In  setLOV instead of  f_set_multi_items_tabular2(filter, field2); do  f_set_multi_items_tabular2(filter.value, field2); like you did when you where calling f_set_multi_items_tabular2 directly.

     

     

    I was going with the assumption that you were understanding the pl/sql code. If I'm wrong in that assumption let me know and I will try to give an explanation like I did with the javascript function.

     

     

    Nicolette

  • 7. Re: How to use Ajax Get Multiple Values in Tabular form?
    LC Newbie
    Currently Being Moderated

    Hi Nicolette

     

    Thank you for the detail explanation in the JavaScript code above.

    I added the new function setLOV, so, it is padding the id correctly now as I can see in the Application Items values in the Session.

     

    However, the TEMPORARY_APPLICATION_ITEM2 returns as [object HTMLSelectElement]...instead of the ID.

    Is it possible for you to go in to my sample application to take a look?

     

    Thanks again for your response!

    Ling

  • 8. Re: How to use Ajax Get Multiple Values in Tabular form?
    Nicolette Expert
    Currently Being Moderated

    Ling

     

    In my post of Jul 31, 2013 9:26 PM  (GMT+0200 (West-Europe (summertime))) I have shown you a correction to the pl/sql code so you would not need the extra javascript function.

    And for the [Object HTML SelectElement] see my post of Jul 31, 2013 9:52 PM. Basically you are passing the whole element instead of only it's value to  f_set_multi_items_tabular2.

     

    Nicolette

  • 9. Re: How to use Ajax Get Multiple Values in Tabular form?
    LC Newbie
    Currently Being Moderated


    Hi Nicolette

     

    Yes, you are right. I made the following change as you have suggested:

    f_set_multi_items_tabular2(filter.value, field2);

     

    Here are the values returned from Sesscion:

    ApplicationItem ValueItem Name
    545223TEMPORARY_APPLICATION_ITEM2
    54522f03_0003T_ROWNUM2

     

    Seems to be right now, right? Because the value are still not been assigned correctly yet.

     

    Decided to keep the java script funtion, becuase the Ipad function won't work right after row 10...

     

    Thanks again!

    Ling

  • 10. Re: How to use Ajax Get Multiple Values in Tabular form?
    Nicolette Expert
    Currently Being Moderated

    Ling

    LC wrote:

     

     

    ApplicationItem ValueItem Name
    54522 3 TEMPORARY_APPLICATION_ITEM2
    54522 f03_0003 T_ROWNUM2

     

    No T_ROWNUM2 should be 0003.

     

    I made a copy of your page to make some corrections.

     

    There were several problems.

    First you where submiting T_ROWNUM2 while you where using :t_rownum in de pl/sql code.

    I have changed the item name in the f_set_multi_items_tabular2.

     

    Secondly you where now setting the rownumber to f03_0001 for the first row.

    Resulting in the folowing XML being returned.

    <body><desc>this xml genericly sets multiple items</desc>
    <item id="f05_f03_0001">CSS Mastery</item>
    <item id="f06_f03_0001">22</item>
    <item id="f07_f03_0001">Andy Budd</item>
    <item id="f08_f03_0001">1</item>
    </body>

    I have changed in the show_lov the following.

    var item = s.substring(4,8);

    var field2 = item;

     

    I also got some compile error on the pl/sql code there was a ); missing at the end of the last item. Corrected that too.

     

    But why do you think lpad won't work for rows 10 and more.

    lpad('10',4,'0')  will give '0010'

    lpad('100',4,'0') will give '0100'

    lpad('1000',4,'0') will give '1000'

    So unless you have more than 9999 rows you wouldn't have any problems.

     

    Nicolette

  • 11. Re: How to use Ajax Get Multiple Values in Tabular form?
    LC Newbie
    Currently Being Moderated

    Hi Nicolette

     

    Thank you very much for all of your help!!!

     

    Can I ask one more question please...

    How do you read the xml returning code?

     

    Thanks!

    Ling

  • 12. Re: How to use Ajax Get Multiple Values in Tabular form?
    Nicolette Expert
    Currently Being Moderated

    Ling

     

    LC wrote:

    How do you read the xml returning code?

     

    By debuging the javascript code in the browser.

    XML is in the responce of the get object (get.responce).

    If you look at the code on page 3 you will notice that I have also added the line var gReturn; to  f_set_multi_items_tabular2.

    By declaring the variable like that it will so up while debuging the code.

     

    Nicolette

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points