Display field lookup instead of value in an email

Developers Programming
#1

I’ve read just about every post having to do with emails. I’m having trouble figuring out how to display the lookup value of a field in an email. in my code I have Assigned To: {assigned_to}. The value in the {assigned_to} field is an int. in the form I’m doing a lookup and matching a name with that int. In the email it displays Assigned To: 4 instead of Bob. Why does it ignore the lookup? Below is my code.

$mail_smtp_server = 'mail.xxx.com';        // SMTP server name or IP address
$mail_smtp_user   = 'xxxx';                   // SMTP user name
$mail_smtp_pass   = 'xxxx';                // SMTP password
$mail_from        = 'xxx@xxx.com';          // From email
$mail_to          = {email};         // To email
$mail_subject     = 'Notice!';            // Message subject
$mail_message     = "<strong>You have tasks to complete<strong><a><br><br>
		Task: {item_title}<br>
                Due Date: {due_date}<br>
	        Due Time: {due_time}<br>
                Assigned By: {created_by}<br>";
$mail_format      = 'H';                       // Message format: (T)ext or (H)tml

// Send email";
sc_mail_send($mail_smtp_server,
			 $mail_smtp_user,
             $mail_smtp_pass,
			 $mail_from,
			 $mail_to,
			 $mail_subject,
			 $mail_message,
			 $mail_format);
#2

[QUOTE=ancr2001;24680]I’ve read just about every post having to do with emails. I’m having trouble figuring out how to display the lookup value of a field in an email. in my code I have Assigned To: {assigned_to}. The value in the {assigned_to} field is an int. in the form I’m doing a lookup and matching a name with that int. In the email it displays Assigned To: 4 instead of Bob. Why does it ignore the lookup? Below is my code.

$mail_smtp_server = 'mail.xxx.com';        // SMTP server name or IP address
$mail_smtp_user   = 'xxxx';                   // SMTP user name
$mail_smtp_pass   = 'xxxx';                // SMTP password
$mail_from        = 'xxx@xxx.com';          // From email
$mail_to          = {email};         // To email
$mail_subject     = 'Notice!';            // Message subject
$mail_message     = "<strong>You have tasks to complete<strong><a><br><br>
		Task: {item_title}<br>
                Due Date: {due_date}<br>
	        Due Time: {due_time}<br>
                Assigned By: {created_by}<br>";
$mail_format      = 'H';                       // Message format: (T)ext or (H)tml

// Send email";
sc_mail_send($mail_smtp_server,
			 $mail_smtp_user,
             $mail_smtp_pass,
			 $mail_from,
			 $mail_to,
			 $mail_subject,
			 $mail_message,
			 $mail_format);

[/QUOTE]

Because {Field} Has the database value, don’t the value you see. You must do a lookup before in your code.


sc_lookup(rs, "select name from employees where employee_id={assigned_to}" );
$employee = {rs[0][0]};

...
...
...
...

$mail_smtp_server = 'mail.xxx.com';        // SMTP server name or IP address
$mail_smtp_user   = 'xxxx';                   // SMTP user name
$mail_smtp_pass   = 'xxxx';                // SMTP password
$mail_from        = 'xxx@xxx.com';          // From email
$mail_to          = {email};         // To email
$mail_subject     = 'Notice!';            // Message subject
$mail_message     = "<strong>You have tasks to complete<strong><a><br><br>
		Task: {item_title}<br>
                Due Date: {due_date}<br>
	        Due Time: {due_time}<br>
                Assigned By:".$employee."<br>";
$mail_format      = 'H';                       // Message format: (T)ext or (H)tml

// Send email";
sc_mail_send($mail_smtp_server,
			 $mail_smtp_user,
             $mail_smtp_pass,
			 $mail_from,
			 $mail_to,
			 $mail_subject,
			 $mail_message,
			 $mail_format);
#3

Thanks! Works great but… what if one of my fields has multiple values? How do I assign them to a variable. Do I assign an array to the variable filled with the rs array results? There would be an undetermined amount of | delimited records in that field so I don’t see how an array would work, seeing as how an array has a defined amount of rows/columns, unless I’m totally wrong.

#4

Multiple values are stored in one variable with a separator. Best option is to explode the variable into an array and then process the array for each value.

#5

EDITED***

^
|
|
|

What Albert sais.

*** EDITED

the “rs” array has a defined number of rows and columns because is the result of a query (wich in fact has a defined number of rows and columns based on your query). rs[0][1] for example, is the field you have with various strings delimited bu “|”

You can extract them with http://www.php.net/manual/es/function.explode.php and iterate the resultign array as you need.

Don’t know if this is what you mean.

#6

hi Giu,

tried this as you explained on sc8 latest version 8.005 but did not work, when sending the email it gives error “variable $team” undefined and it goes empty in the email message! here is my full code, it is in a button of a single-record form:



sc_lookup(rs, "SELECT teamdesc from teams WHERE teamid={team}");
$team = {rs[0][0]};
		   
$mail_smtp_server = 'localhost'; 
$mail_smtp_user   = 'xxxx@xxxx.com'; 
$mail_smtp_pass   = 'xxxxx'; 
$mail_from        = 'xxxx@xxxx.com';
$mail_to          = {mailto}; 
$mail_subject     = {subject};
$mail_message = "Hello <b>" .{nameto}. "</b>, ";
$mail_message .= "<br><br>";
$mail_message .= "A message was sent to you from: <b> " .[usr_name]. "</b>. <br>";
$mail_message .= "Subject: " .{subject}."<br>";
$mail_message .= "Additional Notes: " .{notes}."<br>";
$mail_message .= "<br>";
$mail_message .= "<hr>";
$mail_message .= "<b>Details:</b>";
$mail_message .= "<hr>";
$mail_message .= "Date: " .{date}."<br>";
$mail_message .= "Name: " .{name}."<br>";
$mail_message .= "Number: " .{number}."<br>";
$mail_message .= "Message: " .{message}."<br>";
$mail_message .= "Team: " .$team."<br>";
$mail_message .= "<hr>";
$mail_message .= "<i>This message was sent to you by " .[usr_name]. ", eMail: ".[usr_email]." .This is an auto generated message please do not reply.";


$mail_format      = 'H';                       // Message format: (T)ext or (H)tml
$mail_attachments = array(                     // List of attached files (located on the web server)
//	
                       );

// Send email
sc_mail_send($mail_smtp_server,
			 $mail_smtp_user,
			 $mail_smtp_pass,
			 $mail_from,
			 $mail_to,
			 $mail_subject,
			 $mail_message,
			 $mail_format,
			 '',
			 '',
			 '',
			 '',
			 $mail_attachments);




if ({sc_mail_ok}) {

 echo "<script>alert('Sent successfully!');</script>";

     sc_redir(grid_all_records);

} else { 	

     sc_error_message("There was a problem in sending! ");
}
#7

Can’t say itsme3. Do a var_dump($team) and see what contains.

#8

Got it working, perhaps it was a bug again and solved with latest version 8.005 now again stuck with the attachments in email, couldn’t make it work, but yet trying with different paths, and thanks to EricB who helped me to reach this far in emails sending thing :slight_smile: