This discussion is archived
6 Replies Latest reply: Nov 16, 2012 10:54 AM by 595830 RSS

trouble hiding / showing element with DA based on results of query

595830 Newbie
Currently Being Moderated
Hi all,
I have a field that I would like to valid either on keypress or lose focus. What I need to do is query a table with the value entered to determine if it exists. If it does, I want to show a little green checkmark, if not a red 'x'. I figure that I can use getElementById() to hide or show the image, but I can't seem to figure out how to do that based on the result of the query.

I've tried this so far:
DA
event: lose focus
type: item
item: P3_PO_REF_NUMBER
condition: not null

true action: execute pl/sql code
code:
BEGIN
declare 
l_exists number;

begin
  select count(*) into l_exists
  from PO_LINE
  where BUSINESS_UNIT = 'METRO' and 
    (PO_ID = to_char(:P3_PO_REF_NUMBER,'FM000000000') or PO_ID = 'S'||:P3_PO_REF_NUMBER);

  if l_exists > 0 then 
    apex_util.set_session_state('P3_VALID', 'VALID');
  else
    apex_util.set_session_state('P3_VALID', 'NOT_VALUE');
  end if;
end;
END;
page items to submit: P3_PO_REF_NUMBER
page items to return: P3_VALID

Eventually I thought that I'd create another hide/show DA based on the value of P3_VALID, but before I went much further, I wanted to check to see how this worked. It didn't. Error: exception thrown and not caught.

Any thoughts as to what I'm missing, or am I doing this completely wrong?

Thanks,
Joe
  • 1. Re: trouble hiding / showing element with DA based on results of query
    Kofi Journeyer
    Currently Being Moderated
    Joe, have you tried using the debug feature of Apex? It is very handy at spotting where things went wrong.
    What was the exception you got anyhow?
    Kofi
  • 2. Re: trouble hiding / showing element with DA based on results of query
    595830 Newbie
    Currently Being Moderated
    Hi Kofi,
    Yeah, I often go to the debug, it more often than not is very useful in pinpointing where something broke. In this case though, its a JavaScript error. The page is rendering fine, but the script associated with the DA is failing. Overall, I have been very happy with how easy DA's, and Plugins for that matter, are to implement, but I keep thinking that there is a better way of accomplishing what I want, but I am drawing a blank as to how. :-(

    The error unfortunately isn't terribly helpful...well, not to me at least:

    Message: Exception thrown and not caught
    Line: 16
    Char: 11590
    Code: 0
    URI: http://<server>/i/javascript/apex_4_1.min.js


    I should probably add that our Apex version is 4.1 on 11gR2
  • 3. Re: trouble hiding / showing element with DA based on results of query
    595830 Newbie
    Currently Being Moderated
    Bit of an update:
    I rebuilt the DA as the following:

    Event: Lose Focus
    Selection Type: Item
    no condition

    Event Scope: bind

    True Action
    Action: Set Value
    Fire On Page Load: unchecked

    Set Type: PL/SQL Function Body
    Function Body:
    declare
     l_exists number;
    l_valid varchar2(2);
    
    begin
      select count(*) into l_exists
      from PO_LINE
      where BUSINESS_UNIT = 'METRO' and 
        (PO_ID = to_char(:P3_PO_REF_NUMBER,'FM0000000009') or PO_ID = 'S'||:P3_PO_REF_NUMBER);
    
      if l_exists > 0 then 
        l_valid := 'V';
      else
        l_valid := 'NV';
      end if;
    
      EXCEPTION
        WHEN OTHERS THEN
          l_valid := 'NV';
    
      return l_valid;
    
    end;
    Page Items to Submit: P3_PO_REF_NUMBER

    Affected Elements
    Selection Type: Item
    Items: P3_VALID

    The idea is to have P3_VALID as a hidden field and to create another DA to hide/show a DOM element based on this fields value.
    However, as noted above, this DA is breaking.

    Using Firebug, I was able to get this info:
    SyntaxError: JSON.parse: unexpected character apex_4_1.min.js (line 16)
    ...sFunction(h.promise)?h.promise().then(d.resolve,d.reject):d[g](h)}):b[a](d[g])})...

    I can't determine what is being passed that it doesn't like...Any thoughts would be welcome

    Thanks again,
    Joe
  • 4. Re: trouble hiding / showing element with DA based on results of query
    682558 Journeyer
    Currently Being Moderated
    Hi Joe,

    Your function needs to return a value BEFORE the exception as well as INSIDE the exception.
    The Exception is part of the begin end section

    DECLARE
    BEGIN
    CODE
    RETURN
    EXCEPTION
    CODE
    RETURN
    END

    Regards

    Michael

    Edited by: MKii on Nov 16, 2012 4:46 PM
  • 5. Re: trouble hiding / showing element with DA based on results of query
    595830 Newbie
    Currently Being Moderated
    Thanks MKii,
    You are right, so I changed my code up to:
    DECLARE
    l_exists number;
    l_valid varchar2(2);
     
    BEGIN
      select count(*) into l_exists
      from PO_LINE
      where BUSINESS_UNIT = 'METRO' and 
        (PO_ID = to_char(:P3_PO_REF_NUMBER,'FM0000000009') or PO_ID = 'S'||:P3_PO_REF_NUMBER);
     
      if l_exists > 0 then 
        l_valid := 'V';
      else
        l_valid := 'NV';
      end if;
    
      RETURN l_valid;
     
      EXCEPTION
        WHEN OTHERS THEN
          RETURN 'NV';
     
    END;
    But still getting the same JS error.. :(
  • 6. Re: trouble hiding / showing element with DA based on results of query
    595830 Newbie
    Currently Being Moderated
    Ok, just for closure. I did get this working.

    I ended up deleting the original P3_PO_REF_NUMBER item and recreated it, and everything began to work. As I was doing this, I noticed that it seemed like things would begin to go awry when I changed the item's name, even if I changed it back again. It was almost as if there was an associated between the DA that became corrupted once the item name was changed. If I took the item out of the page items to submit field, no error. Of course the DA wouldn't fire correctly then either because it wasn't getting any value changes, but when I added the item back into the page items to submit field, I go an error.

    So:
    When I created the second PO_REF_NUMBER item, I did not delete the original first, so the new one of course had to be named differently. Then I modified the DA to use the new item name (PO_REF_NUM). It worked! Cool. So, no wanting to get rid of the original yet, I rename it, and then renamed the second one to what the original was (I have other code using that item name). Then I changed the DA again...but now it failed - unless I blanked out the page items to submit field...

    3rd time is a charm. So, this time, I made sure that no items were named "PO_REF_NUMBER", then I created the PO_REF_NUMBER again, naming it exactly as I needed it to be so I wouldn't have to change it later. Then I set out to tweak the field (size, etc), but did not touch the name. Now my DA is working nicely! It sets a hidden field's value, then another DA shows or hides an icon based on that hidden field's.

    Hopefully someone will find this useful,
    Joe

Legend

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