locked
Import data form manual (vistaDB) database to CRM dynamics 2011 RRS feed

  • Question

  • Hi,

    I was working in VistaDB for maintaining my customers. I have created manual tables there and used it. Now I want to import VistaDB data to CRM dynamics 2011. It seems all the primary key in dynamics are uniqueidentifier type and can't be edited. My problem is I used my own primary key to map with foreign key with another table. I used datamapping to import my data. I couldn't put any value in the forign key of the table. because I don't know the primary key of the other table.

    So now I lost all the links between the tables. How can i go for it? Is there any other way to import it?

    Thanks in advance.

    Tuesday, August 9, 2011 9:55 AM

Answers

  • If Scribe is too expensive, make a dummy Attribute in CRM called VistaKey or something like that for every entity you want to load.  Then push the primary key from VistaDB to that field when you're loading your entities.  Then, as you're using CRM 2011's data loading functionality, make sure you set that as the unique identifier when building your relationships in CRM.  That should rebuild all your relationships in CRM just fine.

    Once you're done, and confident that all your data is properly related in CRM, then you can just delete the VistaKey Attribute from all your entities and have a nice, clean CRM 2011 database, with relationships intact.


    The postings on this site are solely my own and do not represent or constitute Hitachi Consulting's positions, views, strategies or opinions.
    • Marked as answer by jeyan Friday, August 12, 2011 8:35 AM
    Tuesday, August 9, 2011 1:11 PM
  • Hi as I said before if you have name of the primary entity in the child entity. It will be easy. When you try to map this name field with the foreignkey(lookup) field of crm you can choose which fields from both (primary and child entity) will be used as linking field.

    I have attached some screen sheets. in the following screen sheet I am linking parentCustomer from my csv file tp parentcompany lookup field in CRM.

    As you can see there is a there is a little button between these 2 field. If you click on it. It will let you choose the link fields from account and contact entity.

    But you can do is add a custom field as uniqueId in primary and child entity. publish the changes.

    When you try to import the child entity (foreign key entity) add a parent entity name to it. when you map the fields, map that field to foreign entityname field on the child entity and chose uniqueid fields from parent and child entity as linking field.

     

    I hope this help.

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Wednesday, August 10, 2011 12:40 PM
    • Marked as answer by jeyan Friday, August 12, 2011 8:34 AM
    Wednesday, August 10, 2011 12:40 PM

All replies

  • You could always use a 3rd party tool like Scribe;

    Have used this before and it's the best data migration tool, very flexible. It has an expensive price tag though..that's the catch.

    You could always try with the 30day trail so see if it fits your needs. There's a cap on 100 records in the demo.

    Normally if this VistaDB is based on a variant of SQL Server, you should also be able to connect to it via Scribe.

    Tuesday, August 9, 2011 12:58 PM
  • If Scribe is too expensive, make a dummy Attribute in CRM called VistaKey or something like that for every entity you want to load.  Then push the primary key from VistaDB to that field when you're loading your entities.  Then, as you're using CRM 2011's data loading functionality, make sure you set that as the unique identifier when building your relationships in CRM.  That should rebuild all your relationships in CRM just fine.

    Once you're done, and confident that all your data is properly related in CRM, then you can just delete the VistaKey Attribute from all your entities and have a nice, clean CRM 2011 database, with relationships intact.


    The postings on this site are solely my own and do not represent or constitute Hitachi Consulting's positions, views, strategies or opinions.
    • Marked as answer by jeyan Friday, August 12, 2011 8:35 AM
    Tuesday, August 9, 2011 1:11 PM
  • CRM won't use  Id field to resolve the lookup field in data imports. It always use name field.

    for e.g say table account and table contact

    Table account data

    id name address

    1  ABC   test street

    Table contact data

    id   name     address      company

    c1  j smith   test street   ABC

    you just need company field(which is name field in account) to link to account entity not Id field

    Thats how data imports work. you don't need ids to resove the lookup.

     

    I hope this helps.

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Tuesday, August 9, 2011 1:15 PM
    Tuesday, August 9, 2011 1:14 PM
  • No, CRM will use any virtually field you ask it to when you want to link fields.  The only caveat is it must be unique.  when setting lookup fields, CRM will have a dropdown that defaults to the Name field of the other entity, but you can change that.  Hence why I suggested a new Attribute to drop the old ID into.  That way you can guarantee uniqueness, as Name is generally a terrible Or at least unreliable) field to check for uniqueness.
    The postings on this site are solely my own and do not represent or constitute Hitachi Consulting's positions, views, strategies or opinions.
    Tuesday, August 9, 2011 2:55 PM
  • Hi

    Wayne is right. I did not know that. I was suggesting what I used to do in CRM 4.0.

     

    Good luck

     

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    Wednesday, August 10, 2011 12:25 AM
  • Dear Wayne Walton,

    Thanks for the reply. I am little bit confused about the method you suggested. 

    I can create dummy field (xKey) in CRM and mapped the data of primary key of the VistaDB.

    How can I set the dummy field (xKey) as unique identifier (requires 16 bit ?)?

    How can I refer from a lookup field from another table? Do I only need to put primary key of the VistaDB as the foriegn of other table?

    Could you please explain me a little bit?

    Is there any other way (by directly accessing the database of CRM, can we import the data from VistaDB to CRM DB?)

    Thanks,

    Wednesday, August 10, 2011 8:09 AM
  • Hi as I said before if you have name of the primary entity in the child entity. It will be easy. When you try to map this name field with the foreignkey(lookup) field of crm you can choose which fields from both (primary and child entity) will be used as linking field.

    I have attached some screen sheets. in the following screen sheet I am linking parentCustomer from my csv file tp parentcompany lookup field in CRM.

    As you can see there is a there is a little button between these 2 field. If you click on it. It will let you choose the link fields from account and contact entity.

    But you can do is add a custom field as uniqueId in primary and child entity. publish the changes.

    When you try to import the child entity (foreign key entity) add a parent entity name to it. when you map the fields, map that field to foreign entityname field on the child entity and chose uniqueid fields from parent and child entity as linking field.

     

    I hope this help.

     


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Amreek Singh Wednesday, August 10, 2011 12:40 PM
    • Marked as answer by jeyan Friday, August 12, 2011 8:34 AM
    Wednesday, August 10, 2011 12:40 PM
  • Thanks for both Wayne and Amreek. It was very helpful and sorted my problem.
    Friday, August 12, 2011 8:37 AM