This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Dec 31, 2009 2:11 PM by 34946 Go to original post RSS
  • 15. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi

    You realise that in your tab_casc_sel_list_area process you have:

    HTP.prn ('><select>');

    An extra > at the beginning - not sure what affect that may have on the rest of the code?

    Andy
  • 16. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    yes, I see that. When I take it out though, the query for the LOV does not work....I had copied this from Denes Kubicek's example, and admittedly, I am not quite clear on the process...but javascript is very new to me (as is apex). sigh.
  • 17. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    Umm... strange that it stops the code from working. Oh well.

    I'm having great "fun" with my tests.

    So far, I have a single record form and a standard (ie, wizard-created but tweaked) tabular form working ok with cascading LOVs:

    http://htmldb.oracle.com/pls/otn/f?p=20297:1

    Two questions:

    1 - What's your reason for using PL/SQL to generate the form?
    2 - How static is the data underlying the LOVs (ie, does it change often, rarely or never?)

    Andy
  • 18. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    Could I get access to your app like last time?

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://htmldb.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 19. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    so glad you are having fun! I can only imagine it has been equal to mine. :)

    I am using pl/sql to generate the form because of a couple of reasons: I intend to eventually have the second collection (efforts_C) be a tabular form (with many rows). This would mean that the page has 3 collections, two of which are tabular forms and each of those tabular forms has cascading lovs. It was my understanding (and again, I am pretty new on this) that in order to have multiple tabular forms, I would need to use collections...and the cascading LOVs enforced that belief.

    Good question on the static/dynamic LOVs...In the instance of AREA/SUB AREA they will not be longer than 25 values and those values are fairly static, however, they are based on the combination of distance_code (c020) and state (c021) so in a sense, they are not truly static lists.

    does that help. again, thank you!

    ps. i will check out your test
  • 20. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    Yes, as I understand it as well, multiple tabular forms have to be handled via collections as you specify the column numbers in batches - say, 1-10 for the first form and 11-20 for the second and so on - with the first column in each batch being the checkbox. This way you can identify which record relates to which underlying table.

    So far, my tests have concluded:

    1 - It is way easier to start with a simple select list that is based on "select null d, null r from dual" and allowing for extra values. Then, using calls to Ajax, generate the select lists based on whatever values are in the rows. This would also get around the issue of CLOBs

    2 - It is simpler to pass all necessary filters to the application process rather than to try and handle them in the select statement using complicated case or where clauses - ie, let the process handle it, just pass it whatever it needs to return the correct select list options

    3 - Even easier, and much quicker, is to handle the entire population of each list using javascript arrays - hence my question about how static the data is. In my test application, I have three lists: LOV1, LOV2 and LOV3. The first contains 10 records, the second contains 100 records (10 each for LOV1) and the third contains 1,000 records (10 each for LOV2). As long as the arrays contain fields that can be used to filter the records, then you could this approach. I have been splitting my test application into two - one for Ajax and one for Arrays - so that I can compare them.

    4 - Creating tabular forms entirely from scratch is a pain in the ****! Do you ever need to add records?

    Andy
  • 21. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    Hi Andy, I was just looking at your example! thanks. What would happen if selecting from LOV1 yielded no rows pulled from LOV2 and from LOV3 you wanted all additional rows that are not tied to LOV2, so if you had a column lov2 in your LOV3 which was null.

    do you see what I am saying.

    admittedly, I am a little intimidated by ajax (having had zip experience in javascript), and after spending so long (weeks) and getting so close (and deadline was aug 1, I hate to have to learn it. That said, I think if I take a break and re-approach, it may help. :)

    thanks again. Karen
  • 22. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    The fact that no rows would appear in LOV2, just means that the value of LOV2 is null or -1 or whatever we want an empty value to be. The filter for LOV3 would then just be whatever LOV2 key on that table matches null or -1 etc. ie, it's just another value to filter against.

    I think that, once you understand what Ajax does, and knowing that the javascript and application processes are pretty much the same for any Ajax call (at least as far as select list populating goes), it gets easier after each use to create the next one - almost a simple copy/paste/tweak job. I've heaps of javascript experience and would prefer to stick with that where practical - hence having pages that rely on javascript arrays rather than Ajax calls to get to the data in my example app.

    If the data in LOV1, LOV2 and LOV3 isn't likely to change, then I would recommend using the pure javascript approach - it's a lot quicker. If the data is changeable, then you have to use Ajax to get the latest data. However, in either case, I think it's easier for you to do post-rendering retrieval to get the data. By that I mean that you create the report with very small select lists (just the nulls and the values on the field) and then use either Ajax or Javascript to create the full lists.

    Either way, I can let you see all the code etc that I have used in my app and you should be able to update it to fit your tables/columns. The only page I had any trouble with is the last one - and that's only because I seem to have broken the links to the insert/update/delete mechanisms showhow. All the other pages were fairly straightforward as they are all created using the wizards.

    Just let me know how to want to proceed and we'll go from there.

    Regards

    Andy
  • 23. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    Hi Andy, thanks again...and thank you for the Ajax encouragement!

    I took your suggestion (and Denes') and created a simpler page...with just the one effort_c on it.

    I am now able to get the cascading lov to work when the second lov (c021) is null...or a 'dummy' value that I have placed in the table.

    now...it works on my simple page, but not on my more complex page and I think it has everything to do with the scripts in my page header. I eliminated most of the functions in my test page and perhaps that is why it is working?

    Is there a way I can check to determine if a function is being called?

    thanks!!

    ps. I would also like to see the code you have written, if it makes sense, I can provide you access to my workspace...but let me know what you think.

    really...you have been tremendous!
  • 24. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    You should definitely try out Ajax in your applications - it is an extremely useful functionality as pages are much slicker because you don't have to submit the page just to change one thing on it.

    If by "Is there a way I can check to determine if a function is being called", you mean a javascript function, then Yes, you just put a line inside your code that pops up a message:
    alert("Here");
    if you mean the application process, then I find the simplest method is to generate an error. Add something like the following into your pl/sql script:
    raise_application_error(-20001, "Here");
    This stops the rest of your code from running but at least you know that it is running.

    I can certainly let you see all of the code that I have used - normally I just copy the code into an HTML region on the page, giving appropriate region headers to show what's what. Would that do? Which bit of code would you want - the Ajax version or the Javascript Arrays version?

    Andy
  • 25. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    yes, I will try the Ajax! I think most of my hesitation is that this application is so behind and users are clamoring that I just need to get it up and going....slick will be the next version. :)

    if possible, could you post both the javascript and ajax?

    thankyou! I owe you a beer.
  • 26. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Sure, it'll have to be tomorrow now as I'm about to close down for the day.

    I don't drink - but may need to soon!

    Andy
  • 27. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    perfect. thanks again.
  • 28. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    Right, I've added in a Notes page and a Documents page.

    The Documents page contains everything you need to install my sample application (probably on your OTN workspace?). You even get stuff you need to create your own document upload/download functionality as it's part of the app now!

    The Notes page contains details of what I have done for the Standard Tabular Form pages.

    http://htmldb.oracle.com/pls/otn/f?p=20297:1

    Enjoy

    Andy
  • 29. Re: LOV null value really is not null - any suggestions here?
    34946 Newbie
    Currently Being Moderated
    I agree with the original thread, when an LOV is set back to the supposedly null value it correctly displays the null replacement,
    but the actual value in the item is a literal %null% which the documentation specifies as the null default here.

    I sent the values to the database via a pipelined table function and wrote them to a log table there....the resultant values are not true nulls, especially if you use them for anything.

    In pl/sql these values can be forced back to null, so function calls from the lov will still work, but any front end queries or conditions that evaluate this for null will fail. I suppose the conditions can use pl/sql expressions to see if :page_item is null or :page_item = '%null%' but this is ludicrous.

    If you force the item to null with a process/branch it really becomes null, until you select something and then select the null option..

    This actually wreaks havoc all over apex and I understand why people started using z or whatever as the null replacement.

    Not to be snippy about this, but perhaps the product manager for apex should talk to Brynn Llewellyn and get some advice on the meaning of null.

    Regards,

    Anthony Harper