Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Filter Subgrid records
Microsoft Dynamics CRM (Archived)

Filter Subgrid records

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello everyone,

I'm trying to filter records in a sub grid.

I've got two entities: InventoryWorksheet (which holds the subgrid control) , and InventoryWorksheetDetail.

Both Entities contain a lookup with the respective relationship to another entity named Warehouse.

like so: 

InventoryWorksheet.PNG

InventoryWorksheetDetail.PNG

Both these lookup fields pointing here:

warehouse.PNG

I'm unable to get this filtration happen ootb.

So I thought that js onLoad with fetchXML might do the trick...

Anyone knows how to achieve this requirement ?

Thank you,

- Bod

*This post is locked for comments

  • RHOU Profile Picture
    RHOU 65 on at
    RE: Filter Subgrid records

    I just created a very detailed feature wish to make this happen in a supported way for Unified Interface here:

    PLEASE VOTE!

    powerusers.microsoft.com/.../675538

  • Suggested answer
    Sanket87 Profile Picture
    Sanket87 160 on at
    RE: Filter Subgrid records

    Hi , 

    I have implemented the same in my project. You can follow this link for an step by step process.

    https://dynamicscrmlearnings.wordpress.com/2015/08/01/hello-world/?preview=true.

    Mark the answer verified. If found helpful

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Filter Subgrid records

    Please go through my link, I achieved this functionality in a supported way and also works on new UCI Forms.

    Do like my page and mark the answer correct.

    sank8sinha.wordpress.com/.../

  • Kjeld Poulsen Profile Picture
    Kjeld Poulsen 180 on at
    RE: Filter Subgrid records

    My requirement was to filter a subgrid on the salesorderdetail form.  This is not walk-in-the-park, because you cannot add any "sub entities" on a salesorder detail, and therefore you cannot use the build in filter on related entities.

    Instead i added a grid on the entity form, that did not relate to the salesorderdetail, and then added the shared filter column with javascript on the form:

    For create form, the grid should be hidden, for edit form, the grid should be visible if the arbo_prid field has a value, and it must add that as a filter to the grid.

    here comes the code:

    if (typeof (Kipon) == "undefined")
    { Kipon = { __namespace: true }; }

    Kipon.Salesorderdetail = {
    loadForm: function () {
    var formtype = Xrm.Page.ui.getFormType();
    var dispGrid = 'dispositionsGrid';

    if (formtype == 1) {
    Xrm.Page.getControl(dispGrid).setVisible(false);
    }

    if (formtype == 2) {
    var prid = Xrm.Page.getAttribute('arbo_prid').getValue();
    if (prid != null && prid.length == 1) {
    var grid = Xrm.Page.getControl(dispGrid);

    var fetchXml = "<fetch distinct='false' mapping='logical' output-format='xml-platform' version='1.0'>" +
    "<entity name='arbo_purchasereferencedisposition'>" +
    "<filter type='and'>" +
    "<condition attribute='statecode' value='0' operator='eq'/>" +
    "<condition attribute='arbo_purchasereferenceid' value='" + prid[0].id + "' operator='eq'/>" +
    "</filter>" +
    "</entity>" +
    "</fetch>";
    grid.getGrid().setParameter('fetchXml', fetchXml);
    grid.setVisible(true);
    grid.refresh();
    } else {
    Xrm.Page.getControl(dispGrid).setVisible(false);
    }
    }
    }
    }

  • CRM Beginner Profile Picture
    CRM Beginner 795 on at
    RE: Filter Subgrid records

    @BodipaMM Sir, My requirement is almost similar to yours but i m getting an error.Can you please help me?

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Filter Subgrid records

    To whomever is interested.. this worked for me:

    function filterSubGrid() 
{
	var entityName, entityId, entityLabel, lookupFieldObject;
	lookupFieldObject = Xrm.Page.data.entity.attributes.get('new_warehouse');
    if (lookupFieldObject.getValue() != null) {
        entityId = lookupFieldObject.getValue()[0].id;
        entityName = lookupFieldObject.getValue()[0].entityType;
      	entityLabel = lookupFieldObject.getValue()[0].name;
      	//alert(entityId);
    }


    var ReqInvenDetails = window.parent.document.getElementById("InvnWshDetSG");
    var currentInvenWshId = Xrm.Page.data.entity.getId();
    if (ReqInvenDetails == null) 
    {
        setTimeout(function () {filterSubGrid(); }, 2000);
        return;
    }

    //fetch xml code 
    var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
  "<entity name='new_inventoryworksheetdetail'>" +
    "<attribute name='new_name' />" +
    "<attribute name='new_warehouse' />" +
    "<attribute name='createdon' />" +
    "<order attribute='new_name' descending='true' />" +
    "<filter type='and'>" +
      "<condition attribute='new_warehouse' operator='eq' value='"+entityId+"'"+"/>" +
    "</filter>" +
  "</entity>" +
"</fetch>";
    if (ReqInvenDetails.control != null) 
    {
        ReqInvenDetails.control.SetParameter("fetchXml", fetchXml); //set the fetch xml to the sub grid   
        ReqInvenDetails.control.refresh(); //refresh the sub grid using the new fetch xml
    } 
    else 
    {
        setTimeout(filterSubGrid, 500);
 
 }
}


  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Filter Subgrid records

    I'm gave this a try but it's not propagating correctly still..

    function dynamicallyFilterSubGrid() {

    //RelatedInventoryWorksheetDetails : is name of subgrid given on Form. 
    var objSubGrid = document.getElementById("InventoryWorksheetDetail");

    //CRM loads subgrid after form is loaded.. so when we are adding script on form load.. need to wait unitil subgrid is loaded. 
    // thats why adding delay..
    if (objSubGrid == null || objSubGrid.readyState != "complete") {
        setTimeout(dynamicallyFilterSubGrid, 2000);
        return;
    }

    else {
        //when subgrid is loaded, get category value
        var category = Xrm.Page.getAttribute('new_warehouse').getValue();

        //Create FetchXML for sub grid to filter records based on category
        var FetchXml = "<fetch distinct='false' mapping='logical' output-format='xml-platform' version='1.0'>" +
                            "<entity name='new_inventoryworksheetdetail'>" +
                                "<attribute name='new_name'/>" +
                                "<attribute name='new_navnumber'/>" +
                                "<attribute name='new_warehouse'/>" +
                                "<attribute name='new_quantity'/>" +
                                "<attribute name='new_count'/>" +
                                "<attribute name='new_cost'/>" +
                                "<attribute name='new_itemnumber'/>" +
                                "<attribute name='createdon'/>" +
                                "<order descending='false' attribute='new_name'/>" +
                                "<filter type='and'>" +
                                    "<condition attribute='new_warehouse' value='" + category + "' operator='eq'/>" +
                                 "</filter>" +
                            "</entity>" +
                        "</fetch>";

        objSubGrid.control.SetParameter("fetchXml", FetchXml);

        //Refresh grid to show filtered records only. 
        objSubGrid.control.Refresh();

    }
}

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Filter Subgrid records

    Not exactly,

    The subgrid is on InventoryWorksheet. Needs to show records of InventoryWorksheetDetail if the warehouse value in warehouse lookup is equal in both of those entities.

    If (InventoryWorksheet) warehouse lookup = (InventoryWorksheetDeatail) warehouse lookup

    fetchXML to subgrid on the InventoryWorksheet (named: InvWsDSg)

  • Suggested answer
    Alagunellaikumar Profile Picture
    Alagunellaikumar 6,210 on at
    RE: Filter Subgrid records

    Hi

    Do you want subgrid in the ware house form for InventoryWorksheet  and InventoryWorksheet Detail?

    If this is a case then you have to put two subgrid in the wareHouse entity form for InventoryWorksheet and InventoryWorksheet Detail.

    In that subgrid you have to choose, related record type in the lookup.

  • Suggested answer
    Nithya Gopinath Profile Picture
    Nithya Gopinath 17,074 on at
    RE: Filter Subgrid records

    Hi,

    Refer this link.

    www.bobylog.com/microsoft-dynamics-crm-2015-subgrid-lookup-filter

    Hope this helps you.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans