locked
Insert custom Entity Data to members of a Marketing List (dependent on Accounts) RRS feed

  • 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
    END

    THX

    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