12 Replies Latest reply: Aug 1, 2013 12:41 AM by Nicolette RSS

    How to use Ajax Get Multiple Values in Tabular form?

    LC

      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-Oracle

          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

            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

              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

                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

                  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

                    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

                      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

                        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


                          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

                            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

                              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

                                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