none
Testing the existing of the NN RelationShip

    Question

  • Hi All,

    Haw can I test the existing of NN RelationShip between two entities before sending the AssociateEntitiesRequest?

    Thanks for help

    Tuesday, August 31, 2010 9:07 AM

Answers

  • // Create a query expression.
    QueryExpression relationshipCheckQuery = new QueryExpression();
    relationshipCheckQuery.EntityName = "new_phonenumber";
    relationshipCheckQuery.ColumnSet = new ColumnSet(new string[] {"new_phonenumberid"});
    
    // Create the link entity from role to systemuserroles.
    LinkEntity le = new LinkEntity();
    le.LinkFromEntityName = "new_phonenumber";
    le.LinkFromAttributeName = "new_phonenumberid";
    le.LinkToEntityName = "new_account_phonenumber";
    le.LinkToAttributeName = "new_phonenumberid";
    
    LinkEntity le2 = new LinkEntity();
    le2.LinkFromEntityName = "new_account_phonenumber";
    le2.LinkFromAttributeName = "accountid";
    le2.LinkToEntityName = "account";
    le2.LinkToAttributeName = "accountid";
    
    // Create the condition to test the account ID.
    ConditionExpression ce = new ConditionExpression();
    ce.AttributeName = "accountid";
    ce.Operator = ConditionOperator.Equal;
    ce.Values = new object[]{in_customerid};
    
    // Add the condition to the link entity.
    le2.LinkCriteria = new FilterExpression();
    le2.LinkCriteria.Conditions = new ConditionExpression[]{ce};
    
    // Create the condition to test the phone ID
    ConditionExpression ce2 = new ConditionExpression();
    ce2.AttributeName = "new_phonenumberid";
    ce2.Operator = ConditionOperator.Equal;
    ce2.Values = new object[]{in_phonenumberid};
    
    // Add the condition to the Query
    relationshipCheckQuery.Criteria = new FilterExpression();
    relationshipCheckQuery.Criteria.Conditions = new ConditionExpression[]{ce2};
    
    // Add the from and to links to the query.
    le.LinkEntities = new LinkEntity[]{le2};
    relationshipCheckQuery.LinkEntities = new LinkEntity[]{le};
    
    // Establish a request to use the query and use dynamic entities (necessary with use of the Assemblies)
    RetrieveMultipleRequest relationshipCheckRequest = new RetrieveMultipleRequest();
    relationshipCheckRequest.ReturnDynamicEntities = true;
    relationshipCheckRequest.Query = relationshipCheckQuery;
    
    // Execute the query, cast the response
    RetrieveMultipleResponse relationshipCheckResponse = (RetrieveMultipleResponse)crmService.Execute(relationshipCheckRequest);
    
    // If no records are returned, the account and new_phonenumber records are not currently related, so it's ok to process the AssociateEntitiesRequest for them
    if (relationshipCheckResponse.BusinessEntityCollection.Count == 0)
    {
     Moniker moniker1 = new Moniker();
     moniker1.Name = "account";
     moniker1.Id = in_customerid;
    
     Moniker moniker2 = new Moniker();
     moniker2.Name = "new_phonenumber";
     moniker2.Id = in_phonenumberid;
    
     AssociateEntitiesRequest request = new AssociateEntitiesRequest();
     request.Moniker1 = moniker1;
     request.Moniker2 = moniker2;
     request.RelationshipName = "new_account_PhoneNumber";
    
     crmService.Execute(request);
    }
    

    Try that.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Thursday, September 02, 2010 5:19 PM
    Moderator
  • For more concise code, using the example provided by Ranjitsingh:

    string fetchXml = "<fetch mapping='logical'> <entity name='new_account_phonenumber'>"
    + "<all-attributes />"
    + "<filter>"
    + "<condition attribute='new_phonenumberid' operator='eq' value ='" + in_phonenumberid + "' />"
    + "<condition attribute='accountid' operator='eq' value='" + in_customerid + "' />"
    + "</filter>"
    + "</entity>"
    + "</fetch>";
    
    string strResult = crmService.Fetch(fetchXml);
    
    XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml(strResult);
    
    XmlNodeList nodeList = xmlDoc.SelectNodes("resultset/result");
    
    if (nodeList.Count == 0)
    {
     Moniker moniker1 = new Moniker();
     moniker1.Name = "account";
     moniker1.Id = in_customerid;
    
     Moniker moniker2 = new Moniker();
     moniker2.Name = "new_phonenumber";
     moniker2.Id = in_phonenumberid;
    
     AssociateEntitiesRequest request = new AssociateEntitiesRequest();
     request.Moniker1 = moniker1;
     request.Moniker2 = moniker2;
     request.RelationshipName = "new_account_PhoneNumber";
    
     crmService.Execute(request);
    }
    



    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Thursday, September 02, 2010 7:36 PM
    Moderator

All replies

  • Hi,

     

    What exactly would you like to do? Check whether two entities are related with a N:N relationship? Are you talking about the native N:N - i.e. a hidden intersect entity exists between your entities, or are you talking about your custom N:N where you use a custom created entity to hold the foreign keys of the linked entities?

     

    Kind regards,

    Kuba


    -- Kuba Skałbania, Netwise
    Tuesday, August 31, 2010 9:20 AM
  • I think he's asking about testing whether or not, for a known relationship, any two records are currently related.

    So long has you have the GUIDs for the records, you can assemble a RetrieveByAttribute request on the "intersect" entity, as Kuba states.  This entity is named within the Relationship.  It should have a total of three attributes:

    1.) It's own Key attribute, which should follow the format:  <intersect entity schema name>id

    2.) A Lookup attribute to one of the entities, which should follow the format:  <entity 1 key attribute schema name>

    3.) A Lookup attribute to the other entity, which should follow the format:  <entity 2 key attribute schema name>

    For instance, for a hypothetical N:N relationship between Accounts and Contacts which has the name "new_accounts_contacts", the attributes will be:

    1.) new_accounts_contactsid

    2.) accountid

    3.) contactid

    So, if you pass your record IDs to a RetrieveByAttribute for attributes 2 and 3, you will get one result if there is an existing relationship, or none.  I would recommend looking at the "Filtered" view of the intersect entity to obtain the proper attribute schema names.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Tuesday, August 31, 2010 7:38 PM
    Moderator
  • Yes,

    Please see the below post which will retrieve the assocated records [N:N].
    http://mscrm-developer.blogspot.com/2008/09/retrieve-associated-records-for-many-to.html

    Once you get all records you can verify / check, if the record alreayd exists.

    Please see these posts as well for more information on N:N records CREATE / DELETE:

    http://mscrm-developer.blogspot.com/2008/09/creating-records-for-many-to-many.html
    http://mscrm-developer.blogspot.com/2008/09/delete-disassociate-many-to-many.html

    Hope this helps.


    Thanks, Ranjitsingh R | http://mscrm-developer.blogspot.com/ | MS CRM Consultant
    Wednesday, September 01, 2010 3:47 AM
  • Hi Ranjitsingh R and Kuba Skalbania

    I have NN RelationShip between two entity (account and new_phonenumber) the name of the relation is  new_account_PhoneNumber to create NN RelationShip between a record of account and a record of new_phonenumber I send the folowing message into my plugin:

    Moniker

     

    moniker1 = new Moniker();

    moniker1.Name = "account";

    moniker1.Id = in_customerid;

     

    Moniker moniker2 = new Moniker();

    moniker2.Name =

    "new_phonenumber";

    moniker2.Id = in_phonenumberid;

     

    AssociateEntitiesRequest request = new AssociateEntitiesRequest();

    request.Moniker1 = moniker1;

    request.Moniker2 = moniker2;

    request.RelationshipName =

    "new_account_PhoneNumber";

    crmService.Execute(request);

    I need now to test if those two record are already related before sending the AssociateEntitiesRequest. I found the following link that describe how to, but I did'nt understand how can I use it for my case.

    http://crm-pitstop.blogspot.com/2009/08/many-to-many-relationship.html

     

    Thanks for Help.

    Wednesday, September 01, 2010 10:14 AM
  • The following code assumes that the schema name of the intersection entity is identical to the relationship name (which is often the case)... if, however, it is different, you will need to update the code accordingly:

    // Setup a check query
    QueryByAttribute relationshipCheckQuery = new QueryByAttribute();
    
    // Use the N:N relationship entity's schema name
    relationshipCheckQuery.EntityName = "new_account_phonenumber";
    
    // Add the key for the entity in the retrieved columns
    relationshipCheckQuery.ColumnSet = new ColumnSet(new string[] { "new_account_phonenumberid" });
    
    // Query for entries which match IDs from both account and new_phonenumber records
    relationshipCheckQuery.Attributes = new string[] { "accountid", "new_phonenumberid" };
    relationshipCheckQuery.Values = new object[] { in_customerid, in_phonenumberid };
    
    // Establish a request to use the query and use dynamic entities (necessary with use of the Assemblies)
    RetrieveMultipleRequest relationshipCheckRequest = new RetrieveMultipleRequest();
    relationshipCheckRequest.ReturnDynamicEntities = true;
    relationshipCheckRequest.Query = relationshipCheckQuery;
    
    // Execute the query, cast the response
    RetrieveMultipleResponse relationshipCheckResponse = (RetrieveMultipleResponse)crmService.Execute(relationshipCheckRequest);
    
    // If no records are returned, the account and new_phonenumber records are not currently related, so it's ok to process the AssociateEntitiesRequest for them
    if (relationshipCheckResponse.BusinessEntityCollection.Count == 0)
    {
     Moniker moniker1 = new Moniker();
     moniker1.Name = "account";
     moniker1.Id = in_customerid;
    
     Moniker moniker2 = new Moniker();
     moniker2.Name = "new_phonenumber";
     moniker2.Id = in_phonenumberid;
    
     AssociateEntitiesRequest request = new AssociateEntitiesRequest();
     request.Moniker1 = moniker1;
     request.Moniker2 = moniker2;
     request.RelationshipName = "new_account_PhoneNumber";
    
     crmService.Execute(request);
    }
    

     


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Wednesday, September 01, 2010 7:35 PM
    Moderator
  • Thanks DavidBerry

    I try it the code but the server return to me the following execption :

    "\n  0x80040800\n  The 'RetrieveMultiple' method does not support entities of type 'new_account_PhoneNumber'.\n  Platform\n".

    In fact 'new_account_PhoneNumber' is not a real entity its just a table of NN RelationShip.

    Any idea!

    Thursday, September 02, 2010 8:57 AM
  • This is because you can't use RetrieveMultiple funciton for N:N relationship

    For your informations

    When you create a many-to-many relationship, an intersect table is created. You can use the intersect tables in the QueryExpression for a RetrieveMultiple query, but you cannot retrieve the intersect table records directly with the RetrieveMultiple method. To retrieve the records in an intersect table, you must use the Fetch method. For more information, see Using Intersect Tables.

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


    Mahain : http://mahenderpal.wordpress.com
    Thursday, September 02, 2010 9:19 AM
    Moderator
  • Thanks for the correction, Mahender.  You're right.  I overlooked the blurb in the page you linked.  To make up for the error, I'll draw up the new code for Balhato to use.
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Thursday, September 02, 2010 4:58 PM
    Moderator
  • // Create a query expression.
    QueryExpression relationshipCheckQuery = new QueryExpression();
    relationshipCheckQuery.EntityName = "new_phonenumber";
    relationshipCheckQuery.ColumnSet = new ColumnSet(new string[] {"new_phonenumberid"});
    
    // Create the link entity from role to systemuserroles.
    LinkEntity le = new LinkEntity();
    le.LinkFromEntityName = "new_phonenumber";
    le.LinkFromAttributeName = "new_phonenumberid";
    le.LinkToEntityName = "new_account_phonenumber";
    le.LinkToAttributeName = "new_phonenumberid";
    
    LinkEntity le2 = new LinkEntity();
    le2.LinkFromEntityName = "new_account_phonenumber";
    le2.LinkFromAttributeName = "accountid";
    le2.LinkToEntityName = "account";
    le2.LinkToAttributeName = "accountid";
    
    // Create the condition to test the account ID.
    ConditionExpression ce = new ConditionExpression();
    ce.AttributeName = "accountid";
    ce.Operator = ConditionOperator.Equal;
    ce.Values = new object[]{in_customerid};
    
    // Add the condition to the link entity.
    le2.LinkCriteria = new FilterExpression();
    le2.LinkCriteria.Conditions = new ConditionExpression[]{ce};
    
    // Create the condition to test the phone ID
    ConditionExpression ce2 = new ConditionExpression();
    ce2.AttributeName = "new_phonenumberid";
    ce2.Operator = ConditionOperator.Equal;
    ce2.Values = new object[]{in_phonenumberid};
    
    // Add the condition to the Query
    relationshipCheckQuery.Criteria = new FilterExpression();
    relationshipCheckQuery.Criteria.Conditions = new ConditionExpression[]{ce2};
    
    // Add the from and to links to the query.
    le.LinkEntities = new LinkEntity[]{le2};
    relationshipCheckQuery.LinkEntities = new LinkEntity[]{le};
    
    // Establish a request to use the query and use dynamic entities (necessary with use of the Assemblies)
    RetrieveMultipleRequest relationshipCheckRequest = new RetrieveMultipleRequest();
    relationshipCheckRequest.ReturnDynamicEntities = true;
    relationshipCheckRequest.Query = relationshipCheckQuery;
    
    // Execute the query, cast the response
    RetrieveMultipleResponse relationshipCheckResponse = (RetrieveMultipleResponse)crmService.Execute(relationshipCheckRequest);
    
    // If no records are returned, the account and new_phonenumber records are not currently related, so it's ok to process the AssociateEntitiesRequest for them
    if (relationshipCheckResponse.BusinessEntityCollection.Count == 0)
    {
     Moniker moniker1 = new Moniker();
     moniker1.Name = "account";
     moniker1.Id = in_customerid;
    
     Moniker moniker2 = new Moniker();
     moniker2.Name = "new_phonenumber";
     moniker2.Id = in_phonenumberid;
    
     AssociateEntitiesRequest request = new AssociateEntitiesRequest();
     request.Moniker1 = moniker1;
     request.Moniker2 = moniker2;
     request.RelationshipName = "new_account_PhoneNumber";
    
     crmService.Execute(request);
    }
    

    Try that.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Thursday, September 02, 2010 5:19 PM
    Moderator
  • For more concise code, using the example provided by Ranjitsingh:

    string fetchXml = "<fetch mapping='logical'> <entity name='new_account_phonenumber'>"
    + "<all-attributes />"
    + "<filter>"
    + "<condition attribute='new_phonenumberid' operator='eq' value ='" + in_phonenumberid + "' />"
    + "<condition attribute='accountid' operator='eq' value='" + in_customerid + "' />"
    + "</filter>"
    + "</entity>"
    + "</fetch>";
    
    string strResult = crmService.Fetch(fetchXml);
    
    XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml(strResult);
    
    XmlNodeList nodeList = xmlDoc.SelectNodes("resultset/result");
    
    if (nodeList.Count == 0)
    {
     Moniker moniker1 = new Moniker();
     moniker1.Name = "account";
     moniker1.Id = in_customerid;
    
     Moniker moniker2 = new Moniker();
     moniker2.Name = "new_phonenumber";
     moniker2.Id = in_phonenumberid;
    
     AssociateEntitiesRequest request = new AssociateEntitiesRequest();
     request.Moniker1 = moniker1;
     request.Moniker2 = moniker2;
     request.RelationshipName = "new_account_PhoneNumber";
    
     crmService.Execute(request);
    }
    



    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Thursday, September 02, 2010 7:36 PM
    Moderator
  • I've written some new utility code for expressly this purpose:  http://crmentropy.blogspot.com/2010/09/nn-relationship-utility-code-javascript.html
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Wednesday, September 08, 2010 10:31 PM
    Moderator