Clarity PPM1

Expand all | Collapse all

Email notification - Process

Jump to Best Answer
  • 1.  Email notification - Process

    Posted 12-24-2018 09:20 AM

    Hi All

     

    I'm writing a process to get an email with details from a query. I've structured my query like this: 

    - Select query (multiple row data)

    - Email tag start

    - Pass in the select query data (using for each to display multiple lines)

    - Close the email tag

    But i can't get the email format correct. Can you please advise on how can i read in multiple row data in single email? 

     

    Thanks in advance. 

     

    Monica



  • 2.  Re: Email notification - Process
    Best Answer

    Posted 12-24-2018 01:06 PM

    You are most likely going to need to process your results set into a string for the body of the email.  Here is a simple example to list each record and the column values.

     

     

    <?xml version="1.0" encoding="utf-8"?>
    <gel:script xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:sql="jelly:sql"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


         <core:new className="java.lang.StringBuilder" var="stringBuilder" />

         <sql:setDataSource url="jdbc:sqlserver://localhost;databaseName=niku;" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="sa" password="password" var="clarityDS" />
         <sql:query dataSource="${clarityDS}" var="queryOne" sql="select id, user_name, EMAIL_ADDRESS from niku.CMN_SEC_USERS" />

         <core:forEach trim="true" items="${queryOne.rowsByIndex}" var="row">
              <core:invoke method="append" on="${stringBuilder}">
                   <core:arg type="java.lang.Object" value="&#xA;"/>
              </core:invoke>
              <core:forEach var="columnName" items="${queryOne.getColumnNames()}" indexVar="i">
                   <core:invoke method="append" on="${stringBuilder}">
                        <core:arg type="java.lang.Object" value="${columnName} = ${row[i]}&#xA;"/>
                   </core:invoke>
              </core:forEach>
         </core:forEach>

         <core:set var="body" value="&#xA;${stringBuilder.toString()}" />
         <gel:log>${body}</gel:log>

    </gel:script>

     

    The body tag contains:

     

     

    V/r,

    Gene



  • 3.  Re: Email notification - Process

    Posted 01-07-2019 10:58 AM

    Hi gcubed , thank you for the response. This helps me to append the text data, but the new line character does not seems to be working. I get the data returned as concatenated text, but not new line separated. I tried with tab space too, neither does that work. Any other recommendation on it? 



  • 4.  Re: Email notification - Process

    Posted 01-07-2019 12:01 PM

    If you are sending HTML email, try <br/> for line feed.  For plain text message try the java.lang.System.lineSeparator method into a var and append it to the stringbuilder.

     

    V/r,

    Gene



  • 5.  Re: Email notification - Process

    Posted 01-07-2019 04:26 PM

    hi gcubed, the stringBuilder itself worked good for line space when enclosed within <pre> tabs in <gel:email>. If not used, there is no line space is showing up in the email content.