This discussion is archived
3 Replies Latest reply: May 1, 2012 5:19 AM by mrpking RSS

Dynamic Action alert if email address exists

mrpking Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;
    }
    }

Legend

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