How to add the Mobile Phone and Private Phone fields to the views being used by Find screens, Reports and Groups

By | April 20, 2013

This blog explains how one can fetch different categories of phone numbers and Email addresses tagged against particular person to display in Report, Find Screen and groups in CRM. First let me explain the structure of Phone and Email entities in CRM. Refer the below given details.

After understanding the relationship between entities specified in the above table, follow below steps to proceed further.
 1. Login to CRM
2. Go to Administration | Customization | Person
3. Click on Views tab.
4. Click on New button. 
5. Fill the required fields
6. For e.g.
A) ViewName    :           vSearchListPerson
B) Checked       :           Report View
C) Checked       :           Group View
D) Checked      :           Keyword Search View
E) View Script  :           contains the actual logic of showing/displaying required data on click of find   button.
SELECT RTRIM(ISNULL(Pers_FirstName, ”)) + ‘ ‘ + RTRIM(ISNULL(Pers_LastName, ”)) AS Pers_FullName,    epd_pers.epd_PhoneCountryCode as Pers_PhoneCountryCode,  epd_pers.epd_PhoneAreaCode as Pers_PhoneAreaCode,  epd_pers.epd_PhoneNumber as Pers_PhoneNumber,   epd_pers.epd_PhoneFullNumber AS Pers_PhoneFullNumber,  epd_pers.epd_FaxCountryCode as Pers_FaxCountryCode,  epd_pers.epd_FaxAreaCode as Pers_FaxAreaCode,  epd_pers.epd_FaxNumber as Pers_FaxNumber,   epd_pers.epd_FaxFullNumber AS Pers_FaxFullNumber,   Purchase.purc_Entity, Purchase.purc_UniqueID, Purchase.purc_applicanttype, Purchase.purc_appid, Purchase.purc_lanno, Purchase.purc_amount, Purchase.Purc_PersonId, Person.*, Comp_CompanyId, Comp_SecTerr, Comp_CreatedBy, Comp_ChannelId, Comp_PrimaryUserId,  Address.*,purc_PurchaseID,purc_PurchaseID As PurchaseId,NULL as  Comp_Name,  (select Top 1 phon_number from Phone (nolock)  inner join Address on phon_UniqueAddressid=addr_UniqueAddressid  inner join PhoneLink on PLink_PhoneId=Phon_PhoneId  and PLink_RecordID =Purc_PersonId and PLink_Type=’Mobile’  and PLink_EntityID=13 and plink_UniqueAddressid=addr_UniqueAddressid order by phon_Preferred desc) as Phon_Number,  (select Top 1 Emai_EmailAddress from email (nolock)  inner join Address on emai_UniqueAddressid=addr_UniqueAddressid  inner join EmailLink on ELink_EmailId=Emai_EmailId  and ELink_RecordID =Purc_PersonId  and ELink_EntityID=13 and ELink_UniqueAddressid=addr_UniqueAddressid order by emai_Preferred desc) as Pers_EmailAddress  FROM Purchase  JOIN Person  on purc_PersonId = Pers_PersonId  LEFT JOIN CRMEmailPhoneData epd_pers  ON epd_pers.epd_EntityID = 13  AND epd_pers.epd_RecordID = Pers_PersonID  LEFT JOIN Address  ON Pers_PrimaryAddressId = Addr_AddressId AND Addr_Deleted IS NULL  LEFT JOIN Company ON Pers_CompanyId = Comp_CompanyId WHERE Pers_Deleted IS NULL
7. Click on Save button
8. The part shown in bold in the above view explains the logic of populating mobile number in person search screen.
9. Now, go to Administration | Customization | Person
10. Click on Screen tab, select vSearchListPersonscreen
11. Click on “Field” drop down select “Mobile Number”
12. Then click on Add | Update | Save