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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Sales forum / Dynamics 365 Related T...
Sales forum

Dynamics 365 Related Table Filtering

(0) ShareShare
ReportReport
Posted on by Mark Holtham

Hi,

 

I've started to move existing report queries from "Tenancy/XRMServices/2011/Organization.svc" to  OData 9.0 and finding that existing related table filtering is not working. I suspect it is a syntax issue but cannot find a workable solution online.

 

In my example I am trying to get order lines relating to a specific data in the order header. There are is a custom date field I normally reference which differs from the main created date but this does not work either

 

 

Source = OData.Feed("<tenant>.api.crm.dynamics.com/api/data/v9.0/salesorderdetails?&$expand=salesorderid&$filter=salesorderid/createdon gt 2016-11-01

 

Any chance someone can advise me on URL filtering for this scenario?

  • Suggested answer
    Ben Thompson Profile Picture
    Ben Thompson 6,350 on at

    Download the Rest API solution from www.toplinestrategies.com/.../crm-rest-builder-useful-tool-working-crm-2016’s-web-api and use that to generate and test your queries....

  • Mark Holtham Profile Picture
    Mark Holtham on at

    I'm comfortable with getting data from Dynamics 365 and making efficient URL queries. The thing that has me stumped at the moment is a Microsoft engineer asking me to move to the API 9.0 REST URL. All of my queries work with the exception of cross table referencing. For some reason I cannot seem to locate the correct syntax for the query using 9.0.

    I'd rather not use a tool. Does anyone know the method required to filter by related tables?

  • Mark Holtham Profile Picture
    Mark Holtham on at

    My apologies for the lack of key information.  This is specifically in regards to querying data via Power BI.  The forum admins asked me to come here and I copied and pasted my question without updating with relevant reference to Power BI.

  • Suggested answer
    Ben Thompson Profile Picture
    Ben Thompson 6,350 on at

    And I will repeat my post above - the best way of ensuring your query works correctly is to use a tool that helps you write the query and allows you to test it. For webapi the tool I use for writing complex queries is the Rest API solution I referenced above.

  • Mark Holtham Profile Picture
    Mark Holtham on at

    I've taken a look at the tool and it is very handy but I've encountered the same issue, the tool does not allow the user to filter by value found in the related entity. Switching to 2011 allows filtering, just not the REST API route. I assume this is not an available feature outside of XRMServices/2011?

    In Power BI, I am trying to achieve a REST API 9.0 query that emulates this:

    OData.Feed("https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/InvoiceDetailSet?&$expand=invoice_detail&$filter=invoice_details/CreatedOn gt datetime'2018-06-01'")

    With REST I can expand and select the date from the invoice header but I wouldn't want to download all records to filtering later on. This is my close equivalent:

    OData.Feed("<tenant>/.../invoicedetails$select=_invoiceid_value,_productid_value,quantity&$expand=invoiceid($select=createdon)

    I have read a few Microsoft resources and found a nice way around the problem.  Using a system or user saved view, without any columns relating to other tables, I can pull data in to Power BI using a Dynamics 365 query.  msdn.microsoft.com/.../mt607533.aspx.  I appreciate there are downsides to this, especially when considering that queries in Dynamics can now influence Power BI reports.  I'll be happy to hear alternative methods if anyone has one?

  • Suggested answer
    Ben Thompson Profile Picture
    Ben Thompson 6,350 on at

    It's worth taking a step back to see why you've been asked to do what you've been asked to do...

    The reason for moving away from the old Soap interface is that it is deprecated and will disappear at some point in the near future.

    Equally in the near future the CDS for analytics will arrive and that will fix the long term issue your problem at the moment is that that CDS interface is currently only in private preview...

     

    There is however, nothing stopping you sending your 2011 soap  fetchxml queries via the webapi see www.powerobjects.com/.../using-fetchxml-with-the-new-dynamics-web-api . I've done that in the past...

  • Mark Holtham Profile Picture
    Mark Holtham on at

    Thank you for the assistance Ben.

    I guess that means that using Power BI will require occasional query maintenance overtime if we want to ensure decent performance with Dynamics 365.

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Sales

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans