Merging to word templates feature can be used to merge quote/order details, search results group results etc to the document. One of the nice feature present in CRM is merging documents from summary screens. A case scenario of how we use it is like when we Create new customer in system, we generate welcome letter for him. An easiest way of doing this can be very easily achieved in three standard steps.
- Create template of Welcome letter
- Go to Person Summary screen
- Right click New button and select Document.
- Select Welcome letter template and your done.
Here you can also use the hash fields on entity tables. As you are using Person entity here putting #pers_firstname# automatically recognizes person first name and puts it in place holder of the hash field. Now where are these values being pulled from and how does system identify where to put them. Well the answer is vMailMerge view. You can fire below query on CRM database and see what all columns are coming up.
–vMailMerge result
Select * from vMailMerge where pers_personid = ‘<Any person id>’
Whatever columns are output through above query can be added to template you are creating, can be used as hash fields and will also be automatically handled by standard merging process at the time of actually merging data to document. Now what if you want to add some more information which is not there in current view? Consider below scenario.
Person has a child entity linked to it named Profile which has a field named description which needs to be displayed in the word template being merged. How will you go about implementing this scenario? Here are the steps.
- Go to Administration | Customization | Person | Views | vMailMerge.
- Change the view query as one mentioned below. See the change in Red color.
SELECT Pers_PersonId, Comp_CompanyId, RTRIM(ISNULL(Pers_PhoneCountryCode, ”)) + ‘ ‘ +
RTRIM(ISNULL(Pers_PhoneAreaCode, ”)) + ‘ ‘ + RTRIM(ISNULL(Pers_PhoneNumber, ”)) AS Pers_PhoneFullNumber,
RTRIM(ISNULL(Pers_FaxCountryCode, ”)) + ‘ ‘ + RTRIM(ISNULL(Pers_FaxAreaCode, ”)) + ‘ ‘ + RTRIM(ISNULL(Pers_FaxNumber, ”))
AS Pers_FaxFullNumber, RTRIM(ISNULL(Pers_FirstName, ”)) + ‘ ‘ + RTRIM(ISNULL(Pers_LastName, ”)) + ‘[FAX: +’ + RTRIM(ISNULL(Pers_FaxCountryCode, ‘1’)) +
‘(‘ + RTRIM(ISNULL(Pers_FaxAreaCode, ”)) + ‘)’ + RTRIM(ISNULL(Pers_FaxNumber, ”)) + ‘]’ AS Pers_EmailFaxNumber, Pers_Salutation, Pers_LastName,
Pers_FirstName, Pers_MiddleName, Pers_Title, Pers_PhoneCountryCode, Pers_PhoneAreaCode, Pers_PhoneNumber, Pers_FaxCountryCode, Pers_FaxAreaCode,
Pers_FaxNumber, Pers_EmailAddress, Comp_Name, Addr_Address1, Addr_Address2, Addr_Address3, Addr_Address4, Addr_Address5, Addr_City, Addr_State,
Addr_Country, Addr_PostCode, Pers_PrimaryUserId, Pers_SecTerr, Pers_CreatedBy, Pers_ChannelId, Comp_PrimaryUserId, Comp_SecTerr, Comp_CreatedBy, prof_description,
Comp_ChannelId FROM vPersonPE LEFT JOIN Address ON Pers_PrimaryAddressId = Addr_AddressId LEFT JOIN vCompanyPE ON Pers_CompanyId = Comp_CompanyId
Left join Profile on pers_personid = prof_personid
WHERE Pers_Deleted IS NULL
- Then modify your word template to add field #prof_description# field in content anywhere.
- Perform merging from Person summary screen.
After following above steps; the new information we required on template is added properly. However while changing mail merge view we have to make sure that it does not result in multiple rows for single person record. Whatever data is manipulated has to result in single row for every person in Mail merge view.