WEB API - Filtering on nested navigation property

(0) Share

Report

Posted on by openclue

Hi,

Having such of Dataverse table model:

EntityA--navigation property x -->EntityX--navigation property y-->EntityY with column 'address'

is it possible to query for EntityA but filter on address from EntityY.

I tried this way:

...&$filter=x\y\name eq 'some value'

...&$filter=contains(x\y\name, 'some value')

In both cases I get an error:

 "error": {
 "code": "0x80040216",
 "message": "entityRelationshipRole for given navigation property not found"
 }

....

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Ram Prakash Duraisamy 5,699 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hello @openclue,

You can try below code

https://[base URL]/api/data/v9.1/Entity1/
?$expand=Entity2($filter=Entity2FieldName eq fieldValue;$select=Entity2FieldNames)

Please Mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
openclue 35 on at

Like (0)

Report
Copy link

Link copied!

Hello @rampprakash

I've tried it before.
This will filter the "entities2" to those meeting the filter condition but return all "entities1" (this will filter the entities2 to those meeting the condition but return all entities1 with some nulls).

BTW - it's easy to filter by entity2 fields - I need to filter out entities1 by entitys3 attribute value:
(Entity1-->Entity2-->Entity3.FieldName)

Let's say we have:

Order->OrderLine->Product

I need a way to query for orders with the specified barcode (Product's field).

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (1)

Report
Copy link

Link copied!

@openclue

I think multi level expand is possible only for entity which has lookup that starts with N:1. Included reference link below,
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/retrieve-related-entities-query#multi-level-expand-of-single-valued-navigation-properties

If you want to navigate from 1:N only one level of depth is possible. You can find examples from above link.

Are you trying to connect dataverse from client? If not, I would recommend to use fetchXML.

If it answers your question, kindly give kudo and accept it as solution.

Regards,
Prakash

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,699 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @openclue,

Cool then we need to use multiple web API calls to make it work is that okay for you?

Else we can use FetchXml calls what you prefer ?

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @openclue,

Unfortunately with OData don't support "inner join" type of behavior like in SQL so you won't be able to filter entity A with entity C filter. There are 2 lambda expressions available (any and all), but you can't use them in an $expand. The only option is to use FetchXml with link-entity in Web API. Here's an example:

<fetch> <entity name='entityA'> <attribute name='columnA'/> <link-entity name='entityB' alias='entityB' to='entityBid' from='entityAid' link-type='inner'> <attribute name='columnB'/> <link-entity name='entityC' alias='entityC' to='entityCid' from='entityBid' link-type='inner'> <attribute name='columnC'/> </link-entity> </link-entity> </entity> </fetch>

then after encoded it pass it as a fetchXml= parameter.

[Organization URI]/api/data/v9.2/accounts?fetchXml=%3Cfetch%20mapping%3D%27logical%27%3E%3Centity%20name%3D%27account%27%3E%3Cattribute%20name%3D%27accountid%27%2F%3E%3Cattribute%20name%3D%27name%27%2F%3E%3Cattribute%20name%3D%27accountnumber%27%2F%3E%3C%2Fentity%3E%3C%2Ffetch%3E

More info: https://docs.microsoft.com/powerapps/developer/data-platform/webapi/retrieve-and-execute-predefined-queries#use-custom-fetchxml
Hope this helps!

Was this reply helpful? Yes No
openclue 35 on at

Like (0)

Report
Copy link

Link copied!

@EricRegnier @rampprakash @Prakash4691

Thank you all.

I need to take a closer look at fetchXml then...

Was this reply helpful? Yes No