{"id":323,"date":"2011-05-27T07:31:00","date_gmt":"2011-05-27T07:31:00","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=323"},"modified":"2011-05-27T07:31:00","modified_gmt":"2011-05-27T07:31:00","slug":"including-phone-numbers-from-phone-table-in-standard-document-merge","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/","title":{"rendered":"Including phone numbers from phone table in standard Document Merge"},"content":{"rendered":"

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. <\/p>\n

<\/p>\n

There is a view under Person<\/strong> entity named vMailMerge<\/strong>. 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. <\/p>\n

<\/p>\n

<\/p>\nOriginal View Script<\/legend>\n

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 <\/em><\/fieldset>\n

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.<\/p>\n

<\/p>\nModified View Script<\/legend>\n

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<\/span> <\/em><\/fieldset>\n

<\/p>\n

Now you can add the field Business_Phone_Number<\/strong> in the template to be merged and get the value when the template s merged. <\/p>\n

<\/p>\n

Note:<\/strong> 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. <\/p>\n

<\/p>\n

If you find this content useful, please drop us an email at crm@greytrix.com<\/a>. <\/b><\/marquee><\/p>\n","protected":false},"excerpt":{"rendered":"

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\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[332],"tags":[993,1503,1552,2030],"class_list":["post-323","post","type-post","status-publish","format-standard","hentry","category-sage-crm","tag-document","tag-library","tag-mail-merge","tag-sage-crm-7-0"],"yoast_head":"\nIncluding phone numbers from phone table in standard Document Merge - Sage CRM \u2013 Tips, Tricks and Components<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Including phone numbers from phone table in standard Document Merge - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"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\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2011-05-27T07:31:00+00:00\" \/>\n<meta name=\"author\" content=\"greysagecrm\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greysagecrm\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/\",\"name\":\"Including phone numbers from phone table in standard Document Merge - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"datePublished\":\"2011-05-27T07:31:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Including phone numbers from phone table in standard Document Merge\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\",\"name\":\"Sage CRM \u2013 Tips, Tricks and Components\",\"description\":\"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\",\"name\":\"greysagecrm\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"greysagecrm\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Including phone numbers from phone table in standard Document Merge - Sage CRM \u2013 Tips, Tricks and Components","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/","og_locale":"en_US","og_type":"article","og_title":"Including phone numbers from phone table in standard Document Merge - Sage CRM \u2013 Tips, Tricks and Components","og_description":"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\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2011-05-27T07:31:00+00:00","author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/","name":"Including phone numbers from phone table in standard Document Merge - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"datePublished":"2011-05-27T07:31:00+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/05\/27\/including-phone-numbers-from-phone-table-in-standard-document-merge\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"Including phone numbers from phone table in standard Document Merge"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/","name":"Sage CRM \u2013 Tips, Tricks and Components","description":"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f","name":"greysagecrm","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"greysagecrm"},"url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/comments?post=323"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/323\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}