Uncategorized

AX RTW – My ODATA and JSON journey – Part I

Learn the word; ODATA. We will hear a lot of ODATA in the future, because it will change the way we integrate and how we exchange information between AX and other systems. A good starting point is the AX help wiki, that Kuntal Mehta created. I have decided to explore what the ODATA can do, and wanted to write a bit about my journey. Instead of trying to explain all technical details of data entities and how the architecture is, then let us rather just test something J

What you need to test what I’m doing is

  1. An AX RTW environment deployed from LCS
  2. Internet explorer
  3. Good old notepad

Step1: What services is available?

To get all entities available to you use your Site address, and add “/data” at the end.

Then save the file you receive, and open it in notepad. (I have associated *.json with notepad). The file you get looks like this:

Each line here represents a data entity service we can use. The format of this is the JSON format, but that is not important now.

Step2: Show me the customers

In the file you may find that there is an entity/schema named “Customers“. I can therefore just add the “/data/Customers” to my URL

And then I get a JSON file of all the customers;

But this is a bit “cloudy” and I can further filer down what I want. Let’s say I just want to see all customer names. I can then add “/data/Customers/?$select=Name” to my URL

Now it returns a JSON file with only the Name.

If I wanted to add one more column, like the Payment terms, the syntax would look like “/data/Customers/?$select=Name,PaymentTerms“, but this would not work because the comma cannot be used on a URL. I therefore need to replace the comma with %2C, that is the URL representation of comma. For multiple columns I therefore add “/data/Customers/?$select=Name%2CPaymentTerms

You see some strange “@data.etag”, and here is an explanation. It is for caching.

Step3: Can I read this in Excel?

Yes. Excel can import OData, and format it like we would.

Then fill in the /data URL, select schema, and then select fields.

And then you may read directly into Excel all entities made available in AX RTW, even without the AX connector.

Step5: Show me all !

Sure. Try to add the “/data/$metadata“, and AX return All schemas, fields and relations. It take a long time, but nice to explore.

Step6: Can we use DIXF to import directly from OData feeds ?

This is what I would love to see. But I have not found it yet.

Happy DAX’ing 😉

I appreciate that I can be a part of this worldwide blog community—as a consultant working from Norway, the blog lets me share more than 20 years of experience with Microsoft Dynamics 365. Along the way, I participated in developing retail, PDA/RF, barcoding, master data, kitting and WMS-solutions for Dynamics. My blog focuses on my deepest interests and expertise: along with a 360 degree view of digital transformation topics, I welcome opportunities to dive into retail and intercompany supply chain automation, logistics, and production—everything that is moving around in a truly connected enterprise. As Enterprise Architect on Dynamics 365, I specialize in strategic development and planning for corporate vertical solutions and works to build international networks that increase knowledge and understanding for Dynamics 365. As an advocate for both providers and customers, I'm committed to ensure that customers constantly changing needs are meet, and I see community as key for increasing expertise. I welcome you to connect with me.

9 comments on “AX RTW – My ODATA and JSON journey – Part I

  1. Hello Kurt – Thanks for the nice post. I started all the steps, when I stuck to Step 2 where my local VM URL: https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/bankaccounts is giving 404 error. I tried /customers or other entities as well but all of them are giving 404. Would you please help to get past this error? Anybody else?

    Like

  2. Unfortunately Dynamics AX does not support CORS, so OData is useless if you are creating a browser/client application unless you proxy all requests through a CORS-capable proxy.

    Like

  3. Peter Nagy

    Hi Kurt,
    It is a useful post and works well. I’m trying to pull out data from a specific legal entity from one of my AX instance. Do you know the syntax of specifying a legal entity name with OData? My goal is to pull out data from AX (e.g. pull out Customers from Contoso USRT company only) to PowerBI with OData.

    Thanks,

    Peter

    Like

  4. Varun Shah

    Hi,

    The updatable or isPublic property is not visible to edit on any view. Can you help me

    Please

    Like

  5. Johan Vermeulen

    What’s your opinion on performance? Would aggregate entities, exposed as OData, be suitable to export millions of lines into a PowerBI file?

    Like

  6. So glad MS is now using REST/JSON/ODATA. Looking forward to see how Swagger will play with it. I’m sure it is already possible in VS. Or maybe somebody will develop a plugin.

    Liked by 1 person

  7. Nice post Kurt… Keep them flowing in.. Thanks.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: