6 Replies Latest reply: Nov 16, 2012 12:54 PM by 595830 RSS

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

    595830
      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
          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
            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
              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
                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
                  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
                    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