Dynamic Action alert if email address exists
mrpkingApr 26 2012 — edited May 1 2012Hi - 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;