web
You’re offline. This is a read only version of the page.
close
Skip to main content
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
    AliAdamu Profile Picture
    AliAdamu on at
    Using OData $filter query option inside $expand - D365FO
    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>
     
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,570 Super User 2025 Season 2 on at
    RE: Using OData $filter query option inside $expand - D365FO

    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.

  • RomilM Profile Picture
    RomilM 25 on at
    RE: Using OData $filter query option inside $expand - D365FO

    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.

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,570 Super User 2025 Season 2 on at
    RE: Using OData $filter query option inside $expand - D365FO

    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)

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 1,099

#2
André Arnaud de Calavon Profile Picture

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

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 215 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans