Dynamics CRM Filter SubGrid with related data from N:N relationship

Question

I have an entity called "Project", it's related to "Account" with N:1 relationship.

I have a N:N relationship between "Project" and "Contact". So for every project I can add many contacts.

Now I have a SubGrid that shows a list of contacts for the project and add existing one.

The problem is: when I use the Search lookup to look for contacts, I get all contacts from the system

What I need is: to get just the contacts from the Account related to the project.

In other words: for every project I want to add many contacts from the account related to the project

Answer 1

You need to add a custom filter to the look up control.

var fetchXml = "<filter type='or'>"; //or 'and' depending if you want more conditions...
fetchXml += "<condition attribute='new_contactAccountField' operator='eq' value='" + yourAccountid + "'/>"; //new_contactAccountField is the field on your contact that links it somehow to the account...
fetchXml += '</filter>';

Xrm.Page.getControl('new_yourContactLookupField').addPreSearch(function () {
    Xrm.Page.getControl('new_yourContactLookupField').addCustomFilter(fetchXml);
});

Answer 2

You can filter your subgrid lookup with this:

this.navigateTo = function () {
        var account = getAccountId();

        //define data for lookupOptions
        var lookupOptions =
        {
            defaultEntityType: "project",
            entityTypes: ["project"],
            allowMultiSelect: true,
            searchText: " "
        };

        if (account !== null) {
            lookupOptions.filters = [
                {
                    filterXml: `<filter type='and'><condition attribute='accountid' operator='eq' value='${account}'/></filter>`,
                    entityLogicalName: "project"
                }
            ];
        }

        // Get account records based on the lookup Options
        Xrm.Utility.lookupObjects(lookupOptions).then(
            function (projects) {
                console.log(projects);
            },
            function (error) {
                console.log(error);
            }
        );
    }

Don't forget to use Ribbon Workbench and add event listener to your "Add Existing Button". I hasten to upset you right away that this navigation after clicking on "add" does not add entities, but only gives you the list that you needed. Unfortunately, I do not have the code for adding new entities to the subgrid.