Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I've been told by an engineer to move to the Dynamics 365 9.0 api for Power BI queries but I'm finding that I receive an error when filtering by date. I suspect it is a formatting issue because I can filter by text field but not by date.
I've tried this with various date formats but getting the error below:
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq datetime'2018-01-01'")
Error
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2018-01-01'' at '13' in 'createdon gt datetime'2018-01-01''.)
Any chance someone can point me in the right direction?
In case it helps, the original query was: https://<tenant>.api.crm.dynamics.com/XRMServices/2011/Organization.svc/InvoiceSet?$filter=CreatedOn gt datetime '2018-01-01'
Mark
Solved! Go to Solution.
@Mark_Holtham,
Change your code to the following:
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq 2018-01-01 ")
Reference:
https://msdn.microsoft.com/en-us/library/mt628816.aspx
Regards,
Lydia
@Mark_Holtham,
Change your code to the following:
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq 2018-01-01 ")
Reference:
https://msdn.microsoft.com/en-us/library/mt628816.aspx
Regards,
Lydia
Thank you, that worked. Thats the one variation I didnt try!
One version of this issue I still have though relates to cross table filtering by date. For example I have the similar date filtering requirement for saleorderdetails where I want to filter only the ones where the salesorder has a specific date.
Normally I would expand and then filter but this does not seem to be supported anymore.
Source = OData.Feed("<tenant>.api.crm.dynamics.com/api/data/v9.0/salesorderdetails?&$expand=salesorderid&$filter=salesorderid/createdon gt 2016-11-01
Any chance you can point me in the right direction for this one as well?
Thanks