Sage CRM 7.2: How Mail Merge Works

The Sage CRM Blog

Articles of general interest about Sage CRM, the community, add-ons and general news!

Sage CRM 7.2: How Mail Merge Works

  • Comments 2
  • Likes

In this article I want to discuss how the Mail Merge process works in detail. This includes what happens within Sage CRM and how the document is created.

The Mail Merge mechanism changed in Sage CRM v7.1sp2. If need to know how Mail Merge used to work then see the article "How Mail Merge Works in Sage CRM v7.1 SP1 and earlier."

Mail Merge for Sage CRM v7.1sp2 onwards takes place server side.

I have discussed previously in the article "System Architecture" how Sage CRM's design is undergoing a process of change.

Sage CRM is moving more and more of its features into the java based technologies. Mail Merge is just one of the features that have been changed to work as a server side process under the Tomcat/Java processes.

It is important to realise that because the merge takes place on the server that no text file or cookie is created during the new Mail Merge process and application data is not passed to the client.

The old mechanism prior to Sage CRM 7.1 sp2 used to create files like "ewaremerge0.dat" and "ewareQuote0.dat" but these files are NEVER created now.

There have been some very helpful articles written that explain how the Mail Merge works in the new technology.

Data Sources

Whether the server-side mail merge uses an HTML template or a Word template the data for the merge needs to be drawn from an underlying view. The view will changing according to the context in which the mail merge is initiated.

The merge templates are stored in the Administration area

Administration -> E-mail and Documents -> Document Templates

As I have mention above Sage CRM now supports 2 types of templates for the merge process.

  • HTML Templates
  • Classic Sage CRM Word Templates

HTML Templates can be saved as files before being uploaded into Document Templates. HTML extensions are allowed

  • Templatename.html
  • Templatename.htm

Template for Quotes & Orders must be saved of correct Type

  • Quote
  • Order

Note: If your system uses a network share for mail merge templates then you will need to manually copy the new files to the network share.

All users can create their own private templates and the process of creating a new template can be easily started by starting to perform a mail merge.

Below you can see that a new HTML Template has started to be created.

You just need to start the mail merge process and opt to create a new template.

The template will then be available in the context where created.

The context determines the views that used and the fields that can be added to the template.

The following views are used

Context

  • Company - vMailMergeCompany
  • Person - vMailMerge
  • Opportunity - vMailMergeOpportunity
  • Case - vMailMergeCase
  • Lead - vListLead

The Orders & Quotes are different as the merge process allows for header and detail merges to take place using information from two separate views.

  • Quotes use vMailMergeQuotes and vMailMergeChildrenQuotes
  • Orders use vMailMergeOrders and vMailMergeChildrenOrders

vMailMergeChildrenQuotes

CREATE VIEW vMailMergeChildrenQuotes AS SELECT QuoteItems.*, NewProduct.* FROM QuoteItems Left Outer Join NewProduct ON QuIt_ProductId = Prod_ProductId  WHERE Quit_Deleted IS NULL

vMailMergeChildrenOrders

CREATE VIEW vMailMergeChildrenOrders AS SELECT OrderItems.*, NewProduct.* FROM OrderItems Left Outer Join NewProduct ON OrIt_ProductId = Prod_ProductId  WHERE Orit_Deleted IS NULL

The process of creating HTML Quote or Merge templates requires you to insert special nested regions into the HTML of the template.

You can start the template using "Insert Nested Region". It is strongly recommended that you include the child information from the Order Items or the Quote Items with the structure of a table.

You should include the CID field beside each currency field. E.g «orIt_quotedprice_CID» «OrIt_quotedprice» as this will ensure correct formatting of the currency information.

Comments
  • Jeff:

    Thank you for the post.  This is a very helpful post on how mail merge works in 7.2.  

    I need some direction in completing a mail merge that works similarly to the Quotes mail merge...but it is not quote data.  It is a company's account (transaction) activity.  We have a statement that we want to create for company records.  We created a secondary entity that contains the transaction data.

    Are there any examples that would help us get the statement activity (transactions) included as part of the mail merge for a company.  We looked at the Quotes template included with the install (and as you are showing above).  However, we soon figured out that the TableStart and TableEnd appeared to be part of the "Nested Region" option.  Is there a way to insert these "special nested regions" in our company mail merge for our statement activity?  Any assistance you can provide would be greatly appreciated.

    Thanks!

  • Hello Jeff. We are mightily confused. We have added new fields to vMailMergeOpportunity and they are not coming up when we merge documents. In the SQL profiler we can see CRMs pulling down the data, however the dll is not spitting that data out onto the word document. Is there any meta data that needs refreshing? (We did try refresh metadata, of course :) ). Is there something I am missing? The old fields still come through correctly from the mail merge, just new fields don't. If I ALIAS a non working field, as a WORKING fields name, that aliased field comes through. So say, Pers_Firstname comes through, and Pers_wibble, a new field, doesn't. If I change Pers_wibble in the view to Pers_wibble AS Pers_Firstname and drop Pers_firstname out, put the field reference in the word document as <> it works fine and the data is Pers_wibble comes through fine. If I alias it to something else, like Per_wibble AS Bob, it doesn't work either. Its crazy man, crazy.