locked
Importing Data through a Script RRS feed

  • Question

  • I am building a script in classic ASP to transfer customer data from our home-grown CRM system into CRM.  We have created all of our current accounts from within CRM and manually transferred their corresponding GUIDs to our database. 

     

    The ASP file is inserting the contact records (Approximately 20,000 Records) into the contactbase table and assigning the contacts to their corresponding Accounts. When you view the contact in CRM they are related to the correct account.  The problem is that when I try and insert the address into the CustomerAddressBase table, the address is not visible from within the Contact record in CRM but it is in the physical SQL table when you perform a query.  I am inserting the required two rows of data into the entity, and the ParentID matches the id of the contact, but it is not the same type of format as GUIDs that CRM generates.

     

    Here are some sample GUIDs generated from CRM:

    Record # 1  Created by the Test User Account within CRM

    {4DF9C19C-D44C-DC11-B6D7-00137252394F}  ContactID /ParentID

    {4EF9C19C-D44C-DC11-B6D7-00137252394F}  CustomerAddressID  AddressNumber = 1

    {4FF9C19C-D44C-DC11-B6D7-00137252394F}  CustomerAddressID  AddressNumber = 2

     

    Record # 2  Created by the My User Account within CRM

    {44FC8917-7EBC-DB11-BF19-00137252394F}  OwningBusinessUnitID

    {9AB9FF84-73E4-DB11-B49F-00137252394F}  OwningUserID

    {58B44876-AFD1-DB11-B49F-00137252394F}  AccountID the contact is assigned to

    {C843288D-AFD1-DB11-B49F-00137252394F}   ContactID /ParentID

    {C943288D-AFD1-DB11-B49F-00137252394F}  CustomerAddressID  AddressNumber = 1

    {CA43288D-AFD1-DB11-B49F-00137252394F}  CustomerAddressID  AddressNumber = 2

     

    Record # 3  Created by my script

    {C4E4A882-9383-4310-9F2E-9866B5963631}  ContactID /ParentID

    {A9D2AA24-6D13-45A9-8257-6F9129AB5771}  CustomerAddressID  AddressNumber = 1

    {3E7FB343-ADBE-47BF-9397-193C441C8002}  CustomerAddressID  AddressNumber = 2

     

     

    CRM is obviously not creating random ids.  This is the only inconsistency I can find when comparing the records that I created through my script and the records created from CRM.  I would ask if this non-random id is required, but I think I have already answered that question.  So I guess the question is.  How do I generate the CustomerAddressID so that it meets CRM's requirements?  Are there triggers that can be called to make this happen automatically?  I also realize the last three sections of the GUIDs for the ContactID come from the owning business unit, and owning user.  So I will need to create the GUIDs for the Contacts based on the recommendations you provide as well, which is not a problem.

     

    Here is a sample of my insert statement:

    Code Snippet

    "Insert into ContactBase (ContactID, OwningBusinessUnit, OwningUser, AccountID, LastName, FirstName, FullName, StateCode, StatusCode, DeletionStateCode,ParticipatesInWorkflow, IsPrivate, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, DoNotSendMM, Merged)  Values (NewID(), '" & OwningBusinessUnit & "','" & OwningUser & "','" & AccountID & "','" & last_name & "','" & first_name & "','" & first_name & " " & last_name & "'" & ",0,1, 0, 0, 0, GetDate(),'" & OwningUser & "',DATEADD(S," & Counter & ",GetDate()) ,'" & OwningUser & "', 0, 0)"

     

     

    I am doing the DATEADD so that I can differentiate the last record my script inserted because the time of the insert was identical when my scripts where looping through the data and I could not locate the last inserted record any other way to my knowledge.

     

    Is there a procedure or function I can call in place of NewID() that I pass some parameters so it generates the GUID correctly such as NewGUID = CreateID(AccountID, OwningUserID, OwningBusinessUnitID, OrganizationID)Or is there some other method I can use to try and accomplish this task?  Also, there is more data to transfer that I am quite sure will require a similar operation to maintain the integrity of the data.

     

    Saturday, August 18, 2007 12:52 AM

Answers

All replies