Summary:

The following shows the user how to add a current date field to a mail merge template.

The new field will be added into two different views so that it will show up for a group and an individual mail merge.

For this example the current date field will be added to the Case entity, but can be added to any entity that uses mail merge.

To add a current date field to a mail merge template:

1. Go into Administration, Customisation, Cases and Views.
2. Modify the following views vMailMergeCases and vSearchListCase.
    The following line needs to be added into both views:

CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate

3. Once the views are changed they should look similar to this:

VMailMerge:

CREATE VIEW vMailMergeCase AS SELECT 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, Case_CaseId, Comp_CompanyId, Pers_PersonId, Pers_Salutation, Pers_LastName, Pers_FirstName, Pers_MiddleName, Pers_Title, Pers_PhoneCountryCode, Pers_PhoneAreaCode, Pers_PhoneNumber, Pers_FaxCountryCode, Pers_FaxAreaCode, Pers_FaxNumber, Pers_EmailAddress, Pers_PrimaryUserId, Pers_SecTerr, Pers_CreatedBy, Pers_ChannelId, Comp_Name, Comp_PrimaryUserId, Comp_Secterr, Comp_CreatedBy, Comp_ChannelId, Addr_Address1, Addr_Address2, Addr_Address3, Addr_Address4, Addr_Address5, Addr_City, Addr_State, Addr_Country, Addr_PostCode, Case_AssignedUserId, Case_SecTerr, Case_CreatedBy, Case_ChannelId, CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate FROM Cases LEFT JOIN vPersonPE ON Case_PrimaryPersonId = Pers_PersonId LEFT JOIN vCompanyPE ON Case_PrimaryCompanyId = Comp_CompanyId LEFT JOIN Address ON Comp_PrimaryAddressId = Addr_AddressId WHERE Case_Deleted IS NULL

vSearchListCase:

CREATE VIEW vSearchListCase AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, 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, vPersonPE.*, Cases.*, vCompanyPE.*, Address.*, CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate FROM Cases LEFT JOIN vCompanyPE ON Case_PrimaryCompanyId = Comp_CompanyId LEFT JOIN vPersonPE ON Case_PrimaryPersonId = Pers_PersonId LEFT JOIN Address ON Pers_PrimaryAddressId = Addr_AddressId WHERE Case_Deleted IS NULL


4. Customising the name of this field can be done through a translation.

For example:

Caption Code: CurrentDate
Caption Family: ColNames
Caption Family Type: Tags

UK Translation: Current Date (DD MM YYYY)
US Translation: Current Date (DD MM YYYY)

5. To add the current date field to a HTML template created with Sage CRM, look for the field in the Insert Sage CRM Field Box when Create a new Mail Merge Template is selected.

6. To add the current date field to a Word document template, just include «CurrentDate» or {MERGEFIELD CurrentDate} in the document, and then upload it to Sage CRM.