7 Replies Latest reply: Nov 27, 2012 12:49 PM by 560577 RSS

    Popup Key LOV in master-detail form (detail is tabular) issue w/validation

    560577
      Apex v4.0.2.00.07.

      I created a master-detail form with the wizard. (I do have a lot of additional Javascript and whatnot firing for other reasons but I am sure it is not affecting this column/field because I have not done any special validation or processing on this column.) Some of my detail columns have not-null validation on them that were created with the wizard when I initially created the form.

      The column in question that is giving me grief is a popup key LOV (one of the new v4 features for tabular forms and detail regions on master-detail).

      I enter in a master and then enter in a few rows of detail. Save the form, no problem (the point here is to have one or more rows of existing detail data).

      I then go back into the form and go to the detail region and add a new row. Fill out the row filliing in all mandatory fields, etc. Now don't save yet. Go back to a mandatory field on this new row and/or to a mandatory field on an existing row and clear the value (so that validation for required values will fail when you save). Save the form.

      Validation fires, telling you that you need to enter a value on whatever field that was missing one (this is OK).

      However on the new row (existing rows are OK with this), the one field that is popup key LOV now displays no value even though I had selected a value on that new row for that field. It seems for existing rows it is fetching the displayed value back into the displayed disabled field for the popup key LOV OK, but on new rows it doesn't repopulate that displayed value.

      Anyone run across this? Workaround/fix? Or Apex bug?
        • 1. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
          560577
          P.S....further clarification...the value is not displayed but it is there behind the scenes because if I fix the field that failed validation and don't bother re-selecting the value for the popup key LOV field, it saves the popup key LOV return value to the database just fine.
          • 2. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
            560577
            And just duplicated it with a new tabular form all wizard-created (so I know it's not the fault of my "complicated" real form).

            1. Create a simple tabular form (doesn't even have to be master-detail) on a table. Make sure you have it such that the form has at least one mandatory column (easy to fail validation).
            2. Change one column to be "popup key LOV".
            3. Enter a few rows correctly and save.
            4. Now that you have saved data, add a new row. Fill it out just like you did the others but leave your mandatory value on this row empty but DO make sure you pick something from your popup LOV. Now save.

            When the form repaints, your popup key LOV on the new row does not have the description populated (and should...this is the bug) but on the existing/saved rows it does have it populated.
            • 3. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
              560577
              I've tried this on v4.1 on tryapexnow.com and it seems it's still a bug.
              • 4. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
                560577
                Have gone through the SR process and so far bug 12940411 has been created for this and is under review.
                • 5. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
                  Dipak R
                  Application Express 4.0.2.00.07

                  I have above apex version.

                  and have the same issue when a validation is occurred.

                  I have populated value description from popup key lov.
                  Then submit it , then validation message came and populated data will lost.

                  If there is a solution then please let me know.
                  Or is there a another way to do it?

                  Dipak
                  • 6. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
                    560577
                    Just checked in v4.2 on apex.oracle.com and it's still a bug. Bug# 1294041 still doesn't look like it's been worked when I check apex.oracle.com.

                    I have a manual workaround for this though that I did. It's a LOT of work so if you can get away with not doing it and just training your end users that the value they chose is still there and one day Apex will fix this, that might be best. But if you do need to do a workaround, this is what I did.

                    IMPORTANT NOTE: I highly recommend that in nearly every step below where you add a page item, a DA, a region, etc. for this workaround that you use the "comments" section of that object and put something like this:
                    This is part of a workaround for Oracle bug# 12940411. If that bug is ever fixed and installed here and works, this can be deleted.
                    This is because whenever Oracle fixes this bug, you can search your app using 12940411 and easily find all this code to yank it out. It's also very kind for your next developer to know why this code is in your app.

                    Of the steps and code below, since I have this requirement in many pages in my app, I will choose my app's page 341 as my example but there are some chunks of code that I use on page 0 to re-use in other pages across my app that need the same workaround. I recommend you use page 0 also for the same reason. Assume all "page" references below are your local page unless I say "page 0" specifically.

                    Here we go...

                    1. Create a hidden page item on your page which will store the name of an Apex collection that you will use. Give the page item a name like "P341_MAINT_ITEM_ID_COLL_NAME" (where in my case "MAINT_ITEM_ID" is the column name data source of the popup key LOV in question). Set to static text, source used = "always, replacing existing value in session state". Hard code the static text value to the display column name tied to your popup key LOV. So in my case since the data column was MAINT_ITEM_ID, put "MAINT_ITEM_ID_DISPLAY" as the value for this item. The only reason we're creating this page item is that we're referring to it in multiple places and I like to hard-code only once :-) So therefore your page item should be something like "P<page_number_here>_<column_name_here>_COLL_NAME".

                    2. Create a DA that fires on page load (e.g.: PopulateMissingLOVDisplayValues)
                    Event = page load
                    Condition = no condition
                    If your tabular form is the detail region of a master-detail page, then further down, set the condition (the one for rendering the DA at all) to the same condition that you have for your detail region showing (so it renders the DA code in harmony with rendering your detail region). If it's a tabular form that's not a master-detail, don't do anything there for condition.

                    3. Make the DA above fire javascript code. In this example, I am calling this function which passes two things:
                    $v('P341_MAINT_ITEM_ID_COLL_NAME') = refers to the hidden item set up in step #1
                    'TEMPLATE_DTL_ID' = the string name of the column that is the PK column of your tabular form or detail region's records. This is used to detect new vs. saved rows.
                    populateMissingLOVDisplayValues($v('P341_MAINT_ITEM_ID_COLL_NAME'),'TEMPLATE_DTL_ID');
                    4. On page 0 (because you probably should make this code reusable for other pages in your app), create a HTML region with no template, display before header, give it a meaningful title (e.g.: Tabular Form PopupKeyLOV Workaround) with this region source:
                    <script type="text/javascript">
                    function populateMissingLOVDisplayValues(pCollection,pPkColName)
                    {
                      if (isThingEmpty(pCollection) || isThingEmpty(pPkColName))
                        return;
                    
                      var vPkObjsArr=findtabformcolumn(pPkColName);
                      var vObjsArr=findtabformcolumn(pCollection);
                      var vSavedRows=0;
                      for (var i=0;i<vPkObjsArr.length;i++)
                      {
                        if (!$v(vPkObjsArr)=="")//saved row
                    vSavedRows++;
                    }
                    var vRow=0;
                    for (var i=0;i<vObjsArr.length;i++)
                    {
                    vRow=getRow(vObjsArr[i]);
                    if (($v(vPkObjsArr[i])=="") && (vRow>0)) //Unsaved row
                    $s(vObjsArr[i],getLOVCollectionAtRow(pCollection,vRow-vSavedRows));
                    }
                    }
                    function getLOVCollectionAtRow(pCollection,pRow)
                    {
                    if (isThingEmpty(pCollection) || isThingEmpty(pRow) ||
                    (typeof gTabFormReportID==="undefined"))
                    return("");

                    var get;
                    get=new htmldb_Get(null,
                    $v('pFlowId'),
                    'APPLICATION_PROCESS=GET_TABFORM_KEY_LOV_COLLECTION_VAL',
                    0);
                    get.addParam('x01',pCollection);
                    get.addParam('x02',pRow);
                    var retval=get.get();
                    get=null;
                    return(retval);
                    }
                    function populateLOVDisplayCollection(pCollection,pPkColName)
                    {
                    if (isThingEmpty(pCollection) || isThingEmpty(pPkColName) ||
                    (typeof gTabFormReportID==="undefined"))
                    return;

                    var vPkObjsArr=findtabformcolumn(pPkColName);
                    var vObjsArr=findtabformcolumn(pCollection);
                    var get;
                    for (var i=0;i<vObjsArr.length;i++)
                    {
                    if(($v(vPkObjsArr[i])=="")&&(getRow(vObjsArr[i])>0))//No PK, unsaved row
                    {
                    get=new htmldb_Get(null,
                    $v('pFlowId'),
                    'APPLICATION_PROCESS=POPULATE_TABFORM_KEY_LOV_COLLECTION',
                    0);
                    get.addParam('x01',pCollection);
                    get.addParam('x02',$v(vObjsArr[i]));
                    get.get();
                    get=null;
                    }
                    }
                    }
                    </script>

                    Optionally you can put a PL/SQL condition on this region such that it only renders when your app page id is one that has a tabular form or master-detail and doesn't render when you are not on a tabular form page (e.g.: v('APP_PAGE_ID') in('341','351','362','331'))

                    5. Add another page 0 region, HTML, no template, display before header, and put this code. This is supporting code that I wrote that enables you to deal with tabular forms and their columns by name. This code is handy not only for this workaround but for lots of tabular form things. Optionally add a condition to only render this region for your tabular form page.
                    <script type="text/javascript">
                    function findtabformcolumnnbr(pColName)
                    { //Pass in a tabular form column name as a string and
                      //get back the column number that it is as an integer.  For example, if
                      //you pass in "ITEM_NBR" and it's normally found in "f02" items, you will
                      //get back 2.
                      if(!pColName)
                        return(null);
                    
                      return(parseInt(findtabformcolbasename(pColName).substr(1),10));
                    }
                    function getRow(pObj)
                    { //Pass in an object reference or string ID to a tabular form cell and get
                      //back an integer derived from the object ID.
                      var vRow;
                    
                      if(typeof(pObj)=='string')
                        vRow=pObj.substr(pObj.indexOf("_")+1);  
                      else
                        vRow=pObj.id.substr(pObj.id.indexOf("_")+1);
                    
                      if (isNaN(vRow))
                        return (null);
                    
                      return (parseInt(vRow,10));
                    }
                    function formatRow(pRow)
                    { //Pass in an integer and it'll be returned as the tabular form cell ID suffix
                      //(e.g.: pass in 1 and get back string "_0001").
                      //Used in building ID references to tabular form cells.
                      if((isThingEmpty(pRow)) || (isNaN(pRow)))
                        return(null);
                    
                      return("_"+lpad(pRow,4,"0"));
                    }
                    function formatFmapId(pObj)
                    { //Replaces the ID in the fmap object to a new string to match base column
                      if (isThingEmpty(pObj))
                        return("");
                      return(pObj.id.replace("fmap_0","f"));
                    }
                    function findtabformcolbasename(pColName)
                    { //Pass in a column name string (e.g.: "PART_NO") from your tabular form
                      //as determined by your SQL for the updateable report and get back
                      //the "name" attribute (e.g.: "f01", "f02", etc.)
                      var vRetVal="";
                      if (isThingEmpty(pColName))
                        return vRetVal;
                    
                      var vMap=document.getElementsByName("fmap");
                    
                      for (var i=0;i<vMap.length;i++)
                      {
                        if ($v(vMap)==pColName.toUpperCase())
                    {
                    vRetVal=formatFmapId(vMap[i]);
                    break;
                    }
                    }
                    return vRetVal;
                    }
                    function findtabformcolumn(pTargetColName)
                    { //Pass in a column name string (e.g.: "PART_NO") from your tabular form
                    //as determined by your SQL for the updateable report and get back
                    //an array of object references to all cells in the column.
                    if(isThingEmpty(pTargetColName))
                    return(null);

                    var vFldBase=findtabformcolbasename(pTargetColName);
                    if(isThingEmpty(vFldBase))
                    return(null);

                    return(document.getElementsByName(vFldBase));
                    }
                    function findtabformcell(pTargetColName,pRow)
                    { //Pass in a column name string (e.g.: "PART_NO") from your tabular form
                    //as determined by your SQL for the updateable report and pass in an integer
                    //for what row you want and get back a single object reference to the cell
                    //for that column-row combination.
                    //HINT: usually you'll call this for a target field on the same row as
                    //a current field so for pRow you may want to pass getRow(this) in place of a
                    //hard-coded integer.
                    if((isThingEmpty(pTargetColName)) || (isThingEmpty(pRow)))
                    return(null);
                    return ($x(findtabformcolbasename(pTargetColName)+formatRow(pRow)));
                    }
                    function settabformcell(pTargetColName,pRow,pVal)
                    { //Pass in a column name string (e.g.: "PART_NO") from your tabular form
                    //as determined by your SQL for the updateable report and pass in an integer
                    //for what row you want and a value (empty string is OK to clear a field) to
                    //set the value at a cell for a column-row.
                    $s(findtabformcell(pTargetColName,pRow),pVal);
                    }
                    </script>


                    6. Add another page 0 region, this time with no condition. I use these functions all the time in general across many pages in my app.
                    <script type="text/javascript">
                    function fireBeforeSubmitDA(pEvent,pRequest)
                    {
                      //Reusable function to help correctly trigger before-submit DAs
                      //because if they don't have a condition, they may still fire when you don't want
                      //them to, such as when navigating to a child-level tab in a two-level tab application
                      //which does execute a submit but usually you don't really need to fire a DA for this.
                      //pEvent - the event that is triggering this
                      //pRequest - the request value
                      //
                      //Example:
                      //
                      //fireBeforeSubmitDA(this.browserEvent,this.data)
                      //
                      //Don't fire on page load.
                      //
                      if(pEvent=='load')
                        return(false);
                      //
                      //Skip if navigating to a tab since Apex likes to execute navigation to
                      //child tabs with an apex.submit call....grrrr...confusing!!!
                      //
                      if (simpleLookup('IS_NAVIGATING_TO_TAB',pRequest)=='1') 
                        return(false);
                      //
                      // If we get this far, OK to fire.
                      //
                      return(true);
                    }
                    function simpleLookup(pProcName,pVal)
                    { //Use for "simple" lookup of PL/SQL application process where you have a
                      //single value (pVal) passed in to look up and a single value coming back
                      //from a PL/SQL application process (pProcName). Used just to streamline code
                      //where it's a lot of lines to write every time when only the lookup value
                      // and process name are the only things that are different.
                      if(isThingEmpty(pProcName))
                        return("");
                      var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=' + pProcName,0);
                      if(pVal)
                        get.addParam('x01',pVal);
                      else
                        get.addParam('x01',"");
                    
                      ret = get.get();
                      get=null;
                      return(ret);
                    }
                    function lpad(pVal,pLength,pChar)
                    {
                      if(!pVal)
                        return("");
                    
                      if(!pLength)
                        return(pVal);
                    
                      if(!pChar)
                        return(pVal);
                    
                      var pNewVal=pVal.toString();
                      while(pNewVal.length<pLength)
                        pNewVal=pChar + pNewVal;
                    
                      return(pNewVal);
                    }
                    function isThingEmpty(pThing)
                    { //Pass in something (anything?) and returns TRUE if it's effectively empty
                      //(nonexistent,null,zero-length,empty string) else returns FALSE.
                      if (!pThing)
                        return(true);
                      if (pThing==null)
                        return(true);
                      if (pThing.length==0)
                        return(true);
                      if (pThing.value=="")
                        return(true);
                    
                      return(false);
                    }
                    </script>
                    7. Create a before-submit DA on your page (e.g.: "Before Submit CreateAndPopulateCollection").
                    condition = Javascript expression with this code (this is so this DA will not fire if the submit value was to delete data nor create new header (master-detail) and the user is not navigating to another child-level tab):
                    (!(this.data in{'DELETE':1,'APPLY_CHANGES_MRD':1,'MULTI_ROW_DELETE':1,'CREATE':1})) && fireBeforeSubmitDA(this.browserEvent,this.data)
                    Also add (if necessary) a condition to render this DA code only for the same condition as your tabular form region.

                    8. In the DA in step 7, create two true actions (and both with "Fire on lage load" turned off).

                    First, "execute pl/sql code" and ensure that you submit your hidden page item (whatever you did in step #1), so
                    Page items to submit = P341_MAINT_ITEM_ID_COLL_NAME
                    apex_collection.create_or_truncate_collection(:P341_MAINT_ITEM_ID_COLL_NAME);
                    COMMIT;
                    Then, "execute javascript code" (this is more or less the same as step #3):
                    populateLOVDisplayCollection($v('P341_MAINT_ITEM_ID_COLL_NAME'),'TEMPLATE_DTL_ID');
                    9. Create a PL/SQL page process (e.g.: DeleteLovDisplayValuesCollection) that is "on load - after footer". Set its condition to "PL/SQL expression" and condition code:
                    apex_collection.collection_exists(:P341_MAINT_ITEM_ID_COLL_NAME)
                    apex_collection.delete_collection(:P341_MAINT_ITEM_ID_COLL_NAME);
                    COMMIT;
                    10. Create another PL/SQL page process (e.g.: DeleteLovDisplayValuesCollection) that is effectively similar to the above, but fires "on submit - after computations and validations". As above, set its condition to "PL/SQL expression" and condition code:
                    apex_collection.collection_exists(:P341_MAINT_ITEM_ID_COLL_NAME)
                    apex_collection.delete_collection(:P341_MAINT_ITEM_ID_COLL_NAME);
                    11. Create an application-level on demand process with name "GET_TABFORM_KEY_LOV_COLLECTION_VAL", PL/SQL, no condition.
                    DECLARE
                      v_collection_name apex_collections.collection_name%TYPE
                                                                          := apex_application.g_x01;
                      v_row             apex_collections.seq_id%TYPE      := apex_application.g_x02;
                      v_retval          apex_collections.C001%TYPE;
                    BEGIN
                      SELECT C001
                      INTO v_retval
                      FROM apex_collections
                      WHERE collection_name = v_collection_name
                      AND   seq_id = v_row;
                      htp.prn(v_retval);
                    EXCEPTION
                      WHEN OTHERS THEN
                        htp.prn(v_collection_name || ' ' || to_char(v_row) || ' ' || sqlerrm);
                    END;
                    12. Create another on-demand application process, PL/SQL, no condition, called "POPULATE_TABFORM_KEY_LOV_COLLECTION"
                    DECLARE
                      v_collection_name  apex_collections.collection_name%TYPE
                                                                          := apex_application.g_x01;
                      v_collection_value apex_collections.C001%TYPE
                                                                          := apex_application.g_x02;
                    BEGIN
                      apex_collection.add_member(v_collection_name,v_collection_value);
                      commit;
                    END;
                    13. Create another on-demand application process, PL/SQL, no condition, called "IS_NAVIGATING_TO_TAB"
                    DECLARE
                      v_count NUMBER := 0;
                    BEGIN
                      select count(*)
                      into v_count
                      from apex_application_tabs
                      where application_id = v('APP_ID')
                      and tab_name = apex_application.g_x01;
                      --
                      IF v_count = 0 THEN -- not navigating to a tab
                        htp.prn(sys.diutil.bool_to_int(false));
                      END IF;
                      htp.prn(sys.diutil.bool_to_int(true));
                    END;
                    OK I hope all that works for you and that I didn't miss anything...it's a lot to try and find and copy/paste here.

                    Edited by: gti_matt on Nov 27, 2012 11:20 AM

                    Edited by: gti_matt on Nov 27, 2012 12:59 PM
                    • 7. Re: Popup Key LOV in master-detail form (detail is tabular) issue w/validation
                      560577
                      Now that I posted up that workaround, I will explain the strategy.

                      The bottom line is that when you submit your page and validations fire (and at least one fails you), your display values for your popup key LOVs are lost due to some Apex bug.

                      So we need to grab the display values of the new/unsaved rows somewhere to hang onto them (an apex collection is what I chose). We do this during before-submit of the page (but I chose to do it only for certain submit actions (not create or delete).

                      If validations pass successfully, we have an after-submit process that just truncates the collection of display values because we don't need them.

                      If validations fail, the page repaints and repopulates the popup key LOV display values of the new/unsaved rows from the collection via the Page Load DA we defined and then deletes the collection (because after we repopulate what we need to, we can delete the collection).

                      All of this requires AJAX, etc. to pass values to the Apex collection and to get them again. And since tabular forms have rather cryptic HTML IDs, I also choose to add another layer of JS code just to work with tabular form rows and columns by column name rather than position (I hate using f01, f02, etc. because they are unreliable).

                      All of the above will add some overhead to your page, so that's why I recommended tagging as much as you can in the comments referring to the bug number because whenever Apex fixes this, you should pull all this code out and you want to easily be able to find it.