Getting exchange rates from a JSON web service

In this video, I show how to use openrates.io as a source for exchange rates. And as a bonus, watch me go crazy in error handling on a HttpClient call process.

https://youtu.be/20ftNwoakL8

In this video, Erik demonstrates how to build a Business Central extension that fetches currency exchange rates from a JSON web service (openrates.io) and populates the Currency Exchange Rate table. Along the way, he covers HTTP client usage, JSON parsing, proper error handling, and the quirks of AL development.

The Web Service: OpenRates.io

Erik stumbled upon openrates.io, a simple web service that returns currency exchange rates in JSON format. The API supports querying rates for specific dates and base currencies. For example, you can request the latest rates with a base of Canadian Dollars (CAD) and receive a JSON response containing exchange rates for dozens of currencies.

The JSON response looks something like this when formatted:

{
  "date": "2021-03-15",
  "base": "CAD",
  "rates": {
    "USD": 0.79,
    "EUR": 0.67,
    "GBP": 0.57,
    ...
  }
}

The structure is straightforward: a root object containing a date field, a base currency, and a rates object where each key is a currency code and each value is the exchange rate.

Understanding Exchange Rates in Business Central

Before diving into code, Erik takes a quick look at how exchange rates work in Business Central. The Currency Exchange Rate table has two key fields:

  • Exchange Rate Amount — How much of the foreign currency you’re buying
  • Relational Exchange Rate Amount — How much of your local currency (LCY) you need to pay

The rates from the web service tell you how much foreign currency you get for one unit of your local currency. Since Erik wants to express it the other way around (how much local currency for one unit of foreign), he inverts the rate using 1 / CurRate.

Building the Extension

The Page Extension

The extension adds a “Get OpenRates” action to the Currencies page. This keeps things simple — users can trigger the rate import directly from where they manage currencies:

pageextension 70310155 "OpenRates Currencies" extends Currencies
{
    actions
    {
        addlast(processing)
        {
            action(GetOpenRates)
            {
                Caption = 'Get OpenRates';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Category4;
                PromotedOnly = true;
                trigger OnAction()
                var
                    OpenRates: Codeunit "OpenRates Mgt";
                begin
                    OpenRates.GetRateForDate(Today());
                end;
            }
        }
    }
}

Erik refactored the logic into a separate codeunit procedure (GetRateForDate) that accepts a date parameter. This makes it easy to add an outer loop later if you want to fetch rates for multiple dates.

The Core Codeunit: HTTP Client and JSON Parsing

The main logic lives in the OpenRates Mgt codeunit. Here’s the complete implementation:

codeunit 70310155 "OpenRates Mgt"
{
    procedure GetRateForDate(D: Date)
    var
        GenLedSetup: Record "General Ledger Setup";
        Client: HttpClient;
        Response: HttpResponseMessage;
        ContentTxt: Text;
        Root: JsonObject;
        rates: JsonToken;
        CurRec: Record Currency;
        rate: JsonToken;
        CurRate: Decimal;
        DateToken: JsonToken;
        CurDate: Date;
        ExchangeRate: Record "Currency Exchange Rate";
        keys: List of [Text];
        k: Text;
    begin
        GenLedSetup.Get();
        if Client.Get('https://api.openrates.io/' + format(D, 0, 9) + '?base=' + GenLedSetup."LCY Code", Response) then begin
            if Response.IsSuccessStatusCode() then begin
                if Response.Content().ReadAs(ContentTxt) then begin
                    if Root.ReadFrom(ContentTxt) then begin
                        if Root.Get('rates', rates) then begin
                            Root.Get('date', DateToken);
                            CurDate := DateToken.AsValue().AsDate();
                            if CurRec.FindSet() then
                                repeat
                                    if rates.AsObject().Contains(CurRec.Code) then begin
                                        if rates.AsObject().Get(CurRec.Code, rate) then begin
                                            CurRate := rate.AsValue().AsDecimal();
                                            ExchangeRate.Init();
                                            ExchangeRate."Currency Code" := CurRec.Code;
                                            ExchangeRate."Starting Date" := CurDate;
                                            if ExchangeRate.Insert(true) then begin
                                                ExchangeRate.Validate("Exchange Rate Amount", 1);
                                                ExchangeRate.Validate("Relational Exch. Rate Amount", 1 / CurRate);
                                                ExchangeRate.Modify(true);
                                            end;
                                        end;
                                    end;
                                until CurRec.Next() = 0;
                        end else
                            error('Could not find "rates" in json (%1)', ContentTxt);
                    end else
                        error('Malformed JSON (%1)', ContentTxt);
                end else
                    error('Server did not return any data');
            end else begin
                if Response.Content().ReadAs(ContentTxt) then
                    error('Http call failed, return value (%1) (Info %2', Response.HttpStatusCode(), ContentTxt)
                else
                    error('Http call failed, return value (%1)', Response.HttpStatusCode());
            end;
        end else
            error('Could not connect to openrates.io');
    end;
}

Step-by-Step Breakdown

Step 1: Build the URL

The URL is constructed dynamically using the date (formatted with format(D, 0, 9) for XML/ISO format: YYYY-MM-DD) and the local currency code from General Ledger Setup:

'https://api.openrates.io/' + format(D, 0, 9) + '?base=' + GenLedSetup."LCY Code"

Erik notes that format parameter 9 gives you the XML date format — one of the few format codes he can actually remember after 30 years!

Step 2: Make the HTTP Call

The Client.Get() method is used as a shortcut instead of building a full HttpRequestMessage. It returns a boolean indicating whether the client could contact the server at all — this is about connectivity, not about whether the request was successful in terms of business logic.

Step 3: Layered Error Handling

Erik emphasizes the importance of thorough error handling when working with web services. The code checks five distinct failure points:

  1. Connection failure — Client.Get() returns false (invalid URL, network issues)
  2. HTTP error status — Response.IsSuccessStatusCode() checks for 2xx responses (handles 404, 500, 401, etc.)
  3. Empty response — Response.Content().ReadAs() fails if no data was returned
  4. Invalid JSON — Root.ReadFrom() fails if the content isn’t valid JSON
  5. Missing data — Root.Get('rates', rates) fails if the expected structure isn’t there

An important detail: even when you get an HTTP error status code, the server may still return useful content (like error messages). That’s why the code attempts to read the response content even in error cases and includes it in the error message.

Step 4: Parse the JSON

The JSON parsing uses AL’s built-in JSON types. A key concept Erik explains is the difference between JsonObject, JsonToken, and JsonValue:

  • JsonToken is the “catch-all” type — the Get function returns tokens because it doesn’t know ahead of time whether the value is an object, array, or simple value
  • AsObject() converts a token to a JsonObject for further navigation
  • AsValue().AsDecimal() converts a token to a concrete value type

Step 5: Loop Through Currencies

The code loops through the Currency table in Business Central and checks if each currency code exists in the JSON response using rates.AsObject().Contains(CurRec.Code). This approach ensures we only try to import rates for currencies that are both configured in BC and available from the web service.

Erik also demonstrates an alternative approach — looping through the JSON keys directly:

keys := rates.AsObject().Keys();
foreach k in keys do begin
    rates.AsObject().Get(k, rate);
    // process rate...
end;

This alternative iterates over all currencies in the JSON response rather than matching against the Currency table. Which approach to use depends on your requirements.

Step 6: Insert Exchange Rates

For each matched currency, the code initializes an exchange rate record, sets the currency code and date, and attempts to insert it. If the record already exists (same currency and date), the Insert returns false and the existing record is left unchanged — a simple way to handle re-runs without duplicating data.

The rate is inverted (1 / CurRate) because the web service returns how much foreign currency you get per unit of local currency, while BC’s exchange rate table expresses how much local currency you pay per unit of foreign currency.

The ReadAs vs ReadFrom Confusion

Erik calls out a well-known source of confusion in AL: the direction of “Read” and “Write” operations varies depending on the data type. On HttpContent, you use ReadAs() to read content into a text variable. On JsonObject, you use ReadFrom() to read from a text variable into the object. Erik references his earlier video about confusing direction names (In/Out/Read/Write) in Business Central — it’s a common pain point that developers just have to learn to live with.

Extension Permissions Dialog

Erik highlights an important gotcha: the first time an extension tries to make an external HTTP call, Business Central shows a permission dialog asking the user to allow or block the connection. If you accidentally click “Block” or “Block Always,” the extension will silently fail on all future calls. To fix this, you need to find the extension in the Extension Management page and toggle the “Allow HttpClient Requests” setting back on.

Fetching Multiple Days

To extend the solution beyond just today’s rates, Erik adds a simple loop to fetch rates for the last 30 days:

D := Today;
for i := 1 to 30 do begin
    GetRateForDate(D);
    D -= 1;
end;

He does share a humorous debugging moment — forgetting to increment the loop counter i, which caused an infinite loop that crashed his Docker container!

Summary

This example demonstrates a practical pattern for consuming JSON web services in Business Central:

  • Use HttpClient to make HTTP calls — Client.Get() is a convenient shortcut for simple GET requests
  • Implement layered error handling at every step: connection, status code, content reading, JSON parsing, and data validation
  • Use JsonObject, JsonToken, and JsonValue to navigate and extract data from JSON responses
  • Use format(Date, 0, 9) to get ISO-formatted dates for API URLs
  • Remember to handle the HTTP permissions dialog — blocking it will cause silent failures
  • Even error responses from web servers can contain useful information, so try to read and display the content in your error messages

As Erik notes, the amount of error handling may seem excessive when everything works — but when something inevitably breaks, your users will thank you for the clear, descriptive error messages.

Picture of hougaard

hougaard