Answered by:
Insert custom Entity Data to members of a Marketing List (dependent on Accounts)

Question
-
Hi,
we have a new entity (new_entity), weher we need to insert data to. Because it is much data, we want to do it via SQL. Therefore I´ve written a script, but I don´t get the right AccountID. I only get the error
error: The INSERT statement conflicted with the FOREIGN KEY constraint "new_entity_new_account". The conflict occurred in database "DBName", table "dbo.AccountBase", column 'AccountId'.
how can I get the accountID?
BEGIN
DECLARE @ListMemberId uniqueidentifier
DECLARE @CLASSID uniqueidentifier
DECLARE NewEntity_CUR CURSOR FOR
SELECT [ListMemberId]
FROM [ListMemberBase] WHERE [ListId] = (SELECT [ListId] FROM [ListBase] WHERE ListName='myTestList')
OPEN NewEntity_CUR
FETCH NEXT FROM NewEntity_CUR INTO @ListMemberId
-- loop over the cursor NewEntity_CUR
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CLASSID = NEWID()
INSERT INTO [New_entityBase] ([New_entityId]) VALUES(@CLASSID)
INSERT INTO [New_entityExtensionBase] ([New_entityId],[New_Path],[New_AccountId]) VALUES(@CLASSID,'whatever',@ListMemberId)
-- error: The INSERT statement conflicted with the FOREIGN KEY constraint "new_entity_new_account". The conflict occurred in database "DBName", table "dbo.AccountBase", column 'AccountId'.
FETCH NEXT FROM NewEntity_CUR INTO @ListMemberId
END
-- close cursor
CLOSE CLASSIFICATION_CUR
DEALLOCATE CLASSIFICATION_CUR
ENDTHX
Thursday, August 4, 2011 11:44 AM
Answers
-
I found the answer: see this link
http://community.dynamics.com/product/crm/f/117/p/53847/97653.aspx
- Marked as answer by mycapi Wednesday, August 10, 2011 5:59 AM
Wednesday, August 10, 2011 5:59 AM
All replies
-
You are adding ListMemberId into New_AccountId. Because of the relationship between account and your custom entities, these ListMemberId have to be the accountid in account entity.
The problem is your MemberListIds are not matching the accountids in account.
I hope this helps
Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/Thursday, August 4, 2011 1:32 PM -
OK. The MemberListIds is not the accountid. But how can I get the accountid (exixting in the account table) out of a list?Wednesday, August 10, 2011 5:03 AM
-
I found the answer: see this link
http://community.dynamics.com/product/crm/f/117/p/53847/97653.aspx
- Marked as answer by mycapi Wednesday, August 10, 2011 5:59 AM
Wednesday, August 10, 2011 5:59 AM