Understanding the CRMEmailPhoneData table

Hints, Tips and Tricks

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

Understanding the CRMEmailPhoneData table

  • Comments 3
  • Likes

If you have spent time looking at the way in which views are constructed in Sage CRM then you may have found the CRMEmailPhoneData table referenced and wondered what is the table's purpose.

I asked a colleague in our development team for some help in explaining the table and the following information is based on their reply to me.

The key facts about the table are

  • CRMEmailPhoneData is a utility table introduced to speed up reports. It is used in views only.
  • It should hold 1 row for each company or person with that company or persons Business Email, Business Phone numbers and Fax phone numbers all in one row.
  • If it is company or person is indicated by the epd_EntityId (usually 5 or 13)
  • The company or person id is in the epd_RecordId field
  • It is entirely maintained by triggers on the PhoneLink and EmailLink table, it is never updated directly.
  • It is really an internal table and we would not advise anyone trying to update it or read it directly. Definitely do not update it!

This then can be expanded on to understand how the table CRMEmailPhoneData is used in the following circumstances

1. How CRMEmailPhoneData  is referenced during the Insert of a Company and or Person with phone and email data

When you insert a company with phone and email data, this will insert the phone records and the phone link records, plus the Email record and the emaillink records. This will force the insert triggers on the link tables to fire. The triggers are clever to insert the CRMEmailPhoneData record for the company if it doesn’t exist or they will update it if it is already there so your end result will be 1 row in the CRMEmailPhoneData table with entityid = 5 (company) recordid = your new company id, plus the email, phone and fax info in the corresponding fields.

2. How CRMEmailPhoneData is used in phone email maintenance

Whenever you edit a Business phone, Fax phone or Business email, the trigger on the link table will update the corresponding row in the CRMEmailPhoneData table.

3. How phone or email type e.g. Business affects things

Type is important – as mentioned above. Only Business emails, Business phone and Fax phone are replicated in the CRMEmailPhoneData table. Changes to other types of records will be ignored by the triggers. E.G if you update the Sales phone number there will be no change in the CRMEmailPhoneData table.

4. How it is referenced in queries/views

For examples of how it is used in views have a look at vCompanyPE . Looks a bit like this (note the “5” is the id of company table in custom_tables). It joins via the epd_EntityId and epd_RecordId fields.

[code language="sql"]
CREATE VIEW vCompanyPE AS
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]

We would very strongly NOT recommend using the CRMEmailPhoneData table directly in a query - you should always use the views vCompanyPE or vPersonPE to get the business phone and email data. Or if you must, then create your own view that joins to it.

5. How consistency is managed when web services (SOAP & REST) calls are made.

The table is entirely controlled by the DB triggers on the link tables so it shouldn’t matter how you update your data, you shouldn’t need to worry about it, the triggers will always take care of it, regardless of whether you are editing in UI, webservices, asp page whatever.

Comments
  • Jeff...

    Thank you for this article.  I have a question regarding what the system is using to actually populate the Primary E-mail on the "People" tab of the Company record.  When I search through the data, it appears that this is being pulled from the system action peoplelist. Is there a way that I can see what makes up the peoplelist?  On the list definition (PersonList), it looks like it is pulling the email address from pers_emailaddress which is on the person table.  However, the email is really stored in the email table isn't it?

    When you enter the email address in the system, does it put it in the person table AND the email table.

    Having trouble relating what I am seeing on the screen to where the data is actually stored in the database.

    Thank you for your assistance...

  • Please see community.sagecrm.com/.../why-do-phone-numbers-appear-in-the-company-and-person-screens-when-they-are-stored-in-the-phone-table.aspx

  • Thanks Jeff!  I will take a look...want to make sure I have a handle on this.