1 2 Previous Next 15 Replies Latest reply: Nov 30, 2012 2:31 PM by Charles A RSS

    Ajax Get Multiple Values Tabular II

    Charles A
      Hello,
      Example 'Ajax Get Multiple Values Tabular II' from Denes Kubicek
       http://apex.oracle.com/pls/otn/f?p=31517:241:5798812617243::NO 
      does not include the Cancel, Delete, Submit and Add Row buttons. But when the tabular form is created through APEX the form has those buttons. The example is not intended to insert and update data from this example. But I need to extend this powerful example to insert rows by clicking Add Row and updating rows when clicking Submit. Before I even try to implement a Insert and Update Process I need to get some information if possible on the (null) values displaying when Add Row is clicked within my apex.oracle.com example. The Ajax Get Multiple Values Tabular II from the example does not have any buttons just static data but within my application I have the standard buttons created. Can anyone assist me to remove those (null) values from being displayed based on the select statement and also having the standard text boxes appear when the Add Row button is clicked?
      SELECT    '<img src="#IMAGE_PREFIX#list_gray.gif" '
             || 'onclick="popUp2(''f?p=&APP_ID.:3:&SESSION.::::'
             || 'P3_ROWNUM:'
             || '#ROWNUM#'
             || ''', 700, 700);" '
             || 'style="cursor:pointer;">' book,
             apex_item.text (32,
                             NULL,
                             80,
                             100,
                             'style="width:290px" ',
                             'f32_' || '#ROWNUM#'
                            ) subjects,
             apex_item.text (33,
                             NULL,
                             80,
                             100,
                             'style="width:50px;text-align:right" ',
                             'f33_' || '#ROWNUM#'
                            ) prices,
             apex_item.text (34,
                             NULL,
                             80,
                             100,
                             'style="width:130px" ',
                             'f34_' || '#ROWNUM#'
                            ) authors,
             apex_item.text (35,
                             NULL,
                             80,
                             100,
                             'style="width:20px;text-align:right" ',
                             'f35_' || '#ROWNUM#'
                            ) qtys
        FROM my_book_store
      My test application on apex.oracle.com has buttons which display (null) for every apex_item.text when Add Row is clicked. Any information on this matter is greatly appreciated. Also, the data is saved in the table my_book_store but it does not display in tabular form, is there a way to display the data in tabular form based on the select statement? I am thinking that this is why the data only displays when selected.

      Edited by: Charles A on Nov 26, 2012 9:10 AM
        • 1. Re: Ajax Get Multiple Values Tabular II
          evrm
          Hi,

          >
          Can anyone assist me to remove those (null) values from being displayed based on the select statement when the Add Row button is clicked?
          >

          In the "Layout and Pagination" section of the "Report Attributes" you can change the field "Show Null Values as" which determines what will be shown when a column is null.

          regards,
          Erik-jan
          • 2. Re: Ajax Get Multiple Values Tabular II
            Charles A
            Hey Erik-jan,
            Thanks for reply, for the 'Layout and Pagination' tab 'Show Null Values as' equals (null) but once the (null) is removed no text boxes appear when 'Add Row' is clicked. Only the check box so the Delete can take place appears. The tabular form acts like only the static data can be selected and values based on field 'Book' are passed to the other fields. Any other ideas or would you like to view the sample on apex.oracle.com?

            Edited by: Charles A on Nov 26, 2012 7:54 AM
            • 3. Re: Ajax Get Multiple Values Tabular II
              evrm
              Hi Charles,

              I'm afraid that it's not possible because you use apex_item.text and as display type "standard report column".
              When your page is rendered Apex makes an initial row apex.widget.tabular.gTabFormInitRow in javascript which is used by the Add Row button function.
              Because of the standard report column no "real" html input will be rendered in the initial row.
              The execution of the SQL query with apex_item.text will generate the html.

              You could try to make it a real Tabular Form and still use the same functionality.
              The only adjustment would be the on demand application process naming of the items which is now 32, 33, 34 and 35.
              I could take a look at your apex.oracle.com application to see how to implement this.

              regards,
              Erik-jan
              • 4. Re: Ajax Get Multiple Values Tabular II
                Charles A
                Hey Erik-jan,
                Yeah, I saw that function call within the Add Row button; did not seem like no way to access the Javascript to modify or view that call. Here is my login information below. This is a powerful example and it could help out with rapid transactions.

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

                Workspace:
                Username:
                Password:

                Application Name: Tabular Set Multi Items II

                Edited by: Charles A on Nov 27, 2012 8:27 AM
                • 5. Re: Ajax Get Multiple Values Tabular II
                  evrm
                  Hi Charles,

                  you could achieve this by using a collection and you own DML processes.

                  Create a before header process that creates a collection for the tabular form.
                  begin
                  wwv_flow_collection.create_or_truncate_collection(p_collection_name=>'INIT_TABULAR_FORM');
                  for i in 1..5
                  loop
                      wwv_flow_collection.add_member(
                      p_collection_name => 'INIT_TABULAR_FORM',
                      p_c001 => i*10,
                      p_c002 => null,
                      p_c003 => null,
                      p_c004 => null,
                      p_c005 => null);
                  end loop;
                  end;
                  Then construct a query like this to fill up the tabular form.
                  select '<img src="#IMAGE_PREFIX#list_gray.gif" '
                         || 'onclick="popUp2(''f?p=&APP_ID.:3:&SESSION.::::'
                         || 'P3_ROWNUM:'
                         || seq_id
                         || ''', 700, 700);" '
                         || 'style="cursor:pointer;">' link,
                  c001, 
                  c002, 
                  c003, 
                  c004, 
                  c005
                  from wwv_flow_collections
                  where collection_name = 'INIT_TABULAR_FORM'
                  Change the application process to use the right item number to pass back the values.
                  Inspect the generated html to see what numbers to use, should be f01 for the first column, f02 for the second etc.

                  Delete the standard DML processes and create your own to process the apex_application.g_fxx array's.

                  regards,
                  Erik-jan
                  • 6. Re: Ajax Get Multiple Values Tabular II
                    Charles A
                    Hey Erik-jan,
                    I copied the test application and created another. I created the Before Header Process and also modified the Tabular Form - Region Source with the select statement you listed. I am not sure on what exactly to change in the current Application Process.
                    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_item;
                    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="f32_' || :t_rownum || '">' || v_subject || '</item>');
                       HTP.prn ('<item id="f33_' || :t_rownum || '">' || v_price || '</item>');
                       HTP.prn ('<item id="f34_' || :t_rownum || '">' || v_author || '</item>');
                       HTP.prn ('<item id="f35_' || :t_rownum || '">' || v_qty || '</item>');
                       HTP.prn ('</body>');
                    END;
                    Possibly change
                       HTP.prn ('<item id="f32_' || :t_rownum || '">' || v_subject || '</item>');
                       HTP.prn ('<item id="f33_' || :t_rownum || '">' || v_price || '</item>');
                       HTP.prn ('<item id="f34_' || :t_rownum || '">' || v_author || '</item>');
                       HTP.prn ('<item id="f35_' || :t_rownum || '">' || v_qty || '</item>');
                    to
                       HTP.prn ('<item id="f01_' || :t_rownum || '">' || v_subject || '</item>');
                       HTP.prn ('<item id="f02_' || :t_rownum || '">' || v_price || '</item>');
                       HTP.prn ('<item id="f03_' || :t_rownum || '">' || v_author || '</item>');
                       HTP.prn ('<item id="f04_' || :t_rownum || '">' || v_qty || '</item>');
                    My knowledge for tabular forms is very limited, the MRU-Multi Row Update in previous versions paused development with tabular forms. When I run the Tabular Form the data appears like below
                     Link                                                                                                                C001  C002 C003 C004 C005
                    <img src="/i/list_gray.gif" onclick="popUp2('f?p=69351:3:8115457329619::::P3_ROWNUM:1', 700, 700);" style="cursor:pointer;">  10   (null) (null)  (null) (null)
                    .........................
                    I set the current Processes After Submit 'ApplyMRU and ApplyMRD' to never so I can create the DML Process. Should the data display as above and do you have an example thread of how the DML Process should be implemented that I can review? Thanks for assisting with this because my tabular knowledge is limited.

                    Edited by: Charles A on Nov 27, 2012 8:41 AM
                    • 7. Re: Ajax Get Multiple Values Tabular II
                      Charles A
                      My reply did'nt post, was blank; sorry for this extra post.

                      Edited by: Charles A on Nov 27, 2012 8:40 AM
                      • 8. Re: Ajax Get Multiple Values Tabular II
                        evrm
                        Hi Charles,

                        for the correct display of the fields you have to change the display type.
                        For the first column this should me "Standard report column" because this some hardcoded HTML. Although this is not best practice it will suffice for now but you should convert it to a real column link.
                        The other columns should have "Text Field" as display type.

                        You are correct about the change in the Application Process
                        >
                        HTP.prn ('<item id="f03_' || lpad(:t_rownum, 4, '0') || '">' || v_subject || '</item>');
                        HTP.prn ('<item id="f04_' || lpad(:t_rownum, 4, '0') || '">' || v_price || '</item>');
                        HTP.prn ('<item id="f05_' || lpad(:t_rownum, 4, '0') || '">' || v_author || '</item>');
                        HTP.prn ('<item id="f06_' || lpad(:t_rownum, 4, '0') || '">' || v_qty || '</item>');
                        >

                        Please read this chapter http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_app.htm#CHDDADIE about referencing the global arrays in a tabular form.
                        Your process could have something like this:
                        for i in 1..apex_application.g_f03.count
                        loop
                           insert into my_book_store(subject, price, author, qty)
                           values(apex_application.g_f03(i), apex_application.g_f04(i), apex_application.g_f05(i), apex_application.g_f06(i)); 
                        end loop;
                        regards,
                        Erik-jan
                        • 9. Re: Ajax Get Multiple Values Tabular II
                          Charles A
                          Hey Erik-jan,
                          Thanks for the follow up, I will test this out within my test application. Thanks for the link also, I will definitely read about the arrays within tabular forms.
                          • 10. Re: Ajax Get Multiple Values Tabular II
                            Tom Petrus
                            Hello Charles,
                            I came across this thread when you posted it and after looking at it for a bit, I thought I'd give it an own whirl :) After all, it seems you are trying to accomplish some sort of modal popup list to use in a tabular form.
                            I myself am not a big fan of manual tabular forms (or tabular forms at all, but wizard generated ones cause me the least headache), and I'd to avoid those. The example by Denes could be used as an exercise, but in this case I'd go about it in a slightly other way. I made a blog post about it, which outlines what I did. I use a wizard generated form, the modal page plugin and some javascript wizardry. You can download the documention in pdf format and the example application from the example application on apex.oracle.com.
                            Just providing some options!
                            • 11. Re: Ajax Get Multiple Values Tabular II
                              Charles A
                              Hey Tom,
                              Thanks for following this thread and definitely for providing me with some needed information. I second what you say about those tabular forms, I am trying my best to reduce the amount of button clicks for my end-users. And that tabular form example which passes multiple values when selecting a unique value is definitely something I am interested in implementing if possible. Can you send me the Username and Password for your sample application.

                              Edited by: Charles A on Nov 29, 2012 7:30 AM
                              • 12. Re: Ajax Get Multiple Values Tabular II
                                Tom Petrus
                                woopsies! I normally add an html block above the login page with them, but forgot about that. It's apex_demo/demo
                                • 13. Re: Ajax Get Multiple Values Tabular II
                                  Charles A
                                  Hi Tom,
                                  Thanks for the pdf with the great documentation, you out did yourself with this example. I was able to view (still viewing) the code and test out the application when imported into apex.oracle.com. Now heres the bad news, your example is exactly what I need but unfortunately I am using Application Express 4.0.2.00.07 and can not import into my workspace since your example and apex.oracle.com are newer versions. I am receiving error
                                  1 error has occurred
                                  NOT COMPATIBLE (Your export may contain calls not supported by your application version.)
                                  which will pause my implementation until my department purchases the newer version of APEX. But I definitely can test this out more and educate myself with your example on apex.oracle.com. Thanks again for taking time to assist with this thread. The newer version of APEX has more features like for a Button Action 'Defined by Dynamic Action' which version 4.0 does not have.
                                  • 14. Re: Ajax Get Multiple Values Tabular II
                                    Tom Petrus
                                    Charles,
                                    Unfortunate :-) However, implementing will be very very much the same. The dynamic action on the add row button is something you can easily work around by changing the javascript code in the URL.

                                    By default it will hold
                                    javascript:addRow();
                                    In my code i used
                                    $(this.affectedElements[0])
                                    to reference the tabular form region, just for ease of use. I'd give the tabular form region a static id, which you can then use in the selector instead

                                    Replace it with
                                    javascript:addRow(); $("#my_tabular_form_id").find("a.modalshow:last").button({icons:{secondary:'ui-icon-circle-arrow-n'}});
                                    That will do the exact same trick.

                                    I'm just not sure if the fmap mapping is generated in 4.0, so check that. Other then that i think you should be fine.
                                    1 2 Previous Next