Business central blog
Subscribe to blog and get news about new posts.

Integration REST API to Business Central. Parse JSON and store data via AL. New York Times API.

With this post I would like to start a series of articles on third-party application integrations through the API for Business Central. An approximate list of articles will look like this:

  • Integration REST API to Business Central. Parse JSON and store data via AL. New York Times API.
  • REST API, Oauth 2.0, POST/DELETE/PUT Http requests in Business Central. Create JSON in AL. (undefined API)
  • Integration SOAP API to Business Central, working with XML in AL. (undefined API)
This time I will not touch on requests optimization or catching errors, the main purpose of this post is a basic set for working with third-party APIs. The free API from the New York Times will be used. Documentation can be found here:
https://developer.nytimes.com/

There is an ideal application for sending any HTTP Requests and testing API. It is Postman. I strongly recommend to check the correctness of your requests through Postman before writing any code. It is completely free, and the download link is available here:
https://www.postman.com/downloads/

New York Times API and Postman

First, you need to register on their website and add to your account a new NYT API from the list of available ones:
I chose the Book API and after that I get a menu with an overview of my API Key, which we need so that, the New York Times server returns the data to us.
Reading the documentation further, you can test our first request to the New York Times Book API through Postman. Endpoint lists/names.json return to us a list of all the names of TOPs according to the New York Times version.
In addition, endpoint lists.json/list=<list_name> will return us a specific list of books from the TOP for the given <list_name>. In example request we used trade-fiction-paperback list name from the first request.

Storing data

Hurray, we got it, and after checking in Postman, we can begin the process of implementing similar functionality in Business Central. Suppose we have a page that displays all the names of the TOPs according to the New York Times, and we can also see a list of specific books for each of the names.

First, we need the main settings page, where we specify the Base URL and our API Key. For storage of API Key, I used Isolated Storage which I mentioned earlier.

table 52000 "NYT API Setup"
{
    Caption = 'New York Times API Setup';
    DataClassification = CustomerContent;

    fields
    {
        field(10; "Primary Key"; Code[10])
        {
            Caption = 'Primary Key';
            DataClassification = CustomerContent;
        }
        field(20; "Base URL"; Text[100])
        {
            Caption = 'Base URL';
            DataClassification = CustomerContent;
        }
    }
    keys
    {
        key(PK; "Primary Key")
        {
            Clustered = true;
        }
    }
    procedure SetAPIKey(NewAPIKey: Text)
    var
        EncryptionManagement: Codeunit "Cryptography Management";
    begin
        if IsolatedStorage.Contains(GetStorageKey(), DataScope::Module) then
            IsolatedStorage.Delete((GetStorageKey()));
        if EncryptionManagement.IsEncryptionEnabled() and EncryptionManagement.IsEncryptionPossible() then
            NewAPIKey := EncryptionManagement.Encrypt(NewAPIKey);

        IsolatedStorage.set(GetStorageKey(), NewAPIKey, DataScope::Module);
    end;

    procedure GetAPIKey(): Text
    var
        EncryptionManagement: Codeunit "Cryptography Management";
        APIKey: Text;
    begin
        if IsolatedStorage.Contains(GetStorageKey(), DataScope::Module) then begin
            IsolatedStorage.Get(GetStorageKey(), DataScope::Module, APIKey);
            if EncryptionManagement.IsEncryptionEnabled() and EncryptionManagement.IsEncryptionPossible() then
                APIKey := EncryptionManagement.Decrypt(APIKey);
            exit(APIKey);
        end;
    end;

    local procedure GetStorageKey(): Text
    begin
        exit(SystemId);
    end;

}


page 52000 "NYT API Setup"
{

    Caption = 'New York Times API Setup';
    PageType = Card;
    SourceTable = "NYT API Setup";
    UsageCategory = Administration;
    ApplicationArea = all;

    layout
    {
        area(content)
        {
            group(General)
            {
                field("Base URL"; "Base URL")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the Base URL';
                }
                field(APIKey; APIKey)
                {
                    ApplicationArea = All;
                    Caption = 'API Key';
                    ToolTip = 'Specifies the API Key';
                    ExtendedDatatype = Masked;
                    trigger OnValidate()
                    begin
                        SetAPIKey(APIKey);
                    end;
                }
            }
        }
    }

    trigger OnOpenPage()
    begin
        if not Get() then begin
            Init();
            Insert();
        end;
        if GetAPIKey() <> '' then
            APIKey := '****';
    end;

    var
        APIKey: Text;
}
The tables for storing data received from the API are also needed.

table 52001 "NYT Best Sellers Theme"
{
    Caption = 'Best Sellers List of Themes';
    DataClassification = CustomerContent;

    fields
    {
        field(10; "List Name"; Text[250])
        {
            Caption = 'List Name';
            DataClassification = CustomerContent;
        }
        field(20; "Oldest Published Date"; Date)
        {
            Caption = 'Oldest Published Date';
            DataClassification = CustomerContent;
        }
        field(30; "Newest Published Date"; Date)
        {
            Caption = 'Newest Published Date';
            DataClassification = CustomerContent;
        }
        field(40; Updated; Text[30])
        {
            Caption = 'Updated';
            DataClassification = CustomerContent;
        }
        field(50; "List Name Encoded"; Text[250])
        {
            Caption = 'List Name Encoded';
            DataClassification = CustomerContent;
        }
    }
    keys
    {
        key(PK; "List Name")
        {
            Clustered = true;
        }
    }
    trigger OnDelete()
    var
        NYTBestSellers: Record "NYT Best Sellers";
    begin
        NYTBestSellers.SetRange("List Name", Rec."List Name");
        if not NYTBestSellers.IsEmpty() then
            NYTBestSellers.DeleteAll();
    end;
}


table 52002 "NYT Best Sellers"
{
    Caption = 'Best Sellers Book';
    DataClassification = CustomerContent;

    fields
    {
        field(10; "List Name"; Text[250])
        {
            Caption = 'List Name';
            DataClassification = CustomerContent;
        }
        field(20; "Line No."; BigInteger)
        {
            Caption = 'Line No.';
            DataClassification = CustomerContent;
        }
        field(30; "Book Title"; Text[250])
        {
            Caption = 'Book Title';
            DataClassification = CustomerContent;
        }
        field(40; "Book Description"; Text[2048])
        {
            Caption = 'Book Description';
            DataClassification = CustomerContent;
        }
        field(50; "Book Author"; Text[250])
        {
            Caption = 'Book Author';
            DataClassification = CustomerContent;
        }
        field(60; "Amazon URL"; Text[250])
        {
            Caption = 'Amazon URL';
            DataClassification = CustomerContent;
            ExtendedDatatype = URL;
        }
    }
    keys
    {
        key(PK; "List Name", "Line No.")
        {
            Clustered = true;
        }
    }
    trigger OnInsert()
    begin
        "Line No." := GetNextLineNo();
    end;

    procedure GetNextLineNo(): BigInteger
    var
        NYTBestSellers: Record "NYT Best Sellers";
    begin
        NYTBestSellers.SetRange("List Name", "List Name");
        if NYTBestSellers.FindLast() then
            exit(NYTBestSellers."Line No." + 1);
        exit(1);
    end;
}

Get http request

Next, we use a simple function for getting requests:

procedure GetRequest(AdditionalURL: Text; var Data: Text; var httpStatusCode: Integer): Boolean
    var
        NYAPISetup: Record "NYT API Setup";
        httpClient: HttpClient;
        httpResponseMessage: HttpResponseMessage;
        requestUri: Text;
    begin
        NYAPISetup.get();
        requestUri := NYAPISetup."Base URL" + AdditionalURL + 'api-key=' + NYAPISetup.GetAPIKey();

        httpClient.Get(requestUri, httpResponseMessage);
        httpResponseMessage.Content().ReadAs(Data);
        httpStatusCode := httpResponseMessage.HttpStatusCode();
        if not httpResponseMessage.IsSuccessStatusCode() then
            Error(RequestErr, httpStatusCode, Data);
        exit(true);
    end;

Parse JSON in AL

The Get Request function will return us the JSON from New York Times API as text in Data variable. Now our task is to parse this data correctly to save it in tables. AL introduced new data types for this: JsonObject, JsonArray, JsonToken, JsonValue.

Using documentation from Microsoft, I wrote codeunit to parse JSON into tables:

codeunit 52001 "NYT JSON Mgt"
{
    procedure SelectJsonToken(JObject: JsonObject; Path: Text): Text
    var
        JToken: JsonToken;
    begin
        if JObject.SelectToken(Path, JToken) then
            if NOT JToken.AsValue().IsNull() then
                exit(JToken.AsValue().AsText());
    end;

    procedure GetValueAsText(JToken: JsonToken; ParamString: Text): Text
    var
        JObject: JsonObject;
    begin
        JObject := JToken.AsObject();
        exit(SelectJsonToken(JObject, ParamString));
    end;

    local procedure EvaluateUTCDateTime(DataTimeText: Text) EvaluatedDateTime: DateTime;
    var
        TypeHelper: Codeunit "Type Helper";
        ValueTest: Variant;
    begin
        ValueTest := EvaluatedDateTime;
        IF TypeHelper.Evaluate(ValueTest, DataTimeText, '', TypeHelper.GetCultureName()) THEN
            EvaluatedDateTime := ValueTest;
    end;

    procedure UpdateBestSellersTheme(Data: text)
    var
        NYTBestSellersTheme: Record "NYT Best Sellers Theme";
        JToken: JsonToken;
        JObject: JsonObject;
        JArray: JsonArray;
    begin
        if Data = '' then
            exit;

        JToken.ReadFrom(Data);
        JObject := JToken.AsObject();
        JObject.SelectToken('results', JToken);
        JArray := JToken.AsArray();

        foreach JToken in JArray do begin
            NYTBestSellersTheme.Init();
            NYTBestSellersTheme."List Name" := CopyStr(GetValueAsText(JToken, 'list_name'), 1, MaxStrLen(NYTBestSellersTheme."List Name"));
            NYTBestSellersTheme."List Name Encoded" := CopyStr(GetValueAsText(JToken, 'list_name_encoded'), 1, MaxStrLen(NYTBestSellersTheme."List Name Encoded"));
            NYTBestSellersTheme.Updated := CopyStr(GetValueAsText(JToken, 'updated'), 1, MaxStrLen(NYTBestSellersTheme.Updated));
            NYTBestSellersTheme."Newest Published Date" := DT2Date(EvaluateUTCDateTime(GetValueAsText(JToken, 'newest_published_date')));
            NYTBestSellersTheme."Oldest Published Date" := DT2Date(EvaluateUTCDateTime(GetValueAsText(JToken, 'oldest_published_date')));
            NYTBestSellersTheme.Insert();
        end;
    end;

    procedure UpdateBestSeller(Data: Text)
    var
        NYTBestSellers: Record "NYT Best Sellers";
        JToken: JsonToken;
        JToken2: JsonToken;
        JObject: JsonObject;
        JObject2: JsonObject;
        JArray: JsonArray;
        JArray2: JsonArray;
    begin
        if Data = '' then
            exit;

        JToken.ReadFrom(Data);
        JObject := JToken.AsObject();
        JObject.SelectToken('results', JToken);
        JArray := JToken.AsArray();

        foreach JToken in JArray do begin
            NYTBestSellers.Init();
            NYTBestSellers."List Name" := CopyStr(GetValueAsText(JToken, 'list_name'), 1, MaxStrLen(NYTBestSellers."List Name"));
            NYTBestSellers."Amazon URL" := CopyStr(GetValueAsText(JToken, 'amazon_product_url'), 1, MaxStrLen(NYTBestSellers."Amazon URL"));
            JObject2 := JToken.AsObject();
            if JObject2.SelectToken('book_details', Jtoken2) then begin
                JArray2 := JToken2.AsArray();
                foreach JToken2 in JArray2 do begin
                    NYTBestSellers."Book Title" := CopyStr(GetValueAsText(JToken2, 'title'), 1, MaxStrLen(NYTBestSellers."Book Title"));
                    NYTBestSellers."Book Description" := CopyStr(GetValueAsText(JToken2, 'description'), 1, MaxStrLen(NYTBestSellers."Book Title"));
                    NYTBestSellers."Book Author" := CopyStr(GetValueAsText(JToken2, 'author'), 1, MaxStrLen(NYTBestSellers."Book Author"));
                end;
            end;
            NYTBestSellers.Insert(true);
        end;

    end;
}

Sync API Data to BC

And the icing on the cake is a function that will make all requests and will save data from the New York Times Book API to Business Central. I also used the not-so-good Sleep () function since the New York TImes Book API contains a limit on the number of calls for a certain time. I did not find the technical ability to get books for all categories at the same time.

    procedure SyncBookAPIData()
    var
        NYTBestSellerTheme: Record "NYT Best Sellers Theme";
        NYTJsonMgt: Codeunit "NYT JSON Mgt";
        Window: Dialog;
        AddUrl: Text;
        HttpStatusCode: Integer;
        RecCounter: Integer;
        Data: Text;
    begin
        NYTBestSellerTheme.DeleteAll(true);

        AddUrl := '/lists/names.json?';
        GetRequest(AddUrl, Data, HttpStatusCode);
        NYTJsonMgt.UpdateBestSellersTheme(Data);
        Window.OPEN('Processing: @1@@@@@@@@@@@@@@@');
        if NYTBestSellerTheme.FindSet() then
            repeat
                RecCounter += 1;
                Window.UPDATE(1, ROUND(RecCounter / NYTBestSellerTheme.Count() * 10000, 1));
                AddUrl := StrSubstNo('/lists.json?list=%1&', NYTBestSellerTheme."List Name Encoded");
                GetRequest(AddUrl, Data, HttpStatusCode);
                NYTJsonMgt.UpdateBestSeller(Data);
                Commit();
                Sleep(7000); // To avoid New York Time API request limit
            until NYTBestSellerTheme.Next() = 0;
        Window.CLOSE();
    end;

Functional Demo

That's all, it remains only to fill in the setup correctly and synchronize all the data through the action!

Source code

SUNDAY, Jule 05, 2020
Author: Volodymyr Dvernytskyi