Announcement

Collapse
No announcement yet.

Display field lookup instead of value in an email

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Display field lookup instead of value in an email

    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.

    PHP 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
    Originally posted by ancr2001 View Post
    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.

    PHP 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); 

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

    PHP 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); 
    /Giuseppe

    Professional Scriptcase Services
    Some Customers opinions

    Comment


    • #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.

      Comment


      • #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.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          EDITED***

          ^
          |
          |
          |

          What Albert sais.


          *** EDITED

          Originally posted by ancr2001 View Post
          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.
          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.
          /Giuseppe

          Professional Scriptcase Services
          Some Customers opinions

          Comment


          • #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:

            Code:
            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! ");
            }

            Comment


            • #7
              Can't say itsme3. Do a var_dump($team) and see what contains.
              /Giuseppe

              Professional Scriptcase Services
              Some Customers opinions

              Comment


              • #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

                Comment

                Working...
                X