Marketing List History

Greetings guys,

Last week I worked on a very strange requirement to implement some kind of a marketing list auditing mechanism. The customer wants to be able to track which user added particular contact in a given marketing list.
Since the list <–> contact relationship is many-to-many and therefore the relations are stored in a third table (called ListMemberBase in this case), there is no event available to trigger a workflow or do something out of the box. Technically nor the list is updated neither the contact. Since I didn’t have much time to do this I decided to use database trigger to achieve the goal. Yes it is unsupported but it works very fast and nice so I decided to share it.
I first created a custom field of type ntext called Marketing List History (new_marketinglisthistory) on the Contact Entity. Added this to the contact form.
Then created the following trigger:

USE [XXX_MSCRM]
GO
/****** Object: Trigger [dbo].[trg_ListHistory] Script Date: 09/28/2010 17:09:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_ListHistory]
ON [dbo].[ListMemberBase]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
if (select EntityType from inserted) = 2
begin
if (select New_MarketingListHistory from Contact, inserted where Contact.contactid=inserted.entityid) is null
begin
UPDATE Contact
SET New_MarketingListHistory = (select l.createdbyname+' --> '+listname from listmember l inner join list m on l.listid=m.listid where l.ListMemberId=inserted.ListMemberId)
FROM inserted
WHERE Contact.contactid = inserted.entityid
end
else
begin
UPDATE Contact
SET New_MarketingListHistory = (select l.createdbyname+' --> '+listname + char(13) from listmember l inner join list m on l.listid=m.listid where l.ListMemberId=inserted.ListMemberId) + (select New_MarketingListHistory from Contact where Contact.contactid=inserted.entityid)
FROM inserted
WHERE Contact.contactid = inserted.entityid
end
end
END

Done! Here is the result:


The last addition in a given marketing list goes on top of the history.
You can also put this column in the marketing list view – all members to look like this:


Regards!
Rinshwind