Spread the love

This is a short follow-up to my previous post Become a Business Central API Superhero – Fredborg now the plan was never to create a part 2, but as my great colleague Bo Arentoft pointed out there are some things that I might have missed in the previous post, so I will try to cover some of those points in this post, however, do keep in mind there might still be some things that I miss.

Expand

Expand is used to get related records that you can include with your API pages, this is done by adding the $expand option to your query URL. The first thing we need to do is set up our API page to include the related records that we want people to be allowed to access, in my example we are going to expose the Base Unit of Measure as part of our APIItems from my previous post, to do this let us first create a simple API page for our Base Unit of Measure:

page 50101 myBaseUnitOfMeasure
{
    APIGroup = 'demo';
    APIPublisher = 'fredborg';
    APIVersion = 'v1.0';
    ApplicationArea = All;
    Caption = 'myBaseUnitOfMeasure';
    DelayedInsert = true;
    EntityName = 'baseUnitOfMeasure';
    EntitySetName = 'baseUnitOfMeasure';
    PageType = API;
    SourceTable = "Unit of Measure";
    DataAccessIntent = ReadOnly;
    Editable = false;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(systemId; Rec.SystemId)
                {
                    Caption = 'SystemId';
                }
                field("code"; Rec."Code")
                {
                    Caption = 'Code';
                }
                field(description; Rec.Description)
                {
                    Caption = 'Description';
                }
            }
        }
    }
}

So as you can see there is nothing special about this code, it is pretty much just another API page, next thing that we need to do is add this page to your existing API page API Items, and this is done by adding a new part:

part(myBaseUnitOfMeasure; myBaseUnitOfMeasure)
                {
                    EntityName = 'baseUnitOfMeasure';
                    EntitySetName = 'baseUnitOfMeasure';
                    Caption = 'baseUnitOfMeasure';
                    SubPageLink = Code = field("Base Unit of Measure");
                }

As you can see this is done in the same way that we would add a new part to a regular page, the only difference is that you must specify EntityName and EntitySetName. If we now hit over to Postman and call our Item API page

GET https://api.businesscentral.dynamics.com/v2.0/dev/api/fredborg/demo/v1.0/companies({CompanyId})/apiItems

You should see that everything looks the same

However, if we add ?$expand=baseUnitOfMeasure so our URL looks like this

GET https://api.businesscentral.dynamics.com/v2.0/dev/api/fredborg/demo/v1.0/companies(5501977c-539e-ed11-9889-000d3a39ac50)/apiItems?$expand=baseUnitOfMeasure

You will see that we now get our Unit of Measure in our response

Now you can do a lot of more complicated things with $expand but I will leave the rest up to you.

Properties on API pages

We have some properties that are important to understand on API pages, so let us look at these.

ODataKeyFields: Now I did talk about ODataKeyFields in my previous post, but it is important to understand that this is the key that is used by other systems to integrate with BC, and that is why it is a good idea to always use Sysyem ID as your ODataKeyFields, because your system ID will never change and it is universally unique.
DataAccessIntent: This is also something that I also mentioned in the previous post, this is used for performance you can choose to set it to ReadOnly which will mean that your API will query against a replica database and therefore not affect performance on your production environment, and then you have ReadWrite, which is used if you will allow people to use your API to modify data in your BC.
InsertAllowed, ModifyAllowed, and DeleteAllowed: now these are very self-explanatory and are used to control which actions you are allowed to perform on your API page.

API Query Type

The API Query is just a query that you expose as an API, the downside to using a query is that it is read-only, however on the upside the performance of a query is much greater than an API page, and on top of that you can easily join more tables together in a query and unlike pages, you do not need to add $expand to view related records since everything is returned as one data set, so a simple API query could look like this:

query 50100 "API Item Query"
{
    APIGroup = 'demo';
    APIPublisher = 'fredborg';
    APIVersion = 'v1.0';
    EntityName = 'itemQuery';
    EntitySetName = 'itemsQuery';
    QueryType = API;

    elements
    {
        dataitem(item; Item)
        {
            column(systemId; SystemId)
            {
            }
            column(no; "No.")
            {
            }
            column(description; Description)
            {
            }
            column(Base_Unit_of_Measure; "Base Unit of Measure")
            {
            }
            dataitem(UMO; "Unit of Measure")
            {
                DataItemLink = code = item."Base Unit of Measure";
                SqlJoinType = LeftOuterJoin;
                column(Code; Code)
                {
                }
                column(UmoDescription; Description)
                {
                }
            }
        }
    }

}

and if I call the following URL in postman:

GET https://api.businesscentral.dynamics.com/v2.0/dev/api/fredborg/demo/v1.0/companies({companyID})/itemsQuery

It will return this:

Now like with $expand this is just a simple query and you can of cause do much more complicated joins, but it is more to give an idea of how we can use queries as part of our API toolbox.

And that is it for this follow-up post, and like I also mentioned earlier there are many things that I did not cover in this post, but hopefully, it is enough for you to get started and if you are very interested in API I would recommend that you take a look at the book Microsoft Dynamics 365 Business Central API v2.0 Reference until next time stay safe.

Leave a Reply