Forgot to add, I know you can mark the old emaillink records as deleted and insert new records.

Hi,

From the below post we understood that, you have imported the email addresses of person and now you are trying to update emaillink ELink_RecordID column with right person id. Well, is it possible for you to provide us the update query which you are trying. So that we can have a better look on the this.

Regards,

Dinesh

It was a handful of records, so the queries were pretty simple:

UPDATE EmailLink

SET ELink_RecordID = [a]

WHERE ELink_LinkID = [b] ;

UPDATE EmailLink

SET ELink_RecordID = [c]

WHERE ELink_LinkID = [d] ;

What was the exact error in SQL that was being presented when the UPDATE failed?

Msg 50000, Level 16, State 1, Procedure EmailLink_InstUpd, Line 13

You are not allowed to modify any of the following columns: ELink_EntityID, ELink_RecordID

So basically, it seems like my only option is to delete the old email links, is that correct?

It seems ridiculous that you can't change the entity an email address is linked to.

I just ran your basic Update statement against my v7.2 install and I had no issues. Could there be an issue with your procedure?

Have you tried to run a single update query from SQL Management Studio to fix a single record to see if that goes through?

- Matt -

Hi,

I've just tried this out on v7.1j and have reproduced the issue. If you're looking for help with a support query, I'd strongly recommend always including the following:

1: The version of CRM that you're on.

2: The steps you're taking to reproduce (in this case the SQL).

3: The observed results (i.e., the error text / log files).

I guess the reason why some of the guys on here can't reproduce this is because we've made changes around phone numbers and email addresses so that they can be normalised. This wouldn't be present in all versions of CRM. Generally speaking, it's not a great idea to go changing this sort of thing in the database, as it's easy to make mistakes.

Anyways, if you're committed to doing this (which I don't recommend - I'd reimport the data unless you're totally sure that your script will work), you can make it work by disabling the EmailLink_InstUpd trigger on the EmailLink table. This trigger is intended to update the CRMEmailPhoneData table on changes to EmailLink. Keep in mind that you will also need to make appropriate changes to the CRMEmailPhoneData table to reflect the changes you're making on EmailLink. Since you have a list of record IDs on EmailLink already, you should be able to make it work by using Elink_EmailId to pull the appropriate Email record, then make your changes on CRMEmailPhoneData.

Hope this helps,

Rob

Rob -

You're right, I omitted crucial info in the original request, and I should know better. (BTW, version is v7.1g.)

As far as doing this via the database, I'm open to other methods, but I can't think of any to reassign email addresses in bulk.

When it becomes an issue is when we're having a data hygiene push, and there are emails clearly assigned to the wrong person or we need to link an email address to the most recent or most valid person record. A typical use case would be:

There is an existing record for Joe Attorney, with opportunities, communications and cases already in place against the person and company records. The email address we have for Joe Attorney is [email protected], and we know it's his assistant because we have a separate record for the assistant using the same email address. A sales rep enters a new record for Joe Attorney - they never remember to check for duplicates - with the address [email protected].

So now we have 2 person records for Joe Attorney: one that has an address we want, and the other has everything else. Then we go through an annual merge/purge process and find a few hundred other people like Joe Attorney.

We can't just delete the email addresses and start over, because some of them are shared by other entities. We can't do a mass update. And we don't want to create a whole new batch of duplicate email addresses.

So what are our options?