How to tell in the Headline Function if the Customer Record is new.
Hint Ref: 020910150007
Hint Date: 15/10/2009
Hint Details:
The customer Headline function can be used to change the contents of various fields when a Customer Form is opened. However there may be times when you only want this to happen if the Customer Form is being displayed for a new customer and there after the fields should no longer update.
After having given this some thought, I decided that the best way to do this was to ensure that the fields would only be updated if the Current Date/Time was within 4 second of the Created Date/Time of the customer record. This Customer Headline Function sets a variable 'NewRec' to 1 if it is a new customer and 0 if it is not (i.e. the record is more than 4 seconds old). In this example, it then sets the OptIntoMarketing to 1 by default;
(in recordid char(20))
returns long varchar
begin
declare rv long varchar;
declare newrec smallint;
declare cdate char(25);
set rv='';
select string(created) into cdate from customer where customerid = recordid;
if val(replace(string(current time),':',''))-val(replace(substr(cdate,locate(cdate,' ')+1,8),':','')) < 5 and "date"("left"(cdate,10)) = current date then set newrec=1
else set newrec=0
end if;
if newrec = 1 then
update customer set OptIntoMarketing = 1
end if;
return(rv)
end
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)