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 person's Business Email, Business Phone numbers and Fax phone numbers all in one row.
• If it is a 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 the company table in custom_tables). It joins via the epd_EntityId and epd_RecordId fields.

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

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, web services, asp page whatever.