This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 30, 2012 12:31 PM by Charles A RSS

Ajax Get Multiple Values Tabular II

Charles A Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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