Integration with Business Central with Microsoft Azure Storage Explorer (Blob Storage) for Importing Bank Statement through XMLPort.

Starting with my first Blog, I want to tell you that there may be some flaws but will try to refine it each and every time I write for you guys.

So in this post, I have customized a button for importing bank statement on Bank Reconciliation by integrating it with Microsoft Azure Blob through XML Port.

Basically a Bank Reconciliation is a process that compares the cash balance on a company’s balance sheet to the corresponding amount on its bank statement. Although some of the banks are linked online but some are not authorized to do so they either drop statements to some ftp or Azure Portals(or Blob Storage), then we can create a link through integration to import it automatically in Business Central.  

Steps to follow:

1. Create a XML Port for the following Bank Statement as shown below:

xmlport 50113 BankAcctestReco

{

    Format = VariableText;

    Direction = Import;

    UseRequestPage = false;

    schema

    {

        textelement(Root)

        {

            XmlName = 'Root';

            tableElement(PSet; Integer)

            {

                XmlName = 'JWBankAcctest';

                AutoSave = false;

                SourceTableView = SORTING(Number) WHERE(Number = CONST(1));

                UseTemporary = true;

                textelement(RecordType)

                {

                    MinOccurs = Zero;

                }

                textelement(AccountNo)

                {

                    MinOccurs = Zero;

                }

                textelement(TransactionDate)

                {

                    MinOccurs = Zero;

                }

                textelement(CreditAmount)

                {

                    MinOccurs = Zero;

                }

                textelement(DebitAmount)

                {

                    MinOccurs = Zero;

                }

                textelement(Codee)

                {

                    MinOccurs = Zero;

                }

                textelement(Description)

                {

                    MinOccurs = Zero;

                }

                textelement(CustomerRef)

                {

                    MinOccurs = Zero;

                }

                textelement(BankRef)

                {

                    MinOccurs = Zero;

                }

                trigger OnBeforeInsertRecord()

                begin

                    IF FirstLine then begin

                        FirstLine := false;

                        currxmlport.SKIP;

                    end;

                end;

                trigger OnAfterInsertRecord()

                var

                    L_Qty: Decimal;

                begin

                    BankHeader.Reset();

                    BankHeader.SetRange("Bank Account No.", '*******');

                    //BankHeader.SetRange("Statement No.", '9');

                    BankHeader.SetRange("Statement Type", BankHeader."Statement Type"::"Bank Reconciliation");

                    if BankHeader.FindFirst then

                        Evaluate(TDate, TransactionDate);

                    if CreditAmount = '' then

                        CreditAmount := '0';

                    Evaluate(UnitPrice, CreditAmount);

                    if DebitAmount = '' then

                        DebitAmount := '0';

                    Evaluate(DAmount, DebitAmount);

                    G_LineNo += 10000;

                    BankLine.Reset();

                    BankLine.SetRange("Bank Account No.", BankHeader."Bank Account No.");

                    BankLine.SetRange("Statement No.", BankHeader."Statement No.");

                    BankLine.SetRange("Statement Line No.", G_LineNo);

                    BankLine.SetRange("Statement Type", BankHeader."Statement Type");

                    if not BankLine.FindFirst() then begin

                        BankLine.Init();

                        BankLine.Validate("Bank Account No.", BankHeader."Bank Account No.");

                        BankLine.Validate("Statement No.", BankHeader."Statement No.");

                        BankLine.Validate("Statement Line No.", G_LineNo);

                        BankLine.Validate("Statement Type", BankHeader."Statement Type");

                        BankLine.Validate("Transaction Text", RecordType);

                        BankLine.Validate("Transaction Date", TDate);

                        BankLine.Validate("Credit Amount", -UnitPrice);

                        BankLine.Validate("Debit Amount", DAmount);

                        BankLine.Validate("Additional Transaction Info", Codee);

                        BankLine.Validate(Description, Description);

                        BankLine.Validate("Related-Party Name", CustomerRef);

                        BankLine.Validate("Related-Party Address", BankRef);                    

                        BankLine.Insert(true);

                    end;

                end;

            }

        }

    }

    trigger OnPreXmlPort()

    begin

        FirstLine := true;

    end;

    var

        G_LineNo: Integer;

        BankHeader: Record "Bank Acc. Reconciliation";

        BankLine: Record "Bank Acc. Reconciliation Line";

        TDate: Date;

        G_Qty: Integer;

        FirstLine: Boolean;

        UnitPrice: Decimal;

        DAmount: Decimal;

}

2. Create a Codeunit for processing file from blob and importing it through XML Port using instream , outstream functions.

codeunit 50113 CreateBlobLink

{

    procedure ImportData()

    var

        PackageURL: Label 'https://***blob****.blob.core.windows.net/****data/%1', Locked = true;

        TempBlob: Codeunit "Temp Blob";

        XMLBankReco: XmlPort BankAcctestReco;

        ImportFile: File;

        PackageOutstream: OutStream;

        DW: Dialog;

        Client: HttpClient;

        ResponseMsg: HttpResponseMessage;

        Instr: InStream;

        ImportLLabel: Label 'Importing Bank Statement...';

        ErrDownload: Label 'Unable to download package';

        Uri: Text;

    begin

        DW.Open(ImportLLabel);

        Clear(TempBlob);

        ClearLastError();

        TempBlob.CreateOutStream(PackageOutstream);

        Uri := StrSubstNo(PackageURL, 'File Name');

        if not Client.Get(Uri, ResponseMsg) then

            Error(ErrDownload, GetLastErrorText());

        if not ResponseMsg.IsSuccessStatusCode() then

            Error(ErrDownload, ResponseMsg.ReasonPhrase);

        ResponseMsg.Content().ReadAs(Instr);

        CopyStream(PackageOutstream, Instr);

        //if TempBlob then

        XMLBankReco.SetSource(Instr);

        XMLBankReco.Import();

        DW.Close();

    end;

}

3. Create an action button on Bank Reconciliation Page and create an instance variable for Codeunit and call the function Import Data.

pageextension 50111 ImportBankStatementCO extends "Bank Acc. Reconciliation"

{

    actions

    {

        addafter("Ba&nk")

        {

            action("Blob Link")

            {

                ApplicationArea = All;

                Promoted = true;

                PromotedCategory = New;

                PromotedIsBig = true;

                trigger OnAction()

                var

                    BankReco: Codeunit CreateBlobLink;

                 

                begin

                    

                    BankReco.ImportData();

                    

                end;

            }

        }

    }

}

This way we can get access to Blob and to get the file or blob whatever is in demand.

Thanks for viewing !!😊

If you find this useful, Please let me know views for the same.