web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / OData query to get som...
Microsoft Dynamics CRM (Archived)

OData query to get some parent and grandparent fields

(0) ShareShare
ReportReport
Posted on by Community Member

I'm new to OData and want to know if something is possible with a single REST query.

Imagine we have an Account (in AccountSet) and in there some Contacts (in ContactSet) and within these contacts we have some custom SubAccount (in a custom SubAccountSet).

So for a given SubAccount there's a parent Contact and for that there's a parent Account.

So far so good.

Now I want to get the following:

1. All SubAccount records satisfying some simple "eq" condition (e.e. some_field == null or something)

2. Return just some of the fields - not everything in the SubAccount object.

3. Also some fields from the parent Contact record. (there's a key field in the SubAccount)

4. Also some fields from the Contact's parent Account record. (there's a key field in the Contact).

Is this doable or must I develop several distinct queries and run them in succession (get all SubAccounts, then for each run another query to get parent and so on).

Could $expand be of use here?

Any help much appreciated!

Korp.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    kyleknab Profile Picture
    kyleknab 517 on at

    The $expand parameter can only query one level deep (1:N or N:1); it does not support querying a mutilevel relationship property.

    It will reduce your query count by 1 though!  You can get Parent Contact fields in your SubAccount query (via $expand); but you'll have to run a second query to get anything from the Parent Contact's parent Account record.

  • Community Member Profile Picture
    Community Member on at

    Hi

    Thanks for the info on one level, most helpful!

    Now what would the syntax actually be though? I'm struggling to internalize this quickly, been reading this

    stackoverflow.com/.../odata-combining-expand-and-select

    But still not quite mastering it - I'm playing around in a browser with a REST tool

    Thx

  • Suggested answer
    kyleknab Profile Picture
    kyleknab 517 on at

    Syntax involves specifying the relationship names in your $expand parameter, and then specifying <relationship name>/<field name on related record> for each field you want in the $select parameter

    For example this query will give you the first account in your system, along with all fields from its primary contact:

    /XRMServices/2011/OrganizationData.svc/AccountSet?$top=1&$expand=account_primary_contact&$select=Name,account_primary_contact

    But to get just the new_parentaccountid field from that primary contact, use a "/<fieldname>" in the $select parameter:

    /XRMServices/2011/OrganizationData.svc/AccountSet?$top=1&$expand=account_primary_contact&$select=Name,account_primary_contact/new_parentaccountid

  • Community Member Profile Picture
    Community Member on at

    Thanks again, much appreciated.

    I can see how "parent" records refer to sets of child records, but given a child how would I refer to (and $expand) its parent?

    e.g. how would one start with contact-id and get at the parent account record?

    Thx

  • Verified answer
    kyleknab Profile Picture
    kyleknab 517 on at

    Same way.  The example I gave is a child record (account) connected to its parent via lookup field (primary contact).

    To get at any related record (parent or child), use the relationship's schema name in the $expand and $select parameters.

  • Community Member Profile Picture
    Community Member on at

    Thanks Kyle, I was just reading this:

    www.asp.net/.../using-select-expand-and-value

    This seems to be saying there is some scope for > 1 level of expansion, but I'm confused - you guys know more than I so am I missing something fundamental here?

    Thx

    (PS: I guess the level 1 limit is just a Dynamics limitation on their implementation of OData querying)

  • Verified answer
    kyleknab Profile Picture
    kyleknab 517 on at

    Your PS is correct. The page you linked to mentions that the default maximum expansion depth is 2.  CRM's oData maximum expansion depth is basically 1.

    The documentation at your link is very useful, but the success and capability of any oData queries you write will depend on the system publishing those web services.

    CRM doesn't support all aspects of the oData protocol so we have to take MSDN at face value when it says

    "Microsoft Dynamics CRM 2015 and Microsoft Dynamics CRM Online 2015 Update doesn’t support querying a multilevel relationship property. For example, this query, intended to retrieve information about the owning user of opportunities related to accounts, won’t work: /AccountSet?$expand=opportunity_customer_accounts/opportunity_owning_user."

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans