Automatically send an email after a PanatrackerGP transaction

Automatically send an email after a PanatrackerGP transaction

This article is a quick introduction to the capability of sending an email based upon details captured in a PanatrackerGP transaction. This is done by using PanatrackerGP's feature that extends our transaction-processing logic via our "Pre submit" or "Post submit" stored procedures. (Note these are different from Microsoft's eConnect's pre/post procedures.)

Configure SQL Server for Email

First, you'll need to configure your SQL Server to allow for the sending of emails. This is a native feature for SQL Server and may already be configured in your environment. The process differs slightly for each version of SQL Server, but the concepts are basically similar.

Create the Stored Procedure

Next, create a stored procedure in your GP company database that PanatrackerGP will call at the start (or end) of our transaction workflow. It must contain the parameter TrxOid to identify our transaction record. Here's an example of a simple procedure that we'll call after we have updated GP for a fixed asset change:

  1. CREATE PROCEDURE Panatrack_PostTrxUpdateAssetNotify
  2. @TrxOid UNIQUEIDENTIFIER
  3. AS
  4. DECLARE @message AS VARCHAR(MAX)
  5. SELECT @message =
  6. N'The asset with barcode ' + AssetLabel + ' (AssetID: ' + AssetCode + '-
  7. ' +
  8. AssetCodeSuffix + ') had a location change to '
  9. + FacilityCode + ' in transaction ' + TransactionCode + '. Here is a recap of
  10. all changes: ' + ChangeSummary +'.'
  11. FROM PanatrackerGP7_TrxUpdateAsset
  12. WHERE ChangeSummary LIKE '%Location%' AND Oid = @TrxOid
  13. IF @message IS NOT NULL
  14. BEGIN
  15. SELECT @message
  16. EXEC msdb.dbo.sp_send_dbmail
  17. @profile_name = 'General Profile',
  18. @recipients = 'mickey@disney.com;
  19. minnie@disney.com', @subject = 'Asset location was
  20. changed', @body = @message;
  21. END
  22. GO
  23. GRANT EXEC ON Panatrack_PostTrxUpdateAssetNotify TO DYNGRP
  24. GO

In the example above, you will notice that we are using the TrxOid parameter as the identifier of a record in the PanatrackerGP database. We are getting that data by making use of database synonyms that are installed in the company database. We can then use information from that record to populate the body of the email message.

Configure PanatrackerGP

Lastly, configure PanatrackerGP to call the above stored procedure either before or after we tell Dynamics GP about the transaction data. You can set this from the PanatrackerGP portal's Transaction Setup page. Look for your desired transaction in the grid and find the column for Pre (or Post) Submit Stored Procedure.

In our example here, we will edit the transaction setup for Update Asset, and paste in the name of the stored procedure we created above (i.e., Panatrack_PostTrxUpdateAssetNotify). When a transaction is submitted, PanatrackerGP will call that stored procedure and pass the TrxOid automatically.

Grant Email Permissions

A permission may need to be configured in SQL to allow emails from your database server:

  1. use msdb
  2. grant exec on sp_send_dbmail to Panatracker

This has been a quick introduction to demonstrate the capabilities of this feature. Our professional services team would be happy to help you apply this in your own environment.

    • Related Articles

    • SMTP Configuration for Automated Email Alerts

      The PanatrackerGP application has the ability to send emails to inform administrators and users of system-level or transaction-related troubles. PanatrackerGP sends these emails via your SMTP server. During the installation and setup process, we'll ...

    • About PanatrackerGP

      PanatrackerGP is a mobile inventory management solution that creates transactions directly in Microsoft Dynamics GP using barcode scanning and a simplified interface. How It Works PanatrackerGP connects to your Dynamics GP database and creates ...

    • Panatracker Transaction Status code values

      The following table lists the values of the TransactionStatus field in PanatrackerGP transaction tables. Status Code Status Name Description 0 InProgress Transaction data is being gathered 1 Queued Saved for send, but submittal web service not yet ...

    • PO Receiving Transaction

      The PO Receiving transaction allows you to receive against Dynamics GP purchase orders and create the corresponding Receiving transaction in GP for posting and matching against invoices. Standard Single Purchase Order Receiving Select Purchase Order ...

    • Dynamics GP and PanatrackerGP Document/Transaction Numbers

      PanatrackerGP creates transactions in Dynamics GP using an independent number sequence. This design provides several benefits: Benefits of Independent Numbering Audit History PanatrackerGP users don't need to be set up as Dynamics GP users. The audit ...