One Click Email

Prerequisites

The custom report you wish to be emailed via the email button in the Estimate, Sales Order, Purchase Order and Invoice modules must be set up to print in place of the corresponding standard OrderStream report.  For example, your custom order confirmation report is the report that prints when Print Order Confirmation is selected from the sales order module’s Reports menu.

To do this, first go to Company Setup and click on the Additional Information tab.  Note the value that is present in the DNS Name field.  If this field is blank, enter a value.  The value can be anything, though typically your company name or an abbreviated version of it is entered.

Next, open your reports Access database.  Open the Reports table and locate the report(s) you wish to print in place of the standard OrderStream equivalent.  Change the value of the Name field for the appropriate reports to the following format:

[DNS Name] [Standard Report Name]

The names of the relevant standard reports are as follows:

Thus, if the value in the DNS field is “Spec” and you wish to have your custom order confirmation report print in place of the standard OrderStream order confirmation report, enter Spec Order Confirmation into the Name field in the reports database Reports table for your custom order confirmation report.

Reports dll:

If you have a company specific reports dll (the naming convention for such files is typically [CompanyName]Reports.dll) and this file was last modified in a year prior to 2010, you will likely require an updated copy of this dll.

If you are using ClientReports.dll then no update will be necessary to implement direct emailing.

Reports Database Setup

The setup required for one click emailing is performed in the ReportDestinationDetails of your reports database.  The data model of this table is as follows:

Destination Address Queries

Rules:

Sample Queries:

The following query will return the email address of contact assigned to the header of an estimate:

SELECT Contacts.Name AS BilltoContact, Contacts.email AS BilltoContactMail, Estimate.EstimateID FROM Estimate INNER JOIN Contacts ON Estimate.ContactID = Contacts.ContactID

The following query will return email addresses for all contacts assigned to a prospect or customer associated to the estimate being emailed where the email address is not blank:

SELECT     CASE WHEN dbo.Estimate.CustomerID IS NOT NULL THEN CustomerContact.BillToContact ELSE ProspectContact.BillToContact END AS BillToContact,  CASE WHEN dbo.Estimate.CustomerID IS NOT NULL THEN CustomerContact.BillToContactMail ELSE ProspectContact.BillToContactMail END AS BillToContactMail,  dbo.Estimate.EstimateID

FROM  dbo.Estimate LEFT OUTER JOIN  (SELECT  dbo.Contacts.email AS BilltoContactMail, dbo.Contacts.Name AS BilltoContact, dbo.ContactLinks.LinkID  FROM   dbo.ContactLinks INNER JOIN dbo.Contacts ON dbo.ContactLinks.ContactID = dbo.Contacts.ContactID WHERE  (dbo.ContactLinks.LinkType = 0) AND (dbo.Contacts.email <> '')) AS CustomerContact ON   dbo.Estimate.CustomerID = CustomerContact.LinkID LEFT OUTER JOIN  (SELECT     Contacts_1.Name AS BilltoContact, Contacts_1.email AS BilltoContactMail, ContactLinks_1.LinkID  FROM    dbo.ContactLinks AS ContactLinks_1 INNER JOIN dbo.Contacts AS Contacts_1 ON ContactLinks_1.ContactID = Contacts_1.ContactID

WHERE      (ContactLinks_1.LinkType = 3) AND (Contacts_1.email <> '')) AS ProspectContact ON dbo.Estimate.ProspectID = ProspectContact.LinkID

Creating the Subject

The subject text is mostly a hard coded value; however, if the name of a field being returned by the main report query is placed within brace brackets, the name of the field will be replaced by the value being returned within that field when the report query is executed.  Note that if the field is aliased within the main report query, place the alias within brace brackets in place of the actual field name.

Order Confirmation Sample Subject:

Your PO {CustomerPO} Order Confirmation (S&P Furniture {SalesOrderNo})

CustomerPO and SalesOrderNo are fields being returned by the main report query.  Since the field names are within brace brackets, the field names will be replace with the actual value returned for these fields by the report query.  The resulting output would be similar the following:

Your PO 542365 Order Confirmation (S&P Furniture SO1102778)

CC Address Queries

Rules:

Similar to the rules for the Destination Address the CC recipient name must precede the recipient’s email address within the query.  The following aliases are available for use within the CC Address query:

Remember to include the appropriate Transaction ID at the end of CC Address query.

Sample Query:

SELECT DISTINCT  Contacts_1.Name AS ShiptoContact, Contacts_1.email AS ShiptoContactMail, Employees_1.LastName AS SalesRep,  Employees_1.email AS SalesRepMail, Employees_2.LastName AS CSR, Employees_2.email AS CSRMail, dbo.SalesOrder.SalesOrderID

FROM   dbo.Employees Employees_1 RIGHT OUTER JOIN dbo.CustomerShipTo RIGHT OUTER JOIN dbo.SalesOrder ON dbo.CustomerShipTo.CustomerShipToID = dbo.SalesOrder.CustomerShipToID LEFT OUTER JOIN dbo.Contacts Contacts_1 ON dbo.CustomerShipTo.ContactID = Contacts_1.ContactID LEFT OUTER JOIN dbo.SalesReps ON dbo.SalesOrder.SalesRepID = dbo.SalesReps.SalesRepID ON Employees_1.EmployeeID = dbo.SalesReps.EmployeeID LEFT OUTER JOIN dbo.Employees Employees_2 ON dbo.SalesOrder.CSREmployeeID = Employees_2.EmployeeID

Creating the Email Body

Similar to the Subject text, the body text is mostly a hard coded value. Text replacement is supported in the same way it is supported within the Subject field.

If you wish to include the value of a field within the main report query within the body text, simply place the name of the field (or the alias if the field is aliased) within brace brackets.

If you wish to place the name of the employee who created the transaction being emailed within the body text you can either retrieve the employee name via the main report query or include #Employee within the body text.  #Employee will be replaced at run time with the employee’s name.

Order Confirmation Sample Body:

Thank you for your recent order.  Attached is your Order Confirmation for sales order {SalesOrderNo}.

If there is anything else we can help you with, please let us know.

Regards,

#Employee 

Customer Service