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.
    Copy
    CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate

    The updated script is as follows:

    Copy
    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.
    Copy
    CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS CurrentDate

    The updated script is as follows:

    Copy
    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)