locked
CRM 2011: Change filter in subgrid RRS feed

  • Question

  • Hi,

    How can I on a form in CRM 2011 display a grid with the resultset of a custom query (that can be based on properties on the form)?
    Is there a way to pass FecthXML as a parameter into a subgrid or change the filter in some other way through JavaScript?

    Let's say that you want to display a subgrid on the contact form. You want the subgrid to list all other contacts related to the same account as the current contact. Is there a way to achive this in CRM 2011? Or is the best shot to create a Silverlight "list-application" and pass the query?

    For CRM 4.0 there is this solution to the problem: http://mscrm4ever.blogspot.com/2009/03/display-fetch-in-iframe-part-2.html
    I'm looking for something similar...

    Emil Mattsson

    Wednesday, February 23, 2011 6:26 PM

All replies

  • Hi Emil,

    See this thread, I think I had the same requirement.

    Rob

     


    Microsoft Certified Technology Specialist (CRM) GAP Consulting Ltd.
    Wednesday, February 23, 2011 9:16 PM
    Answerer
  • Hi Emil,

    I have made a blog post that includes script to apply a custom fetchXML / custm query to a sub-grid.  You can find the post at CRM 2011 - How to filter the 'Add Existing' button on a sub-grid. (A.K.A. filtered sub-grid look up) Hopefully it provides you a solution to your question.

    • Proposed as answer by Matthias Back Wednesday, August 24, 2011 7:17 AM
    Sunday, July 3, 2011 4:38 AM
  • Hi Eric,

    I got a message like "An error occured" while trying to update a subgrid.

    Here is code i'm using 

     //gets the Grid
        var grilledopportunite = document.getElementById("grilleopportunitees");

        // Extraire tous les champs qui vont servir de paramètres pour notre vue
        //--1) Commercial :ownerid
        //--2) Date de conclusion : actualclosedate (Entre ... et ...)
        //--3) Etat de la demande : statuscode operator='in' ...
        //--4) Conclusion : alt_libelle' operator='eq' value='1' />";
        
        // 1) Initializing the lookup field to store in an array.
        var commercial = new Array;

        //Get the lookup field
        commercial = Xrm.Page.getAttribute("alt_commercial").getValue();

        //This will get the lookup field guid if there is value present in the lookup
        if (commercial != null) {
            var commercialid = commercial[0].id;
        }

        //--2) Date de conclusion : actualclosedate (Entre ... et ...)
        var de = Xrm.Page.getAttribute("alt_de").getValue();
        var a= Xrm.Page.getAttribute("alt_a").getValue();
        
        //--3) Etat de la demande : statuscode operator='in' ...
        var etatdelademande= Xrm.Page.getAttribute("alt_etatdelopportunite").getValue();
        
            //--4) Conclusion : alt_libelle' operator='eq' value='1' />";
        var conclusion= Xrm.Page.getAttribute("alt_conclusion").getValue();
        
        //This method is to ensure that grid is loaded before processing.
        if (grilledopportunite ==null || grilledopportunite.readyState != "complete")
        {
            //This statement is used to wait for 2 seconds and recall the function until the grid is loaded.
            setTimeout('PackageIndicateursSO.indicateur.updateSubGrid()', 2000);
            return;
        }    

        alert("voici les parametres"  + "\n" + "commercial" + commercialid + "de quelle date" + de + "à" + a + "\n"+ "conclusion"+ conclusion);
        
        var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
        fetchXml += "<entity name='opportunity'>";
        fetchXml += "<attribute name='alt_totaloffre' />";
        fetchXml += "<attribute name='name' />";
        fetchXml += "<attribute name='ownerid' />";
        fetchXml += "<attribute name='alt_probabilite' />";
        fetchXml += "<attribute name='alt_numero' />";
        fetchXml += "<attribute name='alt_libelle' />";
        fetchXml += "<attribute name='createdon' />";
        fetchXml += "<attribute name='alt_compte' />";
        fetchXml += "<attribute name='alt_totalpondere' />";
        fetchXml += "<attribute name='opportunityid' />";
        fetchXml += "<order attribute='alt_numero' descending='true' />";
        fetchXml += "<filter type='and'>";
          fetchXml += "<condition attribute='ownerid' operator='eq'uiname='myname myfirstname' uitype='systemuser' value='"+commercialid +"' />";
          fetchXml += "<condition attribute='actualclosedate' operator='on-or-after' value='"+de+"' />";
          fetchXml += "<condition attribute='actualclosedate' operator='on-or-before' value='"+a+"' />";
          fetchXml += "<condition attribute='alt_libelle' operator='eq' value='"+conclusion+"' />";
          fetchXml += "<condition attribute='statuscode' operator='in'>";
            fetchXml += "<value>1</value>";
            fetchXml += "<value>"+etatdelademande+"</value>";
          fetchXml += "</condition>";
        fetchXml += "</filter>";
      fetchXml += "</entity>";
    fetchXml += "</fetch>";

       //Setting the fetch xml to the sub grid.
        grilledopportunite.control.setParameter("fetchXml", fetchXml);

        //This statement will refresh the sub grid after making all modifications.
        grilledopportunite.control.refresh();

    },

    Please i need you help.

    Wednesday, October 17, 2012 3:31 PM
  • Hi try following its working for Rollup 12 and earlier.

    function UpdateSubGrid() {
        
        //This will get the related entity grid details and store in a variable.
        var relatedGrid = document.getElementById("SubGrid");
    
        //Get the lookup field
        var lookupfield = Xrm.Page.getAttribute("new_relatedEntityid").getValue();
    
        //This will get the lookup field guid if there is value present in the lookup
        if (lookupfield != null) {
            var lookupid = lookupfield[0].id;
            var lookupName = lookupfield[0].name;
        }
        else {
            return;
        }
    
        //This method is to ensure that grid is loaded before processing.
        if (relatedGrid == null || relatedGrid.readyState != "complete") {
            setTimeout('UpdateSubGrid()', 2000);
            return;
        }
    
        //This is the fetch xml code which will retrieve all 
    //the related entity records to the lookup selected
    
        var fetchXml = "<?xml version='1.0'?>";
        fetchXml += "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
        fetchXml += "<entity name='new_entity'>";
        fetchXml += "<attribute name='new_name' />";
        fetchXml += "<attribute name='new_code' />";
        fetchXml += "<attribute name='createdon' />";
        fetchXml += "<order attribute='new_name' descending='false' />";
        fetchXml += "<filter type='and'>";
        fetchXml += "<condition attribute='new_relatedEntityid' operator='eq' uiname='" + lookupName + "' uitype='new_relatedEntity' value='" + lookupid + "' />";
        fetchXml += "</filter>";
        fetchXml += "</entity>";
        fetchXml += "</fetch>";
    
        try{ 
            //Setting the fetch xml to the sub grid.
            relatedGrid.control.setParameter("fetchXml", fetchXml);
    
            //This statement will refresh the sub grid after making all modifications.
            relatedGrid.control.refresh();
        }
        catch(e) // FOR ROLLUP 12
        {
            //Setting the fetch xml to the sub grid.
            relatedGrid.control.SetParameter("fetchXml", fetchXml);
    
            //This statement will refresh the sub grid after making all modifications.
            relatedGrid.control.Refresh();
        }
    }


    http://www.allaboutdynamics.com

    • Proposed as answer by Johnny Ross Tuesday, July 30, 2013 5:05 PM
    Tuesday, March 12, 2013 9:10 AM
  • Note : On SubGrid properties form , make sure that you have 'All Record Types' selected as the Data source.

    John Ross

    Tuesday, July 30, 2013 5:08 PM