[SOLVED] Filtering across multiple Dynamics entities in Common Data Service

Filtering across multiple Dynamics entities in Common Data Service

I'm trying to query our Dynamics 365 CRM through the Common Data Service / Microsoft Dataverse. I can manage this when I need to just filter on fields that exist within the entity type I'm searching. But I also need to filter on a different linked entity.

So far I can query on the contact entity just fine:

var contacts = svc.Get("contacts?" +
     "$select=firstname,lastname,address1_name,address1_line1," +
     "address1_postalcode,emailaddress1,statecode,_parentcustomerid_value&" +
     $"$filter=contains(firstname,'{firstName}') and " +
     $"contains(lastname, '{lastName}')"
    , formattedValueHeaders);

And this returns a list of contacts which match the filter.

However, I also need to filter on the Account Name that the contact is linked to. If I had the AccountId value then I could do this by adding $"contains(_parentcustomerid_value, '{accountId}')" to the filter. But I can't use this as the input data I'm receiving to query on is the name of the account and not an Id value.

Any ideas on how to do this?

Currently, the only way I can see is to use my existing query, then to loop through each contact and check the returned account name in the response, and if this matches then in effect I've filtered it. But I would prefer to be able to have the whole filter all in a single query.

Solution

You can use this to filter based on lookup field name value.

https://devorg.crm.dynamics.com/api/data/v9.1/contacts?$filter=parentcustomerid_account/name eq 'test'