This is a follow-up to the article I wrote previously called "Understanding the CRMEmailPhoneData table".

That article explained that the CRMEmailPhoneData is a utility table and that its job is to speed up reports. It holds a row for each company or person with that company or person's Business Email, Business Phone numbers and Fax phone numbers all in one row. The table is maintained by triggers on the PhoneLink and EmailLink tables.

But if you go to the company page or look at a list of people you will see the phone number listed.

Where do these come from?

PersonList

The list of people under a company is the best place to start.

Within the Administration Customization area, it is easy to see that the Company tab group contains a call to the system action Person List.

That system action calls the list block 'PersonList'.

You would need to look directly in the database but the metadata table 'custom_screenobjects' shows that the PersonList is based on the view vListPerson.

select * from Custom_ScreenObjects where cobj_name = 'PersonList'

This system view is defined in metadata and has the following definition

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, vPersonPE.*, Person_Link.*, vCompanyPE.*,
Chan_ChannelId, Chan_Description FROM vPersonPE LEFT JOIN Person_Link ON Pers_PersonId =
PeLi_PersonId LEFT JOIN vCompanyPE ON Pers_CompanyID = Comp_CompanyID
LEFT JOIN Channel ON Pers_ChannelId = Chan_ChannelId WHERE Pers_Deleted IS NULL AND Peli_Deleted IS NULL

The view draws information from other views called vPersonPE and vCompanyPE. Some of the fields from those views are used to derive the field 'pers_phonefullnumber' and it is important to realise that the Phone number information is not held in the person table. Fields such as Pers_PhoneCountryCode, Pers_PhoneAreaCode and Pers_PhoneNumber look like person fields because they have the 'pers' prefix but they are derived in the vPersonPE view.

vPersonPE is a core view and has the definition

SELECT epd_pers.epd_EmailAddress as Pers_EmailAddress,epd_pers.epd_PhoneCountryCode as Pers_PhoneCountryCode,epd_pers.epd_PhoneAreaCode as Pers_PhoneAreaCode,epd_pers.epd_PhoneNumber as Pers_PhoneNumber,epd_pers.epd_FaxCountryCode as Pers_FaxCountryCode,epd_pers.epd_FaxAreaCode as Pers_FaxAreaCode,epd_pers.epd_FaxNumber as Pers_FaxNumber,Person.* FROM Person LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID WHERE Pers_Deleted IS NULL

It is here that we can see that the view pulls the phone and email information for the person record from the CRMEmailPhoneData table.

CompanySummary

When we look at a company summary page and the top content it will display the default phone number for the company.

The CompanySummary system action uses the view vSummaryCompany when the company summary screen is loaded. You won't see this in the meta data but you will see this in the SQL log.

SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, RTRIM(ISNULL(User_FirstName, '')) + ' ' + RTRIM(ISNULL(User_LastName, '')) AS User_Name, vAddress.*, vPersonPE.*, vCompanyPE.*, Users.*, Account.* FROM vCompanyPE LEFT JOIN vAddress ON Comp_PrimaryAddressId = Addr_AddressId LEFT JOIN vPersonPE ON Comp_PrimaryPersonId = Pers_PersonId AND Pers_Deleted IS NULL LEFT JOIN Users ON Comp_PrimaryUserId = User_UserId LEFT JOIN Account ON Comp_PrimaryAccountId = Acc_AccountId WHERE Comp_Deleted IS NULL

You can see that both the vPersonPE and the vCompanyPE core views are referenced.

The company phone number is provided by the vCompanyPE view definition.

SELECT epd_comp.epd_EmailAddress as Comp_EmailAddress,epd_comp.epd_PhoneCountryCode as Comp_PhoneCountryCode,epd_comp.epd_PhoneAreaCode as Comp_PhoneAreaCode,epd_comp.epd_PhoneNumber as Comp_PhoneNumber,epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode,epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode,epd_comp.epd_FaxNumber as Comp_FaxNumber,Company.* FROM Company LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID WHERE Comp_Deleted IS NULL

Which in turn draws the Phone and Email data from the CRMEmailPhoneData table.