locked
Dynamics CRM 2011 Custom Entities: creating model that matches my SQL Server foreign and primary key relationships RRS feed

  • Question

  • I have a nice SQL Server DB setup with 2 tables with one to many relationship. Something like this:

    TYPE

    ID Type

    1 mammal

    2 fish

    3 insect

    PETS

    ID Name Foreign Key

    1 Paris 1 (mammal)

    2 Michael 2 (fish)

    3 Mardy 1 (mammal)

    How on earth do i recreate this in CRM? I know CRM likes to create its own primary key GUIDs and relalationship fields. I want to keep the integrity of my tabular relationships particularly due to the fact that i will be importing large amounts of data (1million + records) via a script on a regular basis rather than creating the records and relationships via the crm gui. Thanks.

    Wednesday, October 17, 2012 11:45 AM

Answers

  • Hi,

    Dynamics CRM does not allow you to change the way in which it creates tables or forms relationships between them via Primary/Foreign Keys. You must use the user interface to create entities and relationships - and then Dynamics CRM decided how to best store the data in the database. 

    A Couple of points to add to the above.

    1) Configure relationships between entities using the 1:N, N:1 or N:N relationships. These use uniqueidentifiers as the primary key where the id field is always <entityname>id. There really isn't much point in adding your additional keys unless they mean something outside of CRM - i.e. identifiers that are used by another system.

    2) If you are importing many records - you cannot directly insert data into the database as this is unsupported. You must use the SDK webservices, bulk import wizard or thirdparty tool such as Scribe.

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Wednesday, October 17, 2012 8:54 PM
    Answerer
  • I believe the answer is to 

    1) add an external ID to both entities (my IDs)

    2) create new records in both tables - let CRM via SDK auto generate GUIDs

    3) get the GUIDs of the TYPE records with a query referencin the external IDs (either programatically or via the underlying CRM tables in SQL Server)

    4) creating a new entity reference based on the relevant returned GUID


    Wednesday, October 17, 2012 4:54 PM

All replies

  • I believe the answer is to 

    1) add an external ID to both entities (my IDs)

    2) create new records in both tables - let CRM via SDK auto generate GUIDs

    3) get the GUIDs of the TYPE records with a query referencin the external IDs (either programatically or via the underlying CRM tables in SQL Server)

    4) creating a new entity reference based on the relevant returned GUID


    Wednesday, October 17, 2012 4:54 PM
  • Hi

    I belive you should create the two entities and the relation between them, the Dyn. Crm way - where Dyn. Crm will create the ID´s and the relations. And then I would change my import scripts to support this.

    If you start too make up you own relations, you miss all the features Dyn. Crm gives you out of the box.

    If you cannot live with the way Dyn. Crm does it´s relation, you should consider if Dyn. Crm is the application suited for your requirements.


    --- Best regards Henrik Skydtsgaard

    Wednesday, October 17, 2012 6:12 PM
  • Hi,

    Dynamics CRM does not allow you to change the way in which it creates tables or forms relationships between them via Primary/Foreign Keys. You must use the user interface to create entities and relationships - and then Dynamics CRM decided how to best store the data in the database. 

    A Couple of points to add to the above.

    1) Configure relationships between entities using the 1:N, N:1 or N:N relationships. These use uniqueidentifiers as the primary key where the id field is always <entityname>id. There really isn't much point in adding your additional keys unless they mean something outside of CRM - i.e. identifiers that are used by another system.

    2) If you are importing many records - you cannot directly insert data into the database as this is unsupported. You must use the SDK webservices, bulk import wizard or thirdparty tool such as Scribe.

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Wednesday, October 17, 2012 8:54 PM
    Answerer
  • Fair point - I am considering referencing my 2 (real world) related tables as an external datasource that is linked to my contact records via a membership id. The learning curve refuses to flatten out!
    Thursday, October 18, 2012 8:18 AM
  • That is what i intend to do if i go down this path: create the entities and relationships using the gui then bringing in the data referencing these GUIDS and namespaces. I do still need my additional keys as these are what hold  the relationships between my 2 source tables that live in SQL Server (imported from legacy system) that i need to recreate. I see no way around doing this using SDK webservices and creating the associated relationships as i mentioned above. Thanks Scott.

    Am i right in thinking to associate relationships between 2 records you just need to reference an EXISTING relationship? 

    // Create an object that defines the relationship between the contact and account. Relationship relationship = new Relationship("account_primary_contact"); //from MSDN

    ie) 'account_primary_contact' is an existing 1:N relationship namespace already defined in system?

    Thursday, October 18, 2012 8:26 AM
  • Hi,

    Yes that's right, you configure and publish your relationships in the CRM UI and then you can reference them by their schema name.

    N:N work slightly different in that there is a 'join entity' that is created behind the scenes - to relate over a N:N relationship you must use the Associate Message, but for 1:N you can alternatively just set the foreign key on the 'child' record when you create it, setting it to an 'EntityReference'.

    http://msdn.microsoft.com/en-us/library/gg309247.aspx

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, October 18, 2012 10:28 AM
    Answerer
  • Great thanks.

    I think the link you posted relates to an Associate Message:

    // Associate the accounts to the contact record.// Create a collection of the entities that will be // associated to the contact. EntityReferenceCollection relatedEntities = new EntityReferenceCollection(); relatedEntities.Add(new EntityReference(Account.EntityLogicalName, _account1Id)); relatedEntities.Add(new EntityReference(Account.EntityLogicalName, _account2Id)); relatedEntities.Add(new EntityReference(Account.EntityLogicalName, _account3Id)); // Create an object that defines the relationship between the contact and account. Relationship relationship = new Relationship("account_primary_contact"); //Associate the contact with the 3 accounts. _service.Associate(Contact.EntityLogicalName, _contactId, relationship, relatedEntities);

    Would you mind pointing me in the direction of some code that uses the:

    "...alternatively just set the foreign key on the 'child' record when you create it, setting it to an 'EntityReference'."

    Concept

    Cheers

    Thursday, October 18, 2012 12:57 PM
  • Hi,

    In the following example you'll see the OpportunityProduct records being associated to the Opportunity via a 1:N relationship. The opportunityid Foreign key attribute is set to the EntityReference that contains the GUID of the parent Opportunity:

    // Create a catalog product
                        OpportunityProduct catalogProduct = new OpportunityProduct
                        {
                            OpportunityId = new EntityReference(Opportunity.EntityLogicalName,
                                _opportunityId),
                            ProductId = new EntityReference(Product.EntityLogicalName,
                                _product1Id),
                            UoMId = new EntityReference(UoM.EntityLogicalName, _defaultUnitId),
                            Quantity = 8,
                            Tax = new Money(12.42m)
                        };

    This is taken from the full sample at :http://msdn.microsoft.com/en-us/library/gg509009.aspx

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, October 18, 2012 3:21 PM
    Answerer
  • You have been a great help. Thanks Scott.
    Thursday, October 18, 2012 5:21 PM