Well, when people talk about Business Central Extensions, they think about big extensions, in the old times are called “verticals”; but when the customer asks for a minor or not so big modification, the ancient partners called “developments” and those “developments are requested for the customers in almost every implementation (and, yes, you can laugh here, made it every time from scratch).

So, why not develop a set of extensions that helps the consultants to go live in a project? And you can sell it to others, even to the customer if he wants to roll out more companies by themselves.

For example, in Mexico, many companies buy, import, and sell foreign items from other countries and the most used currency is US Dollar. So, all of them ask for:

Can the system get the currency exchange rate automatically? You know, those 5 minutes used to get the exchange rate from a webpage, open the currencies page, go to the exchange rate page, and write the value is a very hard and exhausting work regardless of dangers that await to the users that performs this task every day (read this with raiders of the lost ark music).

Don’t forget the customers and vendors initial balances, those purchase and sales invoices in USD with a posting date prior the go live date; sometimes those invoices have more than a year and we need to track the currency exchange gains and losses.

Most of the times that the customer asks for this solution, they mentioned the Banco de Mexico (Mexico´s National Bank).

Sometime ago, Microsoft put some code to get information from online sources to get currency exchange values and add that info to BC.

But some sources add authentication or other requirements to allow connection, that’s what’s happened in Mexico, they change the rules and broke this functionality.

After seeing a Hougaard YouTube video, I´ve decided to make some code to get the info from the Banco de Mexico source.

First, we need to check the Bank API coz the bank replaces the SOAP webservice with a new API REST.

The first important thing is the required token, you need to get a token if you want to connect to the service so, follow the instructions and get your token.

Then, we need to get the “serie” that we want to use to get the currency factor info. I was thinking in create a table with the different options.

and the add a setup field in some place like GL setup or a new extension for currency setup

Also, in that table we can add some functions to get the starting information about currency factor when we start a new company,

We need to put some code and a new Boolean field to run the process for the initial load and mark the operation to “executed” to avoid a second run.

Ok, lets go with another part, the service call, the daily call.

We need to think when and how we want to call the service; we want an automatic call every day? we can use a job queue to do that and avoid more development. But in this case, we need to develop a codeunit or report.

I decided to use a codeunit for this example. And let’s check the service definition.

The service call is an URL like

https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF43718/datos/oportuno? mediaType=json&token=0631332b2e34e4e………………

  • The https://www.banxico.org.mx/SieAPIRest/service/v1/series/ part of the url is fixed
  • The SF43718 is the series that we are calling and part of the series table that we created
  • datos/oportuno?token= is another fixed part of the url and the token is the number that you can get when you ask for one in the banks service.

With this call you get the next response (JSON):

{
    "idSerie": "SF43718", 
    "titulo": "Tipo de cambio Pesos por dólar E.U.A. Tipo de cambio para solventar obligaciones denominadas en moneda extranjera Fecha de determinación (FIX)", 
    "datos": [ 
        { 
            "fecha": "29/09/2015", 
            "dato": "15.8174" 
        } 
    ] 
}

We need to catch the answer and look for the data that we need in the response xml. Create your codeunit and set the startup call to the webservice

Client.Get('https://www.banxico.org.mx/SieAPIRest/service/v1/series/' + BMSetup."Bank Series to Use" + '/datos/oportuno?token=' + BMSetup.Token, Response) then begin

If you see, we are using the setup table to get the series to use, and the token assigned to us.

I we get response, we store that response in a text variable and look through It for the data that we need, in this case, the “dato” (exchange rate) and the “fecha” (date).

So, after we store the answer in the text var, we read from and look for a string like 'dato":"' and get the information using a CopyStr function getting the position using the StrPos function.

Also, we need to follow the Hougaard instructions about the error control

procedure GetRateforDateBanxico()
    var
        Client: HttpClient;
        Response: HttpResponseMessage;
        ContentTxt: Text;
        Root: JsonObject;
        CurRate: Decimal;
        CurRateTxt: text;
        CurDateTxT: Text;
        ExchangeRate: Record "Currency Exchange Rate";
        BMSetup: Record "GetCurrfromBM Setup";
        Pos: Integer;
        PosD: Integer;
    begin
        BMSetup.Get();
        if Client.Get('https://www.banxico.org.mx/SieAPIRest/service/v1/series/' + BMSetup."Bank Series to Use" + '/datos/oportuno?mediaType=json&token=' + BMSetup.Token, Response) then begin
            if Response.IsSuccessStatusCode() then begin
                if Response.Content().ReadAs(ContentTxt) then begin
                    if Root.ReadFrom(ContentTxt) then begin
                        if ContentTxt.Contains('dato":"') then begin
                            Pos := StrPos(ContentTxt, 'dato":"') + 7;
                            CurRateTxt := CopyStr(ContentTxt, Pos, 7);
                            PosD := StrPos(ContentTxt, 'fecha":"') + 8;
                            CurDateTxT := CopyStr(ContentTxt, PosD, 10);
                            if Evaluate(CurRate, CurRateTxt) then begin
                                ExchangeRate.Init();
                                ExchangeRate."Currency Code" := BMSetup."Currency Code";
                                ExchangeRate."Starting Date" := ConvDate(CurDateTxT);
                                if ExchangeRate.Insert(true) then begin
                                    ExchangeRate.Validate("Exchange Rate Amount", 1);
                                    ExchangeRate.Validate("Relational Exch. Rate Amount", CurRate);
                                    ExchangeRate.Validate("Adjustment Exch. Rate Amount", 1);
                                    ExchangeRate.Validate("Relational Adjmt Exch Rate Amt", CurRate);
                                    ExchangeRate.Modify(true);
                                end;
                            end else
                                message('There´s no data to read');
                        end else
                            Error('Could not find "dato" in XML (%1)', ContentTxt);
                    end else
                        Error('Malformed XML (%1)', ContentTxt);
                end else
                    error('server did not return')
            end else begin
                if Response.Content().ReadAs(ContentTxt) then
                    Error('Fail call with code (%1) (Info %2', Response.HttpStatusCode(), ContentTxt)
                else
                    Error('Fail call with cod (%1)', Response.HttpStatusCode)
            end;
        end else
            Error('Cannot connect');
    end;

    local procedure ConvDate(curdatetxt: text): Date
    var
        finaldate: Date;
    begin
        if Evaluate(finaldate, curdatetxt) then
            exit(finaldate);
    end;

With the code, we receive the exchange rate, the date and put everything in the related table.

Once we finished and tested the code (I´ve used an action in the setup page to run the code), we can move to the job queue and create a new queue to run the codeunit using a simple function in the on run trigger 

trigger OnRun()
    begin
        GetRateforDateBanxico();
    end;

With this, you can setup your queue to run every 1440 minutes from Monday trough Friday and get the currency exchange rate, but, can we used the same approach in case that i want the exchange rate for a multiple days? or some defined set? remember, if we have initial statements from customer or vendors in USD from previous dates, we need to have that info in the system.

So, we can copy or procedure and add some additional instructions and code to achieve this

procedure GetHistExchRate(StDate: date; EnDate: Date)
    var
        Client: HttpClient;
        Response: HttpResponseMessage;
        ContentTxt: Text;
        Root: JsonObject;
        CurRate: Decimal;
        CurRateTxt: text;
        CurDateTxT: Text;
        ExchangeRate: Record "Currency Exchange Rate";
        BMSetup: Record "GetCurrfromBM Setup";
        Pos: Integer;
        PosD: Integer;
        Startdate: text;
        EndDate: text;
        TDate: Record Date;
    begin
        TDate.Reset();
        TDate.SetRange("Period Start", StDate, EnDate);
        TDate.SetRange("Period Type", TDate."Period Type"::Date);
        TDate.Setfilter("Period No.",'%1..%2',1,5);
        if TDate.FindSet() then
            repeat
                BMSetup.Get();
                Startdate := Format(TDate."Period Start", 0, 9);
                EndDate := Format(TDate."Period Start", 0, 9);
                if Client.Get('https://www.banxico.org.mx/SieAPIRest/service/v1/series/' + BMSetup."Bank Series to Use" + '/datos/' + Startdate + '/' + EndDate + '?mediaType=json&token=' + BMSetup.Token, Response) then begin
                    if Response.IsSuccessStatusCode() then begin
                        if Response.Content().ReadAs(ContentTxt) then begin
                            if Root.ReadFrom(ContentTxt) then begin
                                if ContentTxt.Contains('dato":"') then begin
                                    Pos := StrPos(ContentTxt, 'dato":"') + 7;
                                    CurRateTxt := CopyStr(ContentTxt, Pos, 7);
                                    PosD := StrPos(ContentTxt, 'fecha":"') + 8;
                                    CurDateTxT := CopyStr(ContentTxt, PosD, 10);
                                    if Evaluate(CurRate, CurRateTxt) then begin
                                        ExchangeRate.Init();
                                        ExchangeRate."Currency Code" := BMSetup."Currency Code";
                                        ExchangeRate."Starting Date" := ConvDate(CurDateTxT);
                                        if ExchangeRate.Insert(true) then begin
                                            ExchangeRate.Validate("Exchange Rate Amount", 1);
                                            ExchangeRate.Validate("Relational Exch. Rate Amount", CurRate);
                                            ExchangeRate.Validate("Adjustment Exch. Rate Amount", 1);
                                            ExchangeRate.Validate("Relational Adjmt Exch Rate Amt", CurRate);
                                            ExchangeRate.Modify(true);
                                        end;
                                    end else
                                        message('There´s no data to read');
                                end else;
                                    //Error('Could not find "dato" in XML (%1)', ContentTxt);
                            end else
                                Error('Malformed XML (%1)', ContentTxt);
                        end else
                            error('server did not return')
                    end else begin
                        if Response.Content().ReadAs(ContentTxt) then
                            Error('Fail call with code (%1) (Info %2', Response.HttpStatusCode(), ContentTxt)
                        else
                            Error('Fail call with code (%1)', Response.HttpStatusCode)
                    end;
                end else
                    Error('Cannot connect');
            until TDate.Next() = 0;
    end;

If you can see, we add an initial and end date, to create a query for a date range, also, we remove one else, because we don't want that the process stops when the query don't receive any result (like Saturday and Sundays).

Finally, add the action to the setup page with a boolean to mark the process "executed" when its done like a proper go live process

action(InitialLoad)
            {
                Caption = 'Run Initial Load';
                ApplicationArea = All;
                Promoted = true;
                PromotedIsBig = true;
                PromotedCategory = Process;
                Enabled = IsVisible;
                Image = GetLines;

                trigger OnAction()
                begin
                    ValidateDates();
                end;
            }

var
        StartDate: Date;
        EndDate: Date;
        IsVisible: Boolean;

    trigger OnOpenPage()
    begin
        IsVisible := true;
    end;

    trigger OnAfterGetRecord()
    begin
        if Rec."Initial load executed" = true then
            IsVisible := false
        else
            IsVisible := true;
    end;

    local procedure ValidateDates()
    var
        Err01: Label 'Starting date cannot be later than Ending date';
        Err02: Label 'Starting date cannot be 0D';
        Err03: Label 'Ending date cannot be 0D';
        bm: Codeunit BMGetCurrFactor;
    begin
        if StartDate > EndDate then
            Error(Err01);

        if StartDate = 0D then
            Error(Err02);

        if EndDate = 0D then
            Error(Err03);

        bm.GetHistExchRate(StartDate,EndDate);
    end;

So, you have the code, feel free to make corrections or changes to adjust it to your needs.