Sending an automated email without the Event System


Introduction

Sometimes the Infor “Event System” / Work flow system can be a sledge hammer to crack a walnut. If you are not using it, and have not yet configured it, to send a one off notification it can take a while to get going. If you are using custom tables that you have added the event system will need more work to get it to fire.

In the following example, we are  :-

– Scanning Exchange Mailboxes every 3 minutes

– Extracting PDF attachments

– Passing the documents to AbbySoft flexicapture for Optical Character Recognition (OCR)

– Stripping all core information out and passing the details to a holding table

If / When I get chance I will go into the whole OCR solution in more detail, however the purpose of this post is to show how we have written a trigger to then email the users that they need to process something. If you are following this – please bear in mind, I have already configured my database mail.

Stage 1 : Keep it simple

I like to “evolve” my solutions, rather than going for a big bang approach, and get feedback from the users regarding what information they would like to see. Always trial this on a test database, prior to implementing on a production environment.

use <Database Name>

go

if object_id (‘PBTI_TRIG_OcrInser’,‘tr’) is not null

drop trigger PBTI_TRIG_OcrInser

go

create trigger PBTI_TRIG_OcrInser on PBTI_ocrco

for insert

as

declare @Subject as nvarchar(max)

declare @Message as nvarchar(max)

select

@Subject = ‘New ORC Record inserted for customer’ + CustNum +‘.’

, @Message = ‘New ORC Record inserted for customer’ + CustNum +‘.’

from inserted;

exec msdb.dbo.sp_send_dbmail

@profile_name=‘PBTI’,

@recipients=<email address 1>,<email address 2>,

@importance=‘High’,

@subject=‘New OCR Record Inserted’,

@body_format=‘HTML’,

@body=@Message

This gives quite a bland email alerting the users as follows :-

EmailFromTrigger

Make the TSQL trigger based email user friendly

The next stage is to format the email, giving the users more information and allowing them to click through to the form so they can action the notification.

We want the format to mirror other notfications we have for automated order processing (EDI), so the CSS is inserted at the top of each email (this means it looks correct even if the user has no internet connection, but does increase the size of the email). So as our emails can be amended, we hold the recored in the “Event Global Constants” table.

The T-SQL code now looks like this :-

USE [Live_App]

GO

/****** Object: Trigger [dbo].[PBTI_TRIG_OcrInser] Script Date: 09/16/2014 10:26:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER trigger [dbo].[PBTI_TRIG_OcrInser] on [dbo].[pbti_ocrco]

for insert

as

declare @Subject as nvarchar(max)

declare @Message as nvarchar(max)

declare @htmlhead as nvarchar(max)

select

@Subject = ‘New ORC Record inserted for customer ‘ + CustNum +‘.’

,@htmlhead = (select top 1 value from dbo.EventGlobalConstant where Name = ‘HTMLHeader’)

, @Message = @htmlhead+

‘An OCR Order has been received from ‘ +CustNum +

‘<hr />

Please action this order ASAP. you will need to do the following

<ol>

<li>Open <a href=”http://<application server name>/SLClientDeploy/SyteLine.application?Form=PBTI_CustomerOrdersImport&Config=UnitedKingdom”>PBTI Customer Orders Import</a></li>

<li>Select and import the customer order</li>

<li>Raise and production Jobs</li>

<li>Send Order Acknowledgement</li>

<li>Print the pick list</li>

</ol>

<table>

<tr>

<th>Customer PO</th><th>Customer Number</th><th>Total Price</th>

</tr>

<tr>

<td>’ + isnull(po_num,) + ‘</td><td>’+ isnull(CustNum,) +‘</td><td>’ + isnull(price,) + ‘</td>

</tr>

</table>

<table>

<tr>

<th>Bill to Address</th><th>Ship to Address</th>

</tr>

<tr>

<td>’ + isnull(bill_to_addr,) + ‘</td><td>’ + isnull(ship_to_addr,) + ‘</td>

</tr>

</table>

from inserted;

exec msdb.dbo.sp_send_dbmail

@profile_name=‘PBTI’,

@recipients=‘<recipient 1>,

@importance=‘High’,

@subject=@Subject,

@body_format=‘HTML’,

@body=@Message

 

Which means the email now comes out as follows :-

EmailFromTriggerAdvanced

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.