This discussion is archived
1 2 Previous Next 29 Replies Latest reply: May 23, 2013 3:01 PM by Denes Kubicek RSS

how should I update a tabular form, based on collection

KarenH Newbie
Currently Being Moderated
I am in the process or redesigning an application which uses collections.

I have done the following, but am a bit uncertain how to proceed.

1. I have created a view landings_collection_view based on the collection SPECIES_COLLECTION:
CREATE OR REPLACE VIEW landings_collection_view (
   seq_id,
   landing_seq,
   species_itis,
   grade_code,
   market_code,
   unit_measure,
   disposition_code,
   gear_code,
   reported_quantity,
   price,
   dollars,
   area_code_flag,
   c014,
   additional_measure_flag,
   addnl_reported_quantity,
   addnl_unit_quantity,
   fins_flag,
   finsattached,
   finsnotattached,
   finsunknown,
   fins_code,
   area_fished,
   sub_area_fished,
   local_area_code,
   explanation,
   nature_of_sale,
   hms_area_code,
   sale_price,
   hms_flag )
AS
SELECT
seq_id,
c003 landing_seq,
c004 species_itis, 
c005 grade_code,
c006 market_code, 
c007 unit_measure,
c008 disposition_code,
c009 gear_code,
c010 reported_quantity,
c011 price,
c012 dollars,
c013 area_code_flag,
c014 ,
c017 additional_measure_flag,
c018 addnl_reported_quantity,
c019 addnl_unit_quantity,
c020 fins_flag,
c021 finsattached,
c022 finsnotattached,
c023 finsunknown,
c024 fins_code,
c025 area_fished, 
c026 sub_area_fished,
c027 local_area_code,
c028 explanation,
c029 nature_of_sale,
c040 hms_area_code,
c041 sale_price,
c050 hms_flag
from apex_collections
 where collection_name = 'SPECIES_COLLECTION'
/
The SPECIES_COLLECTION is populated based on a query.

I have an application page comprised of a tabular form region based on the above view, Landings_collection_view.

I would like to update the column DISPOSITION_CODE in the tabular form, but in doing so, other column values will change. I am wondering about an ON-CHANGE action...but not certain where to place the code. I have created a package called SAFIS_COLLECTIONS.update_disposition.
procedure update_disposition(v_seq in number,
                             v_disposition_code in varchar2)
is

begin
   apex_collection.update_member_attribute (p_collection_name=> 'SPECIES_COLLECTION',
                     p_seq => v_seq,
                     p_attr_number => 8,
                     p_attr_value  => v_disposition_code);
apex_debug.message('updated disposition');


end;
so, basically, when the disposition code on the tabular form changes...for any record, I want to update the underlying species collection. It needs to be done as the disposition changes, as other columns may then appear/not appear...a whole other can of worms. ;0

thanks.

Karen
  • 1. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    I think this is close to what you need:

    https://apex.oracle.com/pls/apex/f?p=31517:294

    The "tabular form" and the IR on the right side are both based on the same collection. I think you have the access to my workspace so you can have a look.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 2. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    Fabulous! thanks Denes. I have only implemented the update dynamic action, but it works perfectly. Rather than use the HTML Expression:
    <input id="f01_#ROWNUM#" class="#SEQ_ID#" type="text" value="#DISPOSITION_CODE#" maxlength="3" size="45" name="f01">
    is there a way to make this an LOV. I tried type='lov", but that did not do it. I also tried:
    {code}
    <input id="f01_#ROWNUM#" class="#SEQ_ID#" type="text" list="DISPOSITIONS" value="#DISPOSITION_CODE#" maxlength="3" size="45" name="f01">
    {code}, but no luck.


    I am now in the process of creating a dynamic action for each column that can be changed. Each of these will call a seperate procedure (safis_collection.update_disposition, update_price, etc).

    I have also added the add to collection feature, and it works with the exception that my dynamic action does not refresh the regions (the landings_collection_view, and the IR report). any thoughts?

    thanks for all your help.
    Karen

    Edited by: KarenH on May 17, 2013 3:33 PM
  • 3. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    my example should work with those dynamic actions for any number of columns so you don't need to create one dynamic action per column. Just use the coma separated jQuery selctor in the first dynamic action.

    As far as the select list is concerned I am not sure how to solve that. Maybe in the next few days when I have time for that.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 4. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    Thanks Denes,

    for the suggestion of having one dynamic action, and referencing the column.... I am having trouble setting that up:
    input[name="f08"],[name="f11"]
    do I have that correctly.



    As for your much appreciated help on how to make dispositions appear as an lov, prior to moving to a collection based on a view...and then packages to update the collections, I referenced the collection directly in a tabluar form, and then have quite alot of onchange triggers which reference javascript..and then reference apex processes, Debugging has been a nightmare, and that is why we are looking for something cleaner and easier for the next person to debug.

    If it is any help, our query before (with only the column for disposition) was:
    select 
    apex_item.select_list_from_LOV(6,c008,'DISPOSITIONS','onchange="getAllDisposition('||seq_id||')"','YES','0','  -- Select Favorite --  ','f06_'||seq_id,'') Disposition
    from apex_collections where collection_name = 'SPECIES_COLLECTION';
    getalldispositions selected from a users 'favorite dispositions' table previously set up...and also contained the value SELECT ALL DISPOSITIONS which would pull all dispositions into the lov.
    function getAllDisposition(row)
    {
       
       var pThis = $x('f06_'+row);
    //if select all dispositions has been selected   
       if(pThis.value == 999)
       {
          var l_Return = null;
          var l_Select = pThis;
          var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=getAllDisposition',0);
          get.add('TEMPORARY_ITEM',pThis.value);
          gReturn = get.get('XML');
          if(gReturn && l_Select)
          {
             var l_Count = gReturn.getElementsByTagName("option").length;
             l_Select.length = 0;
             for(var i=0;i<l_Count;i++)
             {
                var l_Opt_Xml = gReturn.getElementsByTagName("option");
    appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'), l_Opt_Xml.firstChild.nodeValue);
    }
    pThis.value = 0;
    }
    }
    if (pThis.value != 0)
    {
    var temp2 = 0;
    if ((row == rowCount) && !($v("P110_DEALER_RPT_ID") > 0)){
    var found = 0;
    for(i = 1;i<rowCount;i++)
    {
    temp2 = i;
    var nextRow = $x('f06_'+i);
    //change all rows that do not equal the first row
    if((nextRow.value != pThis.value) && (nextRow.value == "0"))
    {

    // alert(nextRow.value);
    //checks to make sure that the new value is in the select
    //list
    var children = nextRow.childNodes;
    for (index = 0; index<children.length;index++)
    {
    if(children[index].value == pThis.value)
    {
    found = 1;
    }
    }
    //if the new value is not in select list, add it.
    if(found != 1)
    {
    appendToSelect(nextRow, pThis.value, pThis.options[pThis.selectedIndex].text);
    }
    found = 0;
    //save the new value to the collection
    var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setDisposition',0);
    get.add('SETVALUE',pThis.value);
    get.add('SEQUENCEID',temp2);
    if (row == rowCount)
    {
    get.add('G_FIRST_DISPOSITION', pThis.value);
    }
         gReturn = get.get();
    // alert(gReturn);
    get = null;
    //set the value on screen to the 1st disposition
    nextRow.value = pThis.value;
    }

    }
    }
    //save the disposition so that if a non-oracle error occurs value is saved
    var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setDisposition',0);
    get.add('SETVALUE',pThis.value);
    get.add('SEQUENCEID',row);
    if (row == rowCount)
    {
    get.add('G_FIRST_DISPOSITION', pThis.value);
    }
    gReturn = get.get();
    }
    }
    ugh.  even copying here makes me cringe.  I inherited this code, and do not know javascript myself...so that is part of the issue.
    
    the process GETALLDisposition is currently:
    declare
    l_counter number;
    l_o_name varchar2(2000);
    begin
    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('<select>');
    for rec in (select disposition_desc, pd.disposition_code
    from partner_dispositions pd, dispositions d
    where partner_id = :G_ISSUING_AGENCY and pd.disposition_code = d.disposition_code and pd.system_id = 'EDR' union select '- Select Disposition -','0' from dual)
    loop
    htp.prn('<option value="' || rec.disposition_code || '">' || rec.disposition_desc || '</option>');
    end loop;
    htp.prn('</select>');
    end;
    and the process SETDisposition is: (note this is now, in a newly created package..safis.collections.update_disposition because it is used in several areas)
    begin
    apex_collection.update_member_attribute (p_collection_name=> 'SPECIES_COLLECTION',
    p_seq => :SEQUENCEID,
    p_attr_number => 8,
    p_attr_value => :SETVALUE);
    htp.p('at end of saving disposition');
    end;
    if you can help make sense of this, you are a hero!  :)
    
    thanks
    
    Edited by: KarenH on May 17, 2013 6:01 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 5. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I think it is:
    input[name="f08"],input[name="f11"]
    for all text elements.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 6. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    now the form works with a select list as well. Adding a new column should require only adding the new jQuery Selector. I changed the code a bit:

    On Load Process for creating Collection

    Tabular Form SQL

    Dynamic Action 1 - the setting of the P294_SEQ_ID value

    Dynamic Action 1 - the PL/SQL Block

    Dynamic Action 2 - the PL/SQL Block

    I didn't touch the columns and removed the HTML Expression changes.

    Regards,

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 7. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    yes, that is the syntax. I had a period instead of a comma.
  • 8. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    on your example, are you saying that I should remove the HTML expressions from ALL columns that could possibly be changed? or just the one that is an LOV (disposition_code), and then would I make that column attribuite-display as LOV as opposed to TEXT?
    <input id="f08_#ROWNUM#" class="#SEQ_ID#" type="text"   value="#DISPOSITION_CODE#" maxlength="3" size="45" name="f08" attr="8">
    I see that the dynamic action 1 has a different input line that includes 'select[name='f03'].' Is this how the LOV is now indicated? and is the item DEPT defined as an LOV?
    4. Dynamic Action for Saving Changes in the collection fires on change of the jQuery Selector: 
    input[name="f02"],select[name="f03"]
    so, my jQuery Selector now would have:
    input[name="f09"],[name="f10"],[name="f11"],[name="f12"],select[name="f08"]
    reflecting that f08 is my LOV?

    thanks again!'
    Karen

    Edited by: KarenH on May 18, 2013 12:22 PM
  • 9. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    hmm. so, still don't have it correct, and must be missing something.

    I removed the HTML expression from the column DISPOSITION_CODE and modified the column to be an named LOV.

    I modifed the jQuery selector to include select[name='f08']....but know that can't be right because how is f08 now defined?

    thanks Denes!
  • 10. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    it should be
    select[name="f08"],input[name="f09"],input[name="f10"],input[name="f11"],input[name="f12"]
    No HTML expression changes needed.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 11. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    Thanks. I can't quite seem to get it.

    here is what I have for DISPOSITION_CODE

    DISPLAY AS: Text Field (I had originally had this as named LOV, but changed it back because nothing was happening)
    HTML Expression = <input id="f08_#ROWNUM#" class="#SEQ_ID#" type="text" value="#DISPOSITION_CODE#" maxlength="16" size="16" name="f08" attr="8">



    for Dynamic Action
    event=CHANGE
    selection type=jQUERY Selector
    select[name="f08"],input[name="f09"],input[name="f10"],input[name="f11"],input[name="f12"]

    when columns 9,10, 11, 12 change, the collection is updated, however nothing happens for column 8.


    as a note, I noticed that your TABULAR FORM SELECT is on the collection:
    SELECT NULL del_link, seq_id, seq_id seq_id_display, c001 empno, c002 ename,
           c003 dept
      FROM apex_collections
     WHERE collection_name = 'EMP_COLL'
    whereas mine is on the VIEW of a collection:
    select 
    "ROWID",
    null delete_link,
    "SEQ_ID",
    "SEQ_ID" seq_id_display,
    "LANDING_SEQ",
    "SPECIES_ITIS",
    "GRADE_CODE",
    "MARKET_CODE",
    "UNIT_MEASURE",
    "DISPOSITION_CODE",
    "GEAR_CODE",
    "REPORTED_QUANTITY",
    "PRICE",
    "DOLLARS",
    "AREA_CODE_FLAG",
    "C014",
    "ADDITIONAL_MEASURE_FLAG",
    "ADDNL_REPORTED_QUANTITY",
    "ADDNL_UNIT_QUANTITY",
    "FINS_FLAG",
    "FINSATTACHED",
    "FINSNOTATTACHED",
    "FINSUNKNOWN",
    "FINS_CODE",
    "AREA_FISHED",
    "SUB_AREA_FISHED",
    "LOCAL_AREA_CODE",
    "EXPLANATION",
    "NATURE_OF_SALE",
    "HMS_AREA_CODE",
    "SALE_PRICE",
    "HMS_FLAG"
    from "#OWNER#"."LANDINGS_COLLECTION_VIEW"
    thanks again. Karen

    ps. is it possible to see the code behind your example. that might help.

    Edited by: KarenH on May 20, 2013 11:33 AM

    Edited by: KarenH on May 20, 2013 12:24 PM
  • 12. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Try chaning the f08 to a select list again. In the HTML expression it says input and the dynamic action jquery selector is set to select....


    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 13. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    Hi Denes,

    Currently, the field DISPOSITION_CODE is set as:

    coiumn attribute - display as SELECT LIST (named LOV)
    HTML Expression: <input id="f08_#ROWNUM#" class="#SEQ_ID#" type="text" value="#DISPOSITION_CODE#" maxlength="16" size="16" name="f08" attr="8">


    the Dynamic Action on event= CHANGE
    selecttion type: jQuery Selector
    jQuery Selector = select[name="f08"],input[name="f09"],input[name="f10"],input[name="f11"],input[name="f12"]

    When any column other than f08 iis changed, the underlying collection is properly updated; only f08 is not changing.

    I have been searching the jquery sites, but unfortunately, am not seeing many examples of select[name=, so really feel a bit stuck.   Your help is really apprecieated.  thanks again.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 14. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    read my answers and remove all HTML Expressions from all columns please. I went away from that approach and in the code in my demo application there is no word about HTML Expression changes any more.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
1 2 Previous Next

Legend

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