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 / Supply chain | Supply Chain Management, Commerce / Using OData $filter qu...
Supply chain | Supply Chain Management, Commerce
Suggested Answer

Using OData $filter query option inside $expand - D365FO

(0) ShareShare
ReportReport
Posted on by RomilM 25

Hi Community,

I am trying to filter an OData expand query.

You can use the following query as a test sample on any environment with the standard SalesOrderHeaderV2 entity.

https://[BaseURL]/data/
SalesOrderHeadersV2?$expand=SalesOrderLines($filter=LineNum eq 2;$select=SalesId,LineNum,ItemId)

The above URI should essentially return all sales orders and the sales lines with linenum = 2. The returned fields for sales lines should only include SalesId, LineNum and ItemId

Whenever i try to run the above, Dynamics does everything apart from the filter query option inside of the expand.

I have validated the query option syntax.

https://docs.microsoft.com/en-us/dynamics-nav/using-filter-expressions-in-odata-uris

You could refer to the official OData documentation.

Reference.: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#_Toc31361039

I have the same question (0)
  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

    Hi Romil,

    You shared Odata documentation related to Dynamics NAV which might not be valid for Dynamics 365 SCM.

    In your URI you used non existing column names. Instead of the table field names, you have to use the data entity column names.

    SalesId > SalesOrderNumber

    LineNum > does not exist

    ItemId > ItemNumber

    The next URI is working for me (bit different example as there is no Line numbers for sales orders,

    https://[base URL]/data/SalesOrderHeadersV2
?$expand=SalesOrderLines($filter=ItemNumber eq 'D0001';$select=SalesOrderNumber,InventoryLotId,ItemNumber)

  • RomilM Profile Picture
    RomilM 25 on at

    Thank you for your response Andre.

    My bad for the field names and the link. I was using a custom entity with those field names based on the SalesTable and overlooked at the actual field names.

    Also the link, although different but has the same set of filter options.

    https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/odata

    The point i was trying to make is that the filter option inside the expand doesn't work for me. It results in all the records; completely disregards the filter.

    I tried the query you've shared as-is (replacing my D365FO URL) and that had the same problem. It completely ignores the filter option and returns all sales order/lines with all items.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

    Hi Romil,

    It could indeed have all sales orders as a result, the XML was a bit hard to read. It looks like it only exported lines for a sales order if the item was present. But then indeed, it did show all the lines for the sales order regardless the item ID filter.

    I haven't used the $expand option myself before. If you have a business scenario where you need it, then you can try to contact Microsoft support for some more help here.

  • Suggested answer
    AliAdamu Profile Picture
    AliAdamu on at
    The suggested answer is not correct. The $filter option is not currently supported within the $expand option. 
    This statement : https://[base URL]/data/SalesOrderHeadersV2?$expand=SalesOrderLines($filter=ItemNumber eq 'D0001';$select=SalesOrderNumber,InventoryLotId,ItemNumber) would return an error message.
    $select option is supported instead
     
    Expected error message:
    <Message>An error has occurred.</Message>
    <ExceptionMessage>Nested query options in $expand are not supported.</ExceptionMessage>
     

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 > Supply chain | Supply Chain Management, Commerce

#1
Siv Sagar Profile Picture

Siv Sagar 266 Super User 2025 Season 2

#2
Laurens vd Tang Profile Picture

Laurens vd Tang 213 Super User 2025 Season 2

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 140 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans