locked
How to get values from entity in a many to many relation (CRM 4.0) RRS feed

  • Question

  •  

    Hi

    I have two custom entities that are connected in a many-to-many relation. If I have the Guid of one entity how do I retrieve all related entities from that many-to-many relation?  Can someone link a sample or tell me what objects I need to look at.

     

    /C

    Monday, June 23, 2008 12:00 PM

Answers

  • Hi Christian,

     

    The N:N relationships have one entity behind to store the related objects, you can find the name of that entity in the relationship definition. Normally is composed by the schema name of one entity + "_" + the schema name of the other.

     

    This entity has his own Key attribute which is the entity name + "id", and two other attributes that are called as the schema name of the related entity + "id". For example, if it is a N:N relationship between "account" and "ap_myentity", you will find two attributes called "accountid" and "ap_myentityid". This attributes store the Guid for each pair of related records in the relationship. Anyway, if you use the metadata browser you can view this N:N relationship entities and check the name of his attributes.

     

    So, for querying you just have to query this entity as you do with any other entity. In your case you would create a query adding the condition of one of the fields to be equal to the objectid you have, and with this you would get the ids of all the related records of the other entity.

     

    Here is one example using FetchXML, but it can be done with Query objects as well.

     

    Code Snippet

    string fetchXML =

    "<fetch distinct='false' mapping='logical'>" +

    "<entity name='" + relationshipName + "'><filter type='and'>" +

    "<condition attribute='" + relatedEntityName + "id' operator='eq' value='" + relatedId + "' />" +

    "<condition attribute='" + typeEntityName + "id' operator='eq' value='" + profileId + "' />" +

    "</filter></entity></fetch>";

     

     

    BTW, to create/remove new relationship records. I mean, to relate a pair of records. You have two messages available on the CRM Web Services, one is AssociateEntitiesRequest and the other one is DisassociateEntitiesRequest. Here is one example for create an association.

     

    Code Snippet

    AssociateEntitiesRequest request = new AssociateEntitiesRequest();

    request.RelationshipName = relationshipName;

    Moniker related = new Moniker();

    related.Name = relatedEntityName;

    related.Id = new Guid(relatedId);

    Moniker type = new Moniker();

    type.Name = typeEntityName;

    type.Id = new Guid(profileId);

    request.Moniker1 = type;

    request.Moniker2 = related;

    service.Execute(request);

     

     

    Hope this helps,

    Marco Amoedo

    DynamicsCrmKicks

    Monday, June 23, 2008 2:07 PM
    Moderator
  • here is code

     

    public static void retrieveEntitiesViaFetch(TitanMiscTests.CrmSdk.CrmService service)

    {

    string linkFetch = @”<fetch version=”"1.0″” output-format=”"xml-platform”" mapping=”"logical”" distinct=”"true”">

    <entity name=”"lead”">

    <attribute name=”"fullname”"/>

    <order attribute=”"fullname”" descending=”"true”"/>

    <link-entity name=”"new_account_lead_custom”" from=”"leadid”" to=”"leadid”" visible=”"false”" intersect=”"true”">

    <link-entity name=”"account”" from=”"accountid”" to=”"accountid”" alias=”"aa”">

    <filter type=”"and”">

    <condition attribute=”"name”" operator=”"eq”" value=”"Foo”"/>

    </filter>

    </link-entity>

    </link-entity>

    </entity>

    </fetch>”;

    string result = service.Fetch(linkFetch);

    Console.WriteLine(result);

    }

     

     

    let me know if you want more.. I can give you and explain more

     

    Regards,

    Imran

     

    http://microsoftcrm3.blogspot.com

     

    msn : mscrmexpert@gmail.com

     

    Monday, June 23, 2008 4:23 PM
    Moderator

All replies

  • Hi Christian,

     

    The N:N relationships have one entity behind to store the related objects, you can find the name of that entity in the relationship definition. Normally is composed by the schema name of one entity + "_" + the schema name of the other.

     

    This entity has his own Key attribute which is the entity name + "id", and two other attributes that are called as the schema name of the related entity + "id". For example, if it is a N:N relationship between "account" and "ap_myentity", you will find two attributes called "accountid" and "ap_myentityid". This attributes store the Guid for each pair of related records in the relationship. Anyway, if you use the metadata browser you can view this N:N relationship entities and check the name of his attributes.

     

    So, for querying you just have to query this entity as you do with any other entity. In your case you would create a query adding the condition of one of the fields to be equal to the objectid you have, and with this you would get the ids of all the related records of the other entity.

     

    Here is one example using FetchXML, but it can be done with Query objects as well.

     

    Code Snippet

    string fetchXML =

    "<fetch distinct='false' mapping='logical'>" +

    "<entity name='" + relationshipName + "'><filter type='and'>" +

    "<condition attribute='" + relatedEntityName + "id' operator='eq' value='" + relatedId + "' />" +

    "<condition attribute='" + typeEntityName + "id' operator='eq' value='" + profileId + "' />" +

    "</filter></entity></fetch>";

     

     

    BTW, to create/remove new relationship records. I mean, to relate a pair of records. You have two messages available on the CRM Web Services, one is AssociateEntitiesRequest and the other one is DisassociateEntitiesRequest. Here is one example for create an association.

     

    Code Snippet

    AssociateEntitiesRequest request = new AssociateEntitiesRequest();

    request.RelationshipName = relationshipName;

    Moniker related = new Moniker();

    related.Name = relatedEntityName;

    related.Id = new Guid(relatedId);

    Moniker type = new Moniker();

    type.Name = typeEntityName;

    type.Id = new Guid(profileId);

    request.Moniker1 = type;

    request.Moniker2 = related;

    service.Execute(request);

     

     

    Hope this helps,

    Marco Amoedo

    DynamicsCrmKicks

    Monday, June 23, 2008 2:07 PM
    Moderator
  • here is code

     

    public static void retrieveEntitiesViaFetch(TitanMiscTests.CrmSdk.CrmService service)

    {

    string linkFetch = @”<fetch version=”"1.0″” output-format=”"xml-platform”" mapping=”"logical”" distinct=”"true”">

    <entity name=”"lead”">

    <attribute name=”"fullname”"/>

    <order attribute=”"fullname”" descending=”"true”"/>

    <link-entity name=”"new_account_lead_custom”" from=”"leadid”" to=”"leadid”" visible=”"false”" intersect=”"true”">

    <link-entity name=”"account”" from=”"accountid”" to=”"accountid”" alias=”"aa”">

    <filter type=”"and”">

    <condition attribute=”"name”" operator=”"eq”" value=”"Foo”"/>

    </filter>

    </link-entity>

    </link-entity>

    </entity>

    </fetch>”;

    string result = service.Fetch(linkFetch);

    Console.WriteLine(result);

    }

     

     

    let me know if you want more.. I can give you and explain more

     

    Regards,

    Imran

     

    http://microsoftcrm3.blogspot.com

     

    msn : mscrmexpert@gmail.com

     

    Monday, June 23, 2008 4:23 PM
    Moderator
  • Thank you to the both of you. I did see the Fetch sample but I was hoping for another more object option. But if this is the way it is then so be it.

     

    /c

    Monday, June 23, 2008 4:35 PM
  • Is this what you are looking for? Some code to get the competitor information for a given opportunity GUID.

    Code Snippet

    Guid oppID = new Guid("{D0F05F29-1C54-DD11-A112-0003FF86152E}");

    // Create a query expression.
    QueryExpression qe = new QueryExpression();
    qe.EntityName = "competitor";
    qe.ColumnSet = new AllColumns();

    // Create the link entity from opportuntiycompetiorsto competitor.
    LinkEntity le = new LinkEntity();
    le.LinkFromEntityName = "competitor";
    le.LinkFromAttributeName = "competitorid";
    le.LinkToEntityName = "opportunitycompetitors";
    le.LinkToAttributeName = "competitorid";

    LinkEntity le2 = new LinkEntity();
    le2.LinkFromEntityName = "opportunitycompetitors";
    le2.LinkFromAttributeName = "opportunityid";
    le2.LinkToEntityName = "opportunity";
    le2.LinkToAttributeName = "opportunityid";

    // Create the condition to test the opportunityid
    ConditionExpression ce = new ConditionExpression();
    ce.AttributeName = "opportunityid";
    ce.Operator = ConditionOperator.Equal;
    ce.Values = new object[] { oppID };

    // Add the condition to the link entity.
    le2.LinkCriteria = new FilterExpression();
    le2.LinkCriteria.Conditions = new ConditionExpression[] { ce };

    // Add the from and to links to the query.
    le.LinkEntities = new LinkEntity[] { le2 };
    qe.LinkEntities = new LinkEntity[] { le };

    // Retrieve the roles and write each one to the console.
    BusinessEntityCollection bec = service.RetrieveMultiple(qe);

    If you want to do this with JavaScript, it'd be:

    Code Snippet

    var xml = "" +
    "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
    "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
    " <soap:Header>" +
    " <CrmAuthenticationToken xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
    " <AuthenticationType xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">0</AuthenticationType>" +
    " <OrganizationName xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">MicrosoftCRM</OrganizationName>" +
    " <CallerId xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">00000000-0000-0000-0000-000000000000</CallerId>" +
    " </CrmAuthenticationToken>" +
    " </soap:Header>" +
    " <soap:Body>" +
    " <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
    " <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
    " <q1:EntityName>competitor</q1:EntityName>" +
    " <q1:ColumnSet xsi:type=\"q1:AllColumns\" />" +
    " <q1:Distinct>false</q1:Distinct>" +
    " <q1:LinkEntities>" +
    " <q1:LinkEntity>" +
    " <q1:LinkFromAttributeName>competitorid</q1:LinkFromAttributeName>" +
    " <q1:LinkFromEntityName>competitor</q1:LinkFromEntityName>" +
    " <q1:LinkToEntityName>opportunitycompetitors</q1:LinkToEntityName>" +
    " <q1:LinkToAttributeName>competitorid</q1:LinkToAttributeName>" +
    " <q1:JoinOperator>Inner</q1:JoinOperator>" +
    " <q1:LinkEntities>" +
    " <q1:LinkEntity>" +
    " <q1:LinkFromAttributeName>opportunityid</q1:LinkFromAttributeName>" +
    " <q1:LinkFromEntityName>opportunitycompetitors</q1:LinkFromEntityName>" +
    " <q1:LinkToEntityName>opportunity</q1:LinkToEntityName>" +
    " <q1:LinkToAttributeName>opportunityid</q1:LinkToAttributeName>" +
    " <q1:JoinOperator>Inner</q1:JoinOperator>" +
    " <q1:LinkCriteria>" +
    " <q1:FilterOperator>And</q1:FilterOperator>" +
    " <q1:Conditions>" +
    " <q1:Condition>" +
    " <q1:AttributeName>opportunityid</q1:AttributeName>" +
    " <q1:Operator>Equal</q1:Operator>" +
    " <q1:Values>" +
    " <q1:Value xmlns:q2=\"http://microsoft.com/wsdl/types/\" xsi:type=\"q2:guid\">d0f05f29-1c54-dd11-a112-0003ff86152e</q1:Value>" +
    " </q1:Values>" +
    " </q1:Condition>" +
    " </q1:Conditions>" +
    " </q1:LinkCriteria>" +
    " </q1:LinkEntity>" +
    " </q1:LinkEntities>" +
    " </q1:LinkEntity>" +
    " </q1:LinkEntities>" +
    " </query>" +
    " </RetrieveMultiple>" +
    " </soap:Body>" +
    "</soap:Envelope>" +
    "";

    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);

    var resultXml = xmlHttpRequest.responseXML;
    alert(resultXml.xml);



    Friday, July 18, 2008 3:20 PM
  • I had try it. but it come out credential and keep asking to fill in, then will prompt to failure access. any solution for this problem?
    Monday, September 6, 2010 4:12 AM
  • It means there is some authentication issue, try to use

    try to replace

    " <soap:Header>" +
    " <CrmAuthenticationToken xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
    " <AuthenticationType xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">0</AuthenticationType>" +
    " <OrganizationName xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">MicrosoftCRM</OrganizationName>" +
    " <CallerId xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">00000000-0000-0000-0000-000000000000</CallerId>" +
    " </CrmAuthenticationToken>" +
    " </soap:Header>" +

    with

     

    var authenticationHeader = GenerateAuthenticationHeader();
    var xml = "" +
    "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
    "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + 
    authenticationHeader+
    " <soap:Body>" +

    rest of the code


    Mahain : http://mahenderpal.wordpress.com
    Monday, September 6, 2010 6:42 AM
    Moderator