Business Central Generic API Hack

Views (30)

JAngle

133

Like(0)

Report

What exactly do I mean by API hack? You will note by now if you want data out of BC an API is the typical route. If the standard API’s don’t cover it you need one developing or something from AppSource. Is there another way? This is what I’ll look to uncover in the blog post. Bold promise that might go undelivered? Maybe….

Note, that I’m ignoring publishing a web service because you might not have all the fields you want.

Disclaimer: This is just to save me time when building demos. Can I see it working in production? Would be to limited use cases. Possibly as part of an Excel layout, to aid with Power BI report or a specific Power Automate flow. This does not enable full CRUD, it is only for Reading data and not all of it.

The idea comes from reading this article: Create_Unbound_OData_Action which is great, but the output looks weird, and you have to use a POST command with HTTP. Below is an example of the output this type of action would give you. In principal the format isn’t that big of a deal – it is more about the need to POST. Apparently there is a way that Power BI could handle it, but it’s not a clean and simple method. What we want is a GET based HTTP command so things can work across many platforms in a simple and easy fashion.

//Example of the output from using a bound or unbound OData action with BC
{
    "inputJson": "{\"str\":\"Hello world!\",\"confirm\":true}"
}

What have I done about this? I’ve used some logic I mentioned a while back in this blog: Previous_Blog

The summary version of what I wrote about is having a text field on a API page which gets a Base64 version of the item image. So, can you apply this principal to other pieces of data? Well this blog would be very short if not.

Loosely using the configuration package logic I built a few tables and pages to house what data I want to expose and then pick up for later use:

Included the ability to select which fields you want to see and of course to filter on too.

Note that this uses a **GET** command and you can filter for the table you setup in BC. The resulting JSON from this will be formatted as a text but it can be presented correctly in Power Query or Power Automate for example. Here is a view of the output for table 18 aka Customer:

{
    "@odata.context": "https://api.businesscentral.dynamics.com/v2.0/{{tenantID1}}/Sandbox/api/ja/jagrp/v2.0/$metadata#endpointers",
    "value": [
        {
            "@odata.etag": "W/\"JzE5OzYwMjY4NzkwMTI2ODU1NzYxMzkxOzAwOyc=\"",
            "tableID": 18,
            "JsonResponse": "{\"Customer\":[{\"No\":\"10000\",\"Name\":\"Adatum Corporation\",\"Address\":\"Station Road, 21\",\"Address_2\":\"\",\"City\":\"Cambridge\",\"Contact\":\"Robert Townes\",\"Credit_Limit_LCY\":\"150000\",\"Customer_Posting_Group\":\"DOMESTIC\",\"Payment_Terms_Code\":\"1M(8D)\"},{\"No\":\"20000\",\"Name\":\"Robert Townes\",\"Address\":\"Station Road, 21\",\"Address_2\":\"\",\"City\":\"Cambridge\",\"Contact\":\"Helen Ray\",\"Credit_Limit_LCY\":\"1\",\"Customer_Posting_Group\":\"DOMESTIC\",\"Payment_Terms_Code\":\"14 DAYS\"},{\"No\":\"30000\",\"Name\":\"Trey Research\",\"Address\":\"Southwark Bridge Rd, 91-95\",\"Address_2\":\"\",\"City\":\"London\",\"Contact\":\"Meagan Bond\",\"Credit_Limit_LCY\":\"2\",\"Customer_Posting_Group\":\"FOREIGN\",\"Payment_Terms_Code\":\"CM\"},{\"No\":\"40000\",\"Name\":\"Alpine Ski House\",\"Address\":\"Walter-Gropius-Strasse 5\",\"Address_2\":\"Park Stadt Schwabing\",\"City\":\"Munchen\",\"Contact\":\"Ian Deberry\",\"Credit_Limit_LCY\":\"3\",\"Customer_Posting_Group\":\"EU\",\"Payment_Terms_Code\":\"1M(8D)\"},{\"No\":\"50000\",\"Name\":\"School of Fine Art\",\"Address\":\"10 High Tower Green\",\"Address_2\":\"\",\"City\":\"Miami\",\"Contact\":\"Jesse Homer\",\"Credit_Limit_LCY\":\"4\",\"Customer_Posting_Group\":\"DOMESTIC\",\"Payment_Terms_Code\":\"14 DAYS\"}]}"
        }
    ]
}

How do I sort out the format in Power Query or Power Automate? The latter has a few extra steps but for completeness here is both explained.

Get the data for use in Power Query:

Start a new query for OData and paste in the URL for the API page. You can even include a filter and skip out the encoding. So, for example you could add https://api.businesscentral.dynamics.com/v2.0/<YourTenantID>/Sandbox/api/ja/jagrp/v2.0/companies(<YourCompanyID>)/endpointers?$filter=tableID eq 18

It will pull back what looks to be a perfectly shaped JSON object. However, it is just showing a text value without the \ characters we saw earlier.

To resolve use the “Parse” dropdown from the transform tab and choose JSON

That will make it be a record like above and we can then unfold this to get the actual data we want from inside it to do that simply tap on the double arrow icon on the JsonResponse column.

This will show the table we pulled data from and we can click OK.

The formula bar shows the steps taken thus far. We want to then expand again to pull out all the rows within the table. On this occasion it is the customer.

The final expansion will reveal all of the fields we exposed. Note, that you may need to tap the “Load More” to reveal all of them. Once done you will have a data set to use in Excel or Power BI.

**Get the data for use in Power Automate**

This felt less obvious but was simply a case of understanding the make up of the JSON that was prepared. I’ve reduced the example JSON result you might get back and highlighted in orange the crucial bits. The bit we want is the JsonResponse but we have to get that via the “value” part of the JSON.

{
    "value": [
        {
            "@odata.etag": "W/\"JzE5OzYwMjY4NzkwMTI2ODU1NzYxMzkxOzAwOyc=\"",
            "tableID": 18,
            "JsonResponse": "{}"
        }
    ]
}

Use a parse JSON action and have the input as the entire Body. The schema shown has been generated from pulling some data down and pasting in the example payload. Note, that you would need to do this per table as the the response includes the table name.

Following on from the previous action you will need another parse JSON but this time using an expression to pick out the part of the JSON object which houses the data. The schema shown here is for the table data only and skips out the “JsonResponse” part. Example can be seen below:
{“Customer”:[{“No”:”10000″,”Name”:”Adatum Corporation”,”Address”:”Station Road, 21″,”Address_2″:””,”City”:”Cambridge”,”Contact”:”Robert Townes”,”Credit_Limit_LCY”:”150000″,”Customer_Posting_Group”:”DOMESTIC”,”Payment_Terms_Code”:”1M(8D)”}

What are the conclusions here? Would be fair to assume this has data limitations and I haven’t fully stressed tested that part. If you review the Microsoft documentation (Docs_Link) though it seems volumes shouldn’t be an issue – but I there will be:

Code is here if you want to have a try for yourself and extend on this hack: https://github.com/JAng13sea/Blogs/tree/master/Generic%20API

This was originally posted here.

Comments

*This post is locked for comments

Community site session details

Business Central Generic API Hack

Comments

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!