Including phone numbers from phone table in standard Document Merge

By | May 27, 2011
0 Comment

Letter merge is a part of daily activity followed in sage CRM implemented in several financial and service industries. Apart from emails being sent out, these people always keep in touch with clients by posting them the welcome letters, account Kits, statements, cards etc. These letters can be very easily merged in Sage CRM using Document Merge functionality on Person and Company Entity. Now have you ever wondered how exactly Document merge pulls up all the data for person at the time of merging.

There is a view under Person entity named vMailMerge. Whatever fields this view presents can be included in the word merge templates and merged there on. Below is the original view from sage CRM version 6.2.

Original View Script

CREATE VIEW vMailMerge AS 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, Comp_ChannelId
FROM Person LEFT JOIN Address
ON Pers_PrimaryAddressId = Addr_AddressId LEFT JOIN Company
ON Pers_CompanyId = Comp_CompanyId
WHERE Pers_Deleted IS NULL

Now as you can see that this view already takes care of the phone numbers using the fields from Person table for fax and the default business number. Now what I want here is all the phone numbers under Phone/Email tab of that person to be available for merging in letter. So the only way I can achieve it by joining above query with phone table based on phone type and person id. Now while changing the view above we have to take care that the view should not generate more than one row for single person. You know that there may be multiple entries in phone table for that person so we have to combine them all in single row. Below is how I changed the above view to bring Business and mobile phone numbers separately from the phone table.

Modified View Script

CREATE VIEW vMailMerge AS 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, Comp_ChannelId,
ltrim(rtrim(isnull(p1.phon_countrycode,”))+’ ‘+rtrim(isnull(p1.phon_areacode,”))+’ ‘+rtrim(isnull(p1.phon_number,”))) as Business_Phone_Number,
ltrim(rtrim(isnull(p2.phon_countrycode,”))+’ ‘+rtrim(isnull(p2.phon_areacode,”))+’ ‘+rtrim(isnull(p2.phon_number,”))) as Mobile_Phone_Number,
ltrim(rtrim(isnull(p3.phon_countrycode,”))+’ ‘+rtrim(isnull(p3.phon_areacode,”))+’ ‘+rtrim(isnull(p3.phon_number,”))) as Home_Phone_Number
FROM Person p LEFT JOIN Address
ON Pers_PrimaryAddressId = Addr_AddressId LEFT JOIN Company
ON Pers_CompanyId = Comp_CompanyId
Left join phone p1 on p1.Phon_Personid = p.pers_personid
and p1.phon_type = ‘Business’ and p1.phon_phoneid = (select max(phon_phoneid) from phone c1 where phon_type = ‘Business’ and phon_personid = p.pers_personid)
Left join phone p2 on p2.Phon_Personid = p.pers_personid
and p2.phon_type = ‘Mobile’ and p2.phon_phoneid = (select max(phon_phoneid) from phone c2 where phon_type = ‘Mobile’ and phon_personid = p.pers_personid)
Left join phone p3 on p3.Phon_Personid = p.pers_personid
and p3.phon_type = ‘Home’ and p3.phon_phoneid = (select max(phon_phoneid) from phone c3 where phon_type = ‘Home’ and phon_personid = p.pers_personid)
WHERE Pers_Deleted IS NULL

Now you can add the field Business_Phone_Number in the template to be merged and get the value when the template s merged.

Note: Sage CRM version 7.1 has the different structure for maintaining phone numbers i.e. using link table. So to use the above view in version 7.1 you have to join on phone link table also to get the actual numbers.

If you find this content useful, please drop us an email at crm@greytrix.com.

Related Posts