Harriet_Kim_1764

[Version 10] Output Query Results in Table Format to an E-mail Notification

Discussion created by Harriet_Kim_1764 on Jul 19, 2016
Latest reply on Oct 5, 2018 by SudharsonNarayanamoorthy603801
Is there a way to dynamically output database results in table format to an e-mail notification?

Things to consider:
  • On version 10
  • String data type has a character limitation (1023)
  • Output needs to be in the body of the e-mail notification, not in an attachment 

1.  VARA object

bpp4as52ksec.jpg

Theresult formattext box was used to create the table border line columns.

2.  Call Notification object

In theNotificationtab:

 

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">


<html><head>


<style type="text/css">


.c1 {


  background-color: #ECAC00;


  font-family: Arial, Helvetica, sans-serif;


  font-size: 0.8em;


  font-style: italic;


  padding: 2px 10px 2px 10px;


}


.c2 {


  background-color: #ECECEC;


  font-size: 0.8em;


  font-weight: bold;


  padding-left: 5px;


  padding-right: 5px;


}


.c3 {


  background-color: #B2B2B2;


  font-size: 1.5em;


  padding: 5px 5px 5px 5px;


  text-align: center;


}


table,th,td {


border:1px solid black;


border-collapse: collapse;


}


</style>


</head>


<body>


<table>


  <tr>


    <th>Client#</th>


    <th>Object Type</th>


    <th>Object Name</th>


    <th>Object IDNR</th>


    <th>Object Title</th>


    <th>Delete Flag</th>


  </tr>


 

&OUT#[1]


&OUT#[2]


&OUT#[3]


&OUT#[4]


&OUT#[5]


&OUT#[6]


&OUT#[7]


&OUT#[8]


&OUT#[9]


&OUT#[10]


&OUT#[11]


</table>

</body>


</html>

 

In the Processtab:

 

:DEFINE &OUT#, STRING, 20


:DEFINE &HTML#, STRING, 20


:SET &LINECNT# = 1


:SET &HND#  =  PREP_PROCESS_VAR(VARA.INDEX.HKI)


:PROCESS &HND#

:  SET &LINECNT# = ADD(&LINECNT#, 1)


:  SET &LINECNT# = FORMAT(&LINECNT#)


:  SET &temp# = GET_PROCESS_LINE(&HND#,1)


:  SET &OUT#[&linecnt#] = &temp#


:  PRINT &LINECNT# = &OUT#[&LINECNT#]


:ENDPROCESS


:CLOSE_PROCESS &HND#

 

3.  E-mail output

lj3cfhqlx401.jpg

This will only return up to 11 rows.  Anything over 11 rows, &OUT#[x] needs to be added manually in theNotificationtab.

Any additional input/advice from anyone would be greatly appreciated.  Thanks in advance for your help!

Outcomes