Monthly Archives: May 2013

Query N to N relationship

Having and N to N relationship between points and parameters, from a point ID we want to retrieve all the related parameters.

 EntityCollection getEPParameters(IOrganizationService serv, Guid POINTid)
            QueryExpression query = new QueryExpression()
            {   //Parameters entity
                EntityName = "mile_emissionpointparameters",
                ColumnSet = new ColumnSet(true),
                LinkEntities = 
                            new LinkEntity
                                LinkFromEntityName = "mile_emissionpointparameters", //Parameters table
                                LinkFromAttributeName = "mile_emissionpointparametersid", //Parameters table id
                                LinkToEntityName = "mike_mile_emissionpoint_mile_emissionpointpara", // N to N entity 
                                LinkToAttributeName = "mile_emissionpointparametersid", 
                                LinkCriteria = new FilterExpression
                                    FilterOperator = LogicalOperator.And,
                                    Conditions = 
                                        new ConditionExpression
                                            AttributeName = "mile_emissionpointid",
                                            Operator = ConditionOperator.Equal,
                                            Values = { POINTid }  // Point id

            // Obtain results from the query expression.
            return serv.RetrieveMultiple(query);


If what we need is to get a list of entities filtered by some values of the other entities in the N to N relationship we just need to use nested LinkEntities. Imagine we have an N 2 N between control measures and eventRevisions. We want to get all the measures related to any open – semiopen (L_status 3 or 4) revisions.

QueryExpression objQueryExpression = new QueryExpression("lema_controlmeasure");
objQueryExpression.ColumnSet = new ColumnSet(true);
objQueryExpression.Distinct = true;

// Add the filter using the N to N middle table 
LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Inner;
linkEntity1.LinkFromEntityName = "L_controlmeasure";
linkEntity1.LinkFromAttributeName = "L_controlmeasureid";
linkEntity1.LinkToEntityName = "L_controlmeasure_hazardouseventrevision";
linkEntity1.LinkToAttributeName = "L_controlmeasureid";

// To be able to do a double Link we use another LinkEntity within the the original linked Entity
LinkEntity InnerLinkEntity = new LinkEntity();
InnerLinkEntity.JoinOperator = JoinOperator.Inner;
InnerLinkEntity.LinkFromEntityName = "L_controlmeasure_hazardouseventrevision";
InnerLinkEntity.LinkFromAttributeName = "L_hazardouseventrevisionid";
InnerLinkEntity.LinkToEntityName = "L_hazardouseventrevision";
InnerLinkEntity.LinkToAttributeName = "L_hazardouseventrevisionid";

// Filter of the inner Join by a value of the second entity of the N 2 N
InnerLinkEntity.LinkCriteria.AddCondition("L_status", ConditionOperator.In, 3, 4);

//Add this link to the original link


EntityCollection results = _service.RetrieveMultiple(objQueryExpression);

Following this idea of having a InnerLinkEntity, we could add more innerLinkEntities being able to query N relationships at once!

I hope it was helpfull!!!



Convert Text Field to Hyperlink

function ConvertToLink(fldName) {
        var content = Xrm.Page.getAttribute(fldName).getValue();
        var btn = "<a href='javascript: void(0);' onclick=\"\'" + content + "\', \'windowname1\', \'width=600, height=650\');  return false;\" style='color:blue;text-decoration:underline !important'>" + content +"</a>";
        var ctrl = Xrm.Page.ui.controls.get(fldName)._control;
        // Add the new button 
        ctrl.get_element().innerHTML += btn;
        // Hide the textbox 
        ctrl.get_element() = 'none';