Logic Extender
This mechanism allows adding columns (fields) within the grids shown on the modules. These new fields are typically used to narrow down the selection of documents; however, it can also be used to overwrite fields such as "emailAddress" with a custom logic i.e. data contained in a user defined field.
The lookup column name is the field that is passed to the SQL script as a parameter. Any available fields can be used as a parameter adding ‘@’ (or ':' for Oracle) as a prefix. To see the complete list of available fields open the ‘Look up column Name’ drop-down list from the grid. The SQL script can return any number of columns but only 1 row. If it returns more than 1 row, only the values within the first one will be added to the main grid within the selected module.
To delete an Extended Logic rule, select the row and press Delete. Alternatively, you can uncheck the "Is Active" column. In this case, the selected rule will be preserved but it will not run.
Any column available within each module's grid (default or added through the Logic Extender) can be used as part of the email's subject or body.
Examples:
For SQL:
SELECT USER_8 AS emailAddress
FROM CUSTOMER
WHERE (ID = @CUSTOMER_ID)
Generating Html code to be used within the body of emails:
DECLARE @CUSTOMER_NAME nvarchar(80)
DECLARE @CUST_ORDER_ID nvarchar(80)
DECLARE @SHIP_VIA nvarchar(80)
DECLARE @SHIPTO_NAME nvarchar(100)
DECLARE @SHIPTO_ADDRESS nvarchar(200)
DECLARE @HeaderTableHTML nvarchar(MAX)
DECLARE @DetailstableHTML nvarchar(MAX)
SELECT @CUSTOMER_NAME = CUSTOMER.NAME , @CUST_ORDER_ID = CUSTOMER_ORDER.ID , @SHIP_VIA=ISNULL(CUSTOMER_ORDER.SHIP_VIA,''),@SHIPTO_NAME = ISNULL(CUST_ADDRESS.NAME, '') , @SHIPTO_ADDRESS=
ISNULL(CUST_ADDRESS.ADDR_1, '') + char(13) + ISNULL(CUSTOMER.ADDR_2, '') + char(13) + ISNULL(CUSTOMER.ADDR_3, '') + char(13) + ISNULL(CUST_ADDRESS.CITY, '') + char(13)
+ ISNULL(CUST_ADDRESS.STATE, '') + char(13) + ISNULL(CUST_ADDRESS.ZIPCODE, '') + char(13) + ISNULL(CUST_ADDRESS.COUNTRY, '')
FROM CUSTOMER INNER JOIN
CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID INNER JOIN
SHIPPER ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID LEFT OUTER JOIN
CUST_ADDRESS ON CUSTOMER_ORDER.CUSTOMER_ID = CUST_ADDRESS.CUSTOMER_ID AND CUSTOMER_ORDER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
WHERE (SHIPPER.PACKLIST_ID = @PACKLIST_ID)
SET @HeaderTableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Arial", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 1px solid #9baff1;
border-bottom: 1px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'
+
N'<H2><font color="DarkBlue">SHIPPING CONFIRMATION</H2>'
+
N'<H3><font color="DarkBlue">Dear ' + @CUSTOMER_NAME + ', your order <font color="Red">' + @CUST_ORDER_ID + '<font color="DarkBlue"> has shipped.</H3>'
+
N'<H3><font color="DarkBlue"> <u> Details: </u>'
+
'<p> <b><font color="DarkBlue">ShipTo: </b> <font color="Red">' + @SHIPTO_NAME + '</p>'
+
'<p> <b><font color="DarkBlue">Shipped via: </b> <font color="Red">' + @SHIP_VIA + '</p>'
+
'<p> <b><font color="DarkBlue">Ship to Address: </b> <font color="Red"> ' + @SHIPTO_ADDRESS + '</p>'
SET @DetailstableHTML =
N'<table id="box-table" >' +
N'<tr><font color="Green">
<th>Line#</th>
<th>Part Id</th>
<th>Shipped Qty</th>
</tr>' +
CAST ( (
SELECT
td = SHIPPER_LINE.LINE_NO ,'',
td = isnull(CUST_ORDER_LINE.PART_ID,'') ,'',
td = SHIPPER_LINE.SHIPPED_QTY
FROM SHIPPER_LINE INNER JOIN
CUST_ORDER_LINE ON SHIPPER_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND SHIPPER_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO
WHERE (SHIPPER_LINE.PACKLIST_ID = @PACKLIST_ID)
ORDER BY SHIPPER_LINE.LINE_NO
FOR XML PATH('tr'), TYPE) AS nvarchar(MAX) ) +
'</table>
<br>
<p><button type="button">Track your package</button></p>
<p><font color="DarkBlue">Should you wish to contact us for any reason please contact our sales team on 1300 222 2663 or sales@axme.com</p>
<div>
<![if mso]>
<v:roundrect xmlns:v="urn:schemas-microsoft-com:vml" xmlns:w="urn:schemas-microsoft-com:office:word" href="http://bms365.com" style="height:40px;v-text-anchor:middle;width:300px;" arcsize="10%" stroke="f" fillcolor="#d62828">
<w:anchorlock/>
<center style="color:#ffffff;font-family:sans-serif;font-size:16px;font-weight:bold;">
Contact Axme
</center>
</v:roundrect>
<![endif]>
<![if !mso]>
<table cellspacing="0" cellpadding="0"> <tr>
<td align="center" width="300" height="40" bgcolor="#d62828" style="-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; color: #ffffff; display: block;">
<a href="http://bms365.com" style="font-size:16px; font-weight: bold; font-family:sans-serif; text-decoration: none; line-height:40px; width:100%; display:inline-block">
<span style="color: #ffffff;">
Contact Axme
</span>
</a>
</td>
</tr> </table>
<![endif]>
</div>'
SELECT @HeaderTableHTML + '<br><br>' + @DetailstableHTML AS Html
The code shown above will create a new column called Html which will be available from the module's grid (it can be made not visible if desired). Since the column is now available from the grid, it can be also used to build the email's subject or body. As any other field, it can be referenced by adding "@" as a prefix (see snapshot below)
