CRM web api expand along with filter - inner join or left join?

Question

I'm trying to use web api query and without fetchxml, I was able to build this endpoint but the resultset is behaving like LEFT OUTER JOIN, but I need INNER JOIN.

new_demo is having lookup of new_currentappointment (most recent record is captured in here from a subgrid list), new_currentappointment is having new_user lookup.

I want the list of new_demo with new_currentappointment_lookup where new_user_lookup is the filter.

https://crmdev.crm.dynamics.com/api/data/v9.1/new_demo?$select=new_attribute_one&$expand=new_currentappointment_lookup($select=new_attribute_two;$filter=_new_user_lookup_value eq <guid>)

The result is bringing the every single new_demo in the system, but the expand filter only results null. How to eliminate the filtered null result from expanded entity in main result?

"value": [
    {
      "@odata.etag": "W/\"608177550\"",
      "new_attribute_one": "Demo 1",
    
      "new_currentappointment_lookup": {
        "new_attribute_two": "testing comments",
        "_new_user_lookup_value": "guid",
      },
    },
    {
      "@odata.etag": "W/\"608177790\"",
      "new_attribute_one": "Demo 2",
      
      "new_currentappointment_lookup": null,
    }
  ]

This result explained in ASP.NET web api documentation is what I'm looking for, but I could not find one for Dynamics CRM web api. Any other easy way I'm missing?

Discussion continuing on github issue - github.com/MicrosoftDocs/powerapps-docs/issues/1692 — Arun Vinoth PrecogTechnologies
– Arun Vinoth PrecogTechnologies, Commented Aug 25, 2020 at 13:51

Jim Daly -MSFT- · Accepted Answer · 2020-08-26 21:28:35Z

3

From Doc feedback: CRM web api expand along with filter - inner join or left join?

This is the equivalent query to your scenario:

{{webapiurl}}incidents?$select=title
&$expand=customerid_account($select=name;
$expand=primarycontactid($select=fullname;
$filter=contactid eq '384d0f84-7de6-ea11-a817-000d3a122b89'))

The $filter just controls whether the record is expanded or not.

It is the difference between this:

{
    "@odata.etag": "W/\"31762030\"",
    "title": "Sample Case",
    "incidentid": "d3d685f9-cddd-ea11-a813-000d3a122b89",
    "customerid_account": {
        "name": "Fourth Coffee",
        "accountid": "ccd685f9-cddd-ea11-a813-000d3a122b89",
        "primarycontactid": {
            "fullname": "Charlie Brown",
            "contactid": "384d0f84-7de6-ea11-a817-000d3a122b89"
        }
    }
}

And this:

{
    "@odata.etag": "W/\"31762030\"",
    "title": "Sample Case",
    "incidentid": "d3d685f9-cddd-ea11-a813-000d3a122b89",
    "customerid_account": {
        "name": "Fourth Coffee",
        "accountid": "ccd685f9-cddd-ea11-a813-000d3a122b89",
        "primarycontactid": null
    }
}

See this example (Ctrl+F "Nested Filter in Expand") where all People are returned but only the Trips matching the $filter are expanded.

So the $filter behavior in the expansion will just control whether the detail will be returned or not. It isn't a INNER JOIN behavior. You will need to use FetchXml to achieve this.

edited Aug 26, 2020 at 21:28

answered Aug 25, 2020 at 1:12

Jim Daly -MSFT-

2571 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arun Vinoth PrecogTechnologies Over a year ago

Yeah, similar structure. If this can be tested with that schema as well it may behave same. You want me to check?

Arun Vinoth PrecogTechnologies Over a year ago

Thanks, that clarified it as the expected behavior. And FetchXml is the only way for inner join.

Polshgiant · Accepted Answer · 2021-09-22 19:40:42Z

2

It may not have been possible before, but it IS now possible to get INNER JOIN behavior via the Web API for a relationship one level deep by adding a top level filter against the navigation property. For your example, it would look like this:

https://crmdev.crm.dynamics.com/api/data/v9.1/new_demo?$select=new_attribute_one&$filter=(new_currentappointment_lookup/_new_user_lookup_value eq <guid>)&$expand=new_currentappointment_lookup($select=new_attribute_two)

A great place for help creating Web API queries is the FetchXML Builder tool in the XrmToolbox. Create your query in fetch and then in the View menu enable the OData 4.0 option and it will show you the equivalent Web API query (if it is possible).

answered Sep 22, 2021 at 19:40

Polshgiant

3,6741 gold badge25 silver badges27 bronze badges

2 Comments

Paul F Over a year ago

This needs more upvotes. I thought I was going to have to rewrite a portion of my PCF to use FecthXml instead but this is a godsend.

WhatsUp Over a year ago

This still doesn't behave like INNER JOIN. If there are no matching rows for new_currentappointment_lookup/_new_user_lookup_value eq <guid>, I still get the record in the result.

Collectives™ on Stack Overflow

CRM web api expand along with filter - inner join or left join?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related