I know a lot of my content is focused around D365FO but I also have started working with D365BC endpoints and wanted to give some things I ran into (and how I solved them) while developing external integrations. In my tests below I utilized Postman to query endpoints I created in D365BC, using this approach we can see the raw data returned from the endpoints and take out any possibility of having coding issues that introduce issues.
Virtual Tables/Fields are Not Available to OData Endpoints
If you try to create an OData endpoint that includes a virtual table or virtual field you will receive an ‘object reference’ error. For example, if I use the AllObj table and create an API query that looks like this:
You will get an error:
This has actually been reported to Microsoft and they do not appear to be working on a fix for this natively: https://github.com/microsoft/AL/issues/4248
In our case, we ended up converting this query to a page which also allows you to create an OData endpoint but works with virtual tables.
OData OrderBy Method Not Available on Query Endpoints
Let’s say you create a query object that returns a list of customers:
But now, let’s say I want to place an orderby parameters on the OData query to return the customers in order by their name. If you try this you will get this error:
In this case again, the fix is to create this query on a page object. The downside with this approach is that you lose the ability to perform complex joins that you get from the query object. So you may need to pre-populate a table with the data in the form you would like to return and use that as the datasource for the page.
No Way to Perform a ‘Does Not Contains’ OData Filter
I utilized this Microsoft document heavily while doing OData development: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expressions-in-odata-uris
It lists all of the possible OData methods you can utilize in the $filter parameter of your call. You can see that there is a built in ‘contains’ method but there is no ‘does not contains’ method. I’ve actually ran into this before on the D365FO side, one way around this is to utilize the ‘indexOf’ method which you can then check if the indexOf a particular character string exists and if it doesn’t then you know that object value does not contain that string and should be returned.
Unfortunately when you try to utilize indexOf you are met with this error, in the query below I am looking for customer’s whose names do not contain the letter ‘a’:
In this case, there really isn’t a workaround that you can implement in AL code or in the OData query. The only way to really implement this is to export this data to Excel and perform this filtering there.
Conclusion
I hope you found this post helpful. My goal for this post was not just to point out ‘flaws’ or ‘issues’ with D365BC OData endpoints but to hopefully stop someone from running into the same issue I did, or if you do to provide a possible solution to help alleviate the problem. I look forward to writing about more technical D365BC content in the future.