x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 3 replies
  • Subscribers 74 subscribers
  • Views 324 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Michele Gaw

Phone and Email Data

Posted By

Need some help. I need to understand the purpose of the table CRMEmailPhoneData. We are using 7.2d

I am setting up Sage CRM to sync with our accounting database which is also a Sage product. However, it does not have built in sync. The product is Timberline or as it is now called Sage 300 CRE.

The issue that I am having is with the Phone and Email data. When I create the Phone record, I created the record with the record id in the PhoneLink table of 999 (PLink_RecordID) after the record is created, there is an update procedure to update the PhoneLink table for the correct RecordID by linking it to the Person table through the Timberline record ID which I have added to each table.

My problem is that when I try to update the record ID on the PhoneLink table, I get a message that I am not allowed to update this record ID. I did some research and found that I can get around this by disabling the PhoneLink_InstUpd trigger on the PhoneLink table. If I do this, I will have to not only update the PhoneLink table but I will also have to update the CRMEmailPhoneData table.

I am using SQL stored procedures to insert company and person records from Timberline (along with the address, phone and email records)

I don't know what the purpose of the CRMEmailPhoneData table is.

If I disable the PhoneLink_InstUpd trigger on the PhoneLink table do I loose functionality if a user changes the phone number within the user interface?

Any assistance would be greatly appreciated.

Thanks!

  • 0

    Hi,

    The CRMEmailPhoneData is used for reporting and for a number of views. Basically, it allows for simpler JOIN operations when you need to access email and phone data.

    In general, we wouldn't change the IDs of those records after they're entered - if a user needed to modify a link, we'd usually just do that by adding and removing link records. Is there a particular reason why you can't insert the PhoneLink record with the correct ID?

    I'm not a huge fan of modifying data on the CRM database directly using SQL, since lots of people run into trouble with concurrency and locking; we'd only recommend using one of the CRM APIs rather than trying to manipulate data manually.

    Thanks,

    Rob

  • 0

    Hi Michele,

    After you've finish your updating the company and person records, exec the stored procedure "populate_CRMEmailPhoneData" that will be found in your CRM database. This will rebuild the CRMEmailPhoneData table. (This is also true if you've directly (SQL) added email addresses in the email table).

  • 0

    Rob and Jeff thank you for your replies. I now understand the reason for the table.

    Rob: in response to your question is there a particular reason why you can't insert the PhoneLink record with the correct ID.

    Answer: Probably because I don't know the way to do it. If you could point me to one of the CRM's APIs that would help me accomplish syncing data from another SQL database to CRM. I could look into using one of them. I have worked with an SQL database in the past so this is one of the things that attracted us to Sage CRM. Therefore, I am going to be more inclined to use SQL tools to interact with the database. However, I understand that CRM has its own set of rules and I need to be respectful of those. I am learning as I go along and I do proceed with caution. Thank you for any assistance you can provide.

    Jeff: I think this could work for me so I have checked it as the answer. My question on this is, does the system trigger this stored procedure at any other time? Or is it just there to get everything back in line if you become aware of an issue. Wondering where and when this stored procedure is used.

    Thank you for your assistance.

*Community Hub is the new name for Sage City