Why do Phone numbers appear in the Company and Person screens when they are stored in the Phone table?

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Why do Phone numbers appear in the Company and Person screens when they are stored in the Phone table?

  • Comments 1
  • Likes

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 it's job is to speed up reports. It holds a row for each company or person with that company or persons Business Email, Business Phone numbers and Fax phone numbers all in one row and the table is maintained by triggers on the PhoneLink and EmailLink table.

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 meta data 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 meta data and has the following definition

[code language="sql"]
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
[/code]

The view draws information 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 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

[code language ="sql"]
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
[/code]

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.

[code language="sql"]
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
[/code]

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.

[code language="sql"]
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
[/code]

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

Comments
  • Thanks Jeff!  Knowing how to find which view the action is associated with will help tremendously!