Monday 27 July 2015

Filter N:N Subgrid in CRM 2013 using Javascript

Sometimes, you need to filter the N to N subgrid (not associated view) when you click the Add Existing button and you will see the inline lookup

image

To achieve it, you might need to modify the system ribbon. Well, This method is unsupported because it is not well-documented, but if just in case customers really demanding and want it, I just want to share the ‘how-to’.

Thanks to my friend with this blog post which can convince me that this is not possible to filter subgrid, this is a clue:

http://nycrmdev.blogspot.sg/2014/02/changing-default-view-of-inline-lookup.html

So, this is the common function:

//FUNCTION:AddExistingSubgrid
function filterSubgrid(gridTypeCode, gridControl, entityParentName, subgridName, viewId, entityRelatedName, viewDisplayName, fetchXML, layoutXML) {
    var AGS = window["AGS"] || {};

    AGS.AddExistingSubgrid = function (gridTypeCode, gridControl) {
        Mscrm.GridRibbonActions.addExistingFromSubGridAssociated(gridTypeCode, gridControl);
        if (Xrm.Page.data.entity.getEntityName() == entityParentName &&
            //gridTypeCode == 2 &&
            typeof gridControl['get_viewTitle'] == 'function') {
            var lookupExistingFieldName = 'lookup_' + subgridName + "_i";
            var inlineBehaviour = document.getElementById(lookupExistingFieldName).InlinePresenceLookupUIBehavior;
            setLookupCustomView(inlineBehaviour, viewId, entityRelatedName, viewDisplayName, fetchXML, layoutXML);
        }
    };

    this.AGS = AGS;
    //call this function;
    AGS.AddExistingSubgrid(gridTypeCode, gridControl);
}

// FUNCTION: setLookupCustomView
//must pass the lookup field control, not field name
//to add custom view to the subgrid (Add existing)
function setLookupCustomView(lookupFieldControl, viewId, entityRelatedName, viewDisplayName, fetchXML, layoutXML) {
    // add the Custom View to the primary contact lookup control      
    lookupFieldControl.AddCustomView(viewId, entityRelatedName, viewDisplayName, fetchXML, layoutXML, true);
}


And this is the example how to use that and wrap it in one specific function that would be called during ribbon action execution (clicked)

function filterTrainerProfile(gridTypeCode, gridControl) {
    var entityParentName = "primaryentityname";
   
    // use randomly generated GUID Id for our new view      
    var viewId = "{6fd72744-3676-41d4-8003-ae4cde9ac282}";
    var entityRelatedName = "relatedentityname"; 
    var viewDisplayName = "Filtered View Name";

    var subgridName = "subgrid_name";
   
    var fetchXml = getFetchXML(); //replace this to your own fetch xml

    // build Grid Layout     
    var layoutXml = getLayoutXML(); //replace this to your own layout example

    if (fetchXml != null) {
        filterSubgrid(gridTypeCode, gridControl, entityParentName, subgridName, viewId, entityRelatedName, viewDisplayName, fetchXml, layoutXml);
    }
}

*I used the filterTrainerProfile() function to filter the Trainer profile entity subgrid records (as related record) then I put the subgrid in the Class form entity.

So, the Trainer profile will be my related entity name, while Class is my entity Parent Name.

Then using our friendly tool here, we can modify the system ribbon

image

use number 1 if you want to filter subgrid based on 1:N relationship and use number 2 if you want to filter for N to N relationship (well in my example is N to N)

Then, after that, modify the command:

image

Here you go for the detail command

image

And you can see the result, it would filter my Trainer Profile records based on the xml I defined!

*Note: It can break the associated view, you might need to add try catch or conditional to make sure it works if you really need the associated view. In my case, users do not want to use associated view because we have subgrid already.

I hope this can help you, it seems becoming most requirement recently I found in the forum and also other projects.

Thanks.

8 comments:

  1. Hi

    I am using this code to filter the records of sub grid on the form in MSCRM 2015 online. I did it the same way and put the function on Add existing (2) button as it for N:N. But it's not working. Please suggest.It's not filtering or showing even alerts in the function.

    ReplyDelete
  2. FetchXML you can test here online http://msxrmtools.com

    ReplyDelete
  3. FetchXML you can test here online http://msxrmtools.com

    ReplyDelete
  4. Does this solution work with CRM Dynamics 2015 Upgrade 1?

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. tell me what are those two parameters values you are passing...

    ReplyDelete
  7. Hi Aileen Gusni
    How can I do that https://community.dynamics.com/crm/f/117/t/216780 with the help of your post. May you please help me?
    Thank you

    ReplyDelete

My Name is..