Forum Discussion

wpenfold's avatar
wpenfold
Contributor
5 years ago

Email snap with both pipeline variables and HTML table?

I need to produce an HTML email that includes both pipeline variable information in the HTML header and an HTML table that is a multiline error report. I can make one or the other work, but not both. The error report is being read from a database table. I want the email to look like:

Pipeline aaaa was run with parameters x, y, z

Error Report:

GUROUTP_LINE

POPULATION SELECTION EXTRACT
CONTROL REPORT PAGE 1

ERROR Parm 3 must be entered if 1 and 2 are
SQLCODE = 0000
SQL ERROR =
X01 ROLLBACK SQLCODE=0000
X01 COMMIT (1) SQLCODE=0000
SQLCODE = 0000
ORA-01403: no data found
DQY-ABORT ROLLBACK SQLCODE = 0000
ORA-01403: no data found

11 Replies

  • igormicev's avatar
    igormicev
    Contributor
    5 years ago

    Hi @wpenfold

    You’re probably close to your goal.

    Make changes in your email sender snap like below:

    I guess your Template body is OK, i.e. in the HTML you should have something like

    ...
<table> 
</table>
<br>
</body>
</html>

    and before the email sender snap you should have a mapper like this one (note the $table object) that provides the $table:

    That should be enough

    /Igor

    • wpenfold's avatar
      wpenfold
      Contributor
      5 years ago

      It appears I can only use $list if the table is the only input to the email snap. If I try to union in the header data I need to include in the email, then the snap objects

  • wpenfold's avatar
    wpenfold
    Contributor
    5 years ago

    I created the table as you suggested ($list), but if I try to use it for table-data path, I get ‘Failure: invalid path, Reason: Snap failed unexpectedly and did not provide any reason’. Even though the output from the mapper seems good…

    [

    {

    “job”:

    “rorbpst”

    “one_up_no”:

    3370175

    “exit_code”:

    0

    “output”:

    “ORACLE_BASE environment variable is not being set since this\ninformation is not available for the current user ID snapuser.\nYou can set ORACLE_BASE manually if it is required.\nResetting ORACLE_BASE to its previous value or ORACLE_HOME\nThe Oracle base has been set to /u01/oracle/12.2.0.1/client\ndoing banner C programs\n”

    “warning_message”:

    “Connected.\nConnected.\n”

    }

    {

    “list”:{

    “line”:"\n"

    }

    }

    {

    “list”:{

    “line”:" ELLUCIAN \n"

    }

    }

    {

    “list”:{

    “line”:" POPULATION SELECTION EXTRACT \n"

    }

    }

    {

    “list”:{

    “line”:" CONTROL REPORT PAGE 1 \n"

    }

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    {

    “list”:{…}

    }

    ]

  • cstewart's avatar
    cstewart
    Former Employee
    5 years ago

    @wpenfold It is certainly possible to both use the table data, and a set of other fields in the email template.
    I have an email sender which uses $group for the table, and then goes on to use a set of other fields (my summary data) in a footer. Those other fields are at the root level in my input document.

    • cstewart's avatar
      cstewart
      Former Employee
      5 years ago

      This is my template body:

      "<!DOCTYPE html> 
<html> 
<head> 
     <title>My Title</title> 
<style type=\"text/css\"> table.Snap {background-color:transparent;border-collapse:collapse;width:50%;} table.Snap th, 
      table.Snap td {text-align:center;border:1px solid black;padding:5px;} table.Snap th {background-color:AntiqueWhite;} 
</style> 
</head> 
<body> 
        <table class=\"Snap\" ALIGN='CENTER'> 
        </table> 
<h2>My Extra Details</h2>
<table>
<tr></tr>
<tr><td>FIrst Line in my Custom Table</td><td>" + $One + "/ " + $Two + "/ " + $Total + "</td></tr>
<tr><td>Second Line</td><td>"  + $Three + "/ " + $Four + "</td></tr>
<tr><td>Third Line</td><td>"  + $Five + "/ " + $Six + "/ " + $TotalThree + "</td></tr>
</table>
<br><H2>A load of other stuff</H2><br>" + $SummaryChanges + "
</body> 
</html>"
  • rfduboissl's avatar
    rfduboissl
    New Contributor II
    4 years ago

    @cstewart, I’ve attempted to use your solution, but I need further info. Do you have a snapshot or examples of the mapper snap going into this email snap as well as the email snap, showing the configuration. I have the email type set as “HTML table” using your solution and my email snap continues to give me => Failure: Template body: $complete_PDW is undefined.

    Below is the html in my template body:

    "<DOCTYPE html>
<html>
<head></head>
<p>Good morning Team,Today’s DQC values for your review.</p>
<body>
<p>1. Attunity Load Items Status/Issues</p>
<ul></ul>
<li>a). Status – Complete.</li>
<li>b). Issues – No issues</li>
<p>2.	PDW Load Items Status/Issues</p>
<table border=1>
<tr></tr>
<tr><td>PDW               – Completed at: </td><td>" + $complete_PDW + " </td></tr>
<tr><td>CALL Center       – Completed at: </td><td>"  + $complete_CC  + " </td></tr>
<tr><td>SpecRx            – Completed at: </td><td>"  + $complete_SPECRX + " </td></tr>
<tr><td>MHK               – Completed at: </td><td>"  + $complete_MHK + " </td></tr>
<tr><td>Reporting Cluster – Completed at: </td><td>"  + $complete_RPT_Cluster + " </td></tr>
</table>
<li>f)	Issues :  None</li>
</body>
</html>"
    • dmiller's avatar
      dmiller
      Former Employee
      4 years ago

      @rfduboissl because you have Table-driven path set to $table, it’s trying to find $complet_PDW within that, but base on your JSON image it is not within a $table but at the same level.

      I think you want to set your email type to HTML Text since you are explicitly listing the fields in the HTML. If you use HTML table as the Email type, it will append the table in the HTML.

      • rfduboissl's avatar
        rfduboissl
        New Contributor II
        4 years ago

        Diane …

        Thanks you for the reply.

        I DID figure this out and have it working.

        I will post the solution.

        Rich

  • dmiller's avatar
    dmiller
    Former Employee
    4 years ago

    @rfduboissl if you put the HTML in the preformatted text format, it won’t try to generate the HTML within the Community post.

  • LeeDuffy's avatar
    LeeDuffy
    New Contributor II
    2 years ago

    Late reply to this one!

    The above solution I am implementing, but my requirement would be to send 1 email with any parameter or previous $data type values that aren't part of the table, and also bring through the table. I replicated the table and $data on the "same level", which is working nicely, however I am getting x number of emails sent as there are 9 documents, when I only need 1 email.

    To resolve quickly, I added a Head snap which reduces the duplicate incoming stream to 1, just wondering if there is a better solution to reducing the documents to 1 for 1 email to be sent!

  • ptaylor's avatar
    ptaylor
    Employee
    3 years ago

    Ok. And you’ve set up the Mapper to map a string from the input document to the special field content, like this?

    Why do you need the Transcoder? The ASCII character set is extremely limited (only 128 characters) so if there are characters in the input that can’t be transcoded to ASCII that would be why you’re seeing that error. (The error message is confusing.)