Adding current date to a mail merge view

To add a current date field to a mail merge template, you must add it to the entity mail merge view and the entity search view. This example adds the current date field to the Cases entity.

  1. Click <My Profile> | Administration | Customization | Cases | Views.
  2. Click vMailMergeCase and click Change.
  3. Add the following line to the script in View Script and then click Save.
    CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate

    The updated script is as follows:

    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
  4. Click vSearchListCase and click Change.
  5. Add the following line to the script in View Script and then click Save.
    CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate

    The updated script is as follows:

    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
  6. You can customize the field name. For more information, see Modifying language translations. For example:
    • Caption Code: CurrentDate
    • Caption Family: ColNames
    • Caption Family Type: Tags
    • UK Translation: Current Date (DD MM YYYY)
    • US Translation: Current Date (MM DD YYYY)