This discussion is archived
7 Replies Latest reply: May 6, 2013 10:40 AM by 1007368 RSS

Popup LOV **in tabular form** filtered by a value on the same row?

560577 Pro
Currently Being Moderated
All,

I'm anticipating a requested change to one of my tabular forms that may head me down the path of trying to filter a popup LOV's SQL by a value. Effectively this is a cascading LOV which Apex does support (I am on version 4.x) but only for page items. For tabular form items, I can't see how to do this.

The background is that I have a tabular form with part numbers and a vendor ID (currently as a select-list, and on change of the part number I currently successfully repopulate the select-list of vendor IDs to only vendors for that part). I anticipate the user will want to change this to be a popup LOV so they can either still choose from a list of values or manually type into the field if they already know the value.

While everything is working fine as a select-list, I am stumped on how one would do this with a popup LOV.

Challenges that I foresee:

1. It seems that popup LOVs in general, if dependent on some value as a cascading LOV, filter their values only when the popup LOV is executed at runtime. Therefore, unlike the driving field (part number in my case) triggering, via onchange, code that repaints the LOV values in a select-list, a popup LOV instead would be unaffected by the onchange on part number and instead when the LOV is invoked, this is when the part number field should be evaluated to filter the values in the LOV SQL.

2. Focus is not required in a popup LOV field...by that I mean that you can click on a popup LOV button to invoke it without your cursor being in the corresponding field. You also don't even need to be on the same row. Therefore, I am not sure how I would even know what row I'm on in the tabular form to reliably somehow pass the part number from the correct row to the popup's SQL somehow.

3. The popup LOV button itself has no internal HTML ID associated with it, so I can't even really trigger something from that being pressed to tell what row I'm on in order to then find the part number on the row I'm on.

Anyway, has anyone managed to successfully define a cascading LOV in v4 on a tabular form that is a popup LOV? Ideas or examples?
  • 1. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    560577 Pro
    Currently Being Moderated
    Anybody? Nobody's had to do this?

    This has come back to haunt me. For a while I got away with it being a select-list which I was able to make work.

    But the user has come back with a changed requirement that they must be able to type a value into the field that isn't in the LOV. In other words, the LOV values are "suggestions" for that field on that row (dependent on the value of another field) but they want to be able to type in anything to the field.

    So this suggests to me that we need a popup LOV (has a filtered list of values dependent on another item on the row but the user can still type into the field).

    I found this:
    http://dbswh.webhop.net/htmldb/f?p=BLOG:READ:0::::ARTICLE:2914000346137910

    Tried it and it doesn't quite work (not for me anyway).

    When the page renders, all of the child popup LOVs seem to hang onto the list of values driven by the parent value of the last row on the page.

    If you click on the child LOV of several rows and then refresh the page, the parent of the last row you clicked on is now th driver for all the child popup LOVs.

    Help?

    I am in Apex 4.1.1.00.23 if it matters any. Also, my driving "parent" value on each row itself is a popup key LOV but just to eliminate any ambiguity about that, I changed it to a text field (so as to not confuse the display value vs. return value) but the problem persists.

    I'm sure the answer lies here somewhere (this is code from that site above) but this is beyond my comprehension.
    (function ($) {; /*** Cascading popup list ***/
        $.fn.htmldbCascadePopup = function (parent, tempItem, options) {
            options = $.extend({
                loadingTxt: "Loading ...",
                loadingCss: {
                    "width": "80px",
                    "float": "left"
                }
            }, options);
            return this.each(function (i) {
                var self = $(this);
                var anchor = self.next("a");
                var lParent = $(parent).eq(i);
                var lHref = anchor.attr("href").substr(11);
                var lPopupFn = new Function(lHref); /* Clear child when parent changes */
                lParent.change(function () {
                    self.val("");
                });
                anchor.click(function (e) {
                    e.preventDefault();
                    var lParentVal = $(lParent).val();
                    if (!lParentVal) {
                        lParentVal = "";
                    };
                    $.ajax({
                        type: "POST",
                        async: false,
                        url: "wwv_flow.show",
                        data: {
                            p_flow_id: "&APP_ID.",
                            p_flow_step_id: "&APP_PAGE_ID.",
                            p_instance: "&APP_SESSION.",
                            p_request: "APPLICATION_PROCESS=DUMMY",
                            p_arg_names: tempItem,
                            p_arg_values: lParentVal
                        },
                        beforeSend: function () {
                            self.hide().after($("<div/>", {
                                "html": options.loadingTxt,
                                "css": options.loadingCss,
                                "class": "ui-autocomplete-loading"
                            }).width(self.outerWidth()).height(self.outerHeight()));
                        },
                        complete: function () {
                            lPopupFn();
                            self.show().next("div.ui-autocomplete-loading").remove();
                        }
                    });
                });
            });
        };
    })(apex.jQuery);
  • 2. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    user477654 Newbie
    Currently Being Moderated
    Hi

    Did you manage to find a working solution to this. I have exactly the same requirement !!!
  • 3. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    560577 Pro
    Currently Being Moderated
    Nope, not yet :-(

    Did you try the above solution too like I did? If you do and it works for you, then it maybe is some copy/paste mistake on my part as to why it didn't work for me? Might help if someone can confirm if I missed something in following the instructions at the URL listed above.

    It's so disappointing that a functional request (cascading LOV in a tabular form) is not generally unreasonable but it's jumping through hoops to make it happen.

    I'd revert to a select-list that is a cascading LOV because I had that working OK except for the requirement that the user be able to type into the list a value that is not in the list. Grrrr....
  • 4. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    560577 Pro
    Currently Being Moderated
    I'm not sure what I did wrong the first time but I went through the steps again and it worked this time.

    The only thing I could guess is that in copying/pasting some of that code I ran it through a online JS beautifier (since it comes out with incorrect line breaks when I paste it and looks like a big ugly jumble in the HTML of the page) and maybe the beautifier broke it? IDK...but it's working now.

    Tip...if your standard for your tabular form is to have your ADD button (for adding rows) at the top and bottom of the page, the bottom button will not work. To get around this, just copy/paste your button as "ADD2" and then copy/paste your dynamic action as well and attach it to the new button. A little redundant, but it works.

    Our standard is to have the cancel, delete, submit, and add buttons all top and bottom, so I copied all four buttons accordingly, made the originals all display at the top, and made the copies display at the bottom. If you do this, you still want your submit button to submit as "SUBMIT", not as "SUBMIT2" so you want to keep the same name on that button (Apex will let you name two buttons the same).

    Phew!
  • 5. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    560577 Pro
    Currently Being Moderated
    As an aside, in using the above workaround, I am a fan of putting JS code on page 0 as a no-template HTML region that displays before header and conditionally displaying it according to the current page ID for the pages where it's needed. That said, the CSS style code at the above link and some of the JS is page-independent (generic) and I moved it to a page 0 region instead. That way, if I have later another page in my app, I can just create the DAs only w/o pasting the generic code redundantly on a separate page and I just add that page# to the display condition of the page 0 region.

    So my page 0 region looks like this:
    <style type="text/css">
    .ui-autocomplete-loading{background: url("#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/images/ui-anim_basic_16x16.gif") no-repeat scroll right center transparent;}
    </style>
    <script>
    (function($){;  /*** Cascading popup list ***/ $.fn.htmldbCascadePopup=function(parent,tempItem,options){      options=$.extend({    loadingTxt    : "Loading ...",    loadingCss    : {"width":"80px","float":"left"}   },options);       return this.each(function(i){        var self=$(this);    var anchor=self.next("a");    var lParent=$(parent).eq(i);    var lHref=anchor.attr("href").substr(11);    var lPopupFn=new Function(lHref);      /* Clear child when parent changes */   lParent.change(function(){     self.val("");    });         anchor.click(function(e){       e.preventDefault();           var lParentVal=$(lParent).val();     if(!lParentVal){lParentVal="";};          $.ajax({      type:"POST",      async:false,      url:"wwv_flow.show",      data:{       p_flow_id:"&APP_ID.",       p_flow_step_id:"&APP_PAGE_ID.",       p_instance:"&APP_SESSION.",       p_request:"APPLICATION_PROCESS=DUMMY",       p_arg_names:tempItem,       p_arg_values:lParentVal      },beforeSend:function(){       self        .hide()        .after($("<div/>",{"html":options.loadingTxt,"css":options.loadingCss,"class":"ui-autocomplete-loading"})         .width(self.outerWidth())         .height(self.outerHeight())        );      },complete:function(){       lPopupFn();       self        .show()        .next("div.ui-autocomplete-loading")        .remove();      }     });          });   });  }; })(apex.jQuery);
    </script>
  • 6. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    560577 Pro
    Currently Being Moderated
    More Page 0 tips for this.

    I was able to create this JS on my page 0 region:
    <script>
    function setParentChildPopupLov(pParentCol,pChildCol,pNewRow)
    {
      if(pNewRow==true)
      {
        $("[name=" + pChildCol + "]:last").htmldbCascadePopup( "[name=" + pParentCol + "]:last", "G_POPUP_PARENT_VALUE");
      }
      else
        $("[name=" + pChildCol + "]").htmldbCascadePopup( "[name=" + pParentCol + "]", "G_POPUP_PARENT_VALUE");
    }
    </script>
    Such that my JS functions on the actual page look like this...

    The one for existing rows on page load and region refresh:
    setParentChildPopupLov(findtabformcolbasename("PART_MASTER_ID"),
                           findtabformcolbasename("VENDOR_ID"),
                           false);
    The one for adding a new row:
    addRow();
    setParentChildPopupLov(findtabformcolbasename("PART_MASTER_ID"),
                           findtabformcolbasename("VENDOR_ID"),
                           true);
    BTW, findtabformcolbasename() is my own function that reads the fmap and returns f01, f02, etc. based on the column name you pass in. I hate having to hard-code those fnn numbers so I made my own dynamic function for that. If you don't have a function like that, you will need to pass "f01", "f02", etc. as hard-coded strings.
  • 7. Re: Popup LOV **in tabular form** filtered by a value on the same row?
    1007368 Newbie
    Currently Being Moderated
    Hello gti_matt,

    I tried following your example to implement pop up in tabular form filtered by an id field on the same row.. but my pop up is blank and returns no names, can you please advise or have any idea what could be the reason for it :$

    Many thanks :)

Legend

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