3 Replies Latest reply: May 1, 2012 7:19 AM by mrpking RSS

    Dynamic Action alert if email address exists

    mrpking
      Hi - I am running 4.1 on 11GR2 using IE (bad habit) and Firefox/Firebug as needed. I have a contact form with 500+ contacts. I have a validation that checks for existing contact email address when the form is submitted. I would like to check it when they enter their email address in the first item on the form, and alert them immediately, so they do not have to fill out the entire form. I added a DA (sequence 10) that sets the value on change or lose focus of the item :P2_E_MAIL_ADDRESS. I see the email address value is in session state. I added a second DA (sequence 11) with condition exists (sql returns at least one row) select id from purchasing_contacts where e_mail_address=:P2_E_MAIL_ADDRESS. I can't get the alert to fire with either SQL exists or PL/SQL Function Body reurning a boolean. The alert fires if i remove the condition on the second DA but I need it to fire when the value entered exists in contacts. Can I get a DA to fire off the validation, or is there a better way to do this? I am a DBA and I have been working with APEX for years. I am a javascript/DA newbie. Any help is greatly appreciated.

      Peter

      The conditional validation returning boolean looks like this, and it works on submit. the DA does not fire alert with the condition on the DA, which is the same as the validation except if v_email is null then return false;
      else return true;
      declare
      cursor contacts_cursor is
      select e_mail_address
      from purchasing_contacts
      where e_mail_address=:P2_E_MAIL_ADDRESS;
      v_email varchar(50);
      begin
      open contacts_cursor;
      fetch contacts_cursor into v_email;
      if v_email is null then return true;
      else return false;
      end if;
      end;
        • 1. Re: Dynamic Action alert if email address exists
          450214
          Hi
          Please try the below method, which will invoke ajax call, and popup javascript alert when an email address exist.

          Step1:
          Create on demand application process with name: emailid_check

          declare
          lnum number;
          begin
          select 1 into lnum from purchasing_contacts where upper(e_mail_address) = upper(:P2_E_MAIL_ADDRESS) ;

          HTP.prn ('Email id exist..');

          exception
          when others then
          NULL;
          end;

          Step2: Create java script funtion in the javascript tab of page properties

          function emailIdExistcheck()
          {
          var ajaxRequest = new htmldb_Get( null , &APP_ID. , 'APPLICATION_PROCESS=emailid_check', 0);

          ajaxRequest.add( 'P2_E_MAIL_ADDRESS', $v('P2_E_MAIL_ADDRESS'));

          var gReturn = ajaxRequest.get();

          if(gReturn)

          alert(gReturn);
          // force focus on the email id text item
          $f_First_field('P2_E_MAIL_ADDRESS');
          }
          else
          {  ajaxRequest = null;

          }
          }

          Step3: In the email id text item properties go to Element section and do the below step
          HTML Form Element Attributes onBlur="javascript:emailIdExistcheck();"


          Thnx
          MK
          • 2. Re: Dynamic Action alert if email address exists
            mrpking
            MK- Beautiful. Thank you so much. I would never get this on my own. The alert works like a charm. Is there a method for escape so if the message says "Click Welcome tab..." or something else, it will let me, without having to change or delete the typed email address? I can click OK but unless I remove all or part of the email address I am stuck. Be careful what you wish for, right? I should have thought ahead to what happens next. Thanks. Peter

            Edited by: mrpking on Apr 30, 2012 7:26 AM I tried adding :P2_E_MAIL_ADDRESS := null; to the application process right after begin to clear the item. Not sure how to change session state item value in this context.
            • 3. Re: Dynamic Action alert if email address exists
              mrpking
              Got it. I added $x_Value('P1_E_MAIL_ADDRESS',''); Very cool. Thanks again, MK.

              Peter

              var htmldb_delete_message='"DELETE_CONFIRM_MSG"';
              function emailIdExistcheck()
              {
              var ajaxRequest = new htmldb_Get( null , &APP_ID. , 'APPLICATION_PROCESS=emailid_check', 0);
              ajaxRequest.add( 'P2_E_MAIL_ADDRESS', $v('P2_E_MAIL_ADDRESS'));
              var gReturn = ajaxRequest.get();
              if(gReturn)
              {
              alert(gReturn);
              // force focus on the email id text item
              $f_First_field('P2_E_MAIL_ADDRESS');
              $x_Value('P2_E_MAIL_ADDRESS','');
              }
              else
              { ajaxRequest = null;
              }
              }