BizCentralOrbit

Automating Customer Ledger Apply Entry/Knock‑Off in Business Central Using Excel Bulk Uploader


When we work with large volumes of customer invoices and payments, manually applying entries in Business Central can be slow and error‑prone. To solve this, We have built a small “knocking‑off” framework that reads data from an Excel‑based uploader and automatically applies posted sales invoices against posted customer payments.

In this blog, we will walk through the idea behind the solution and then break down the AL code of the button that performs the knock‑off, line by line, so that even a beginner can follow and use the pattern.

Step – 1. First create a Table and List Page for Excel uploader

table 82064 "Knocking Off Entries"
{
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "Entry Type"; Option)
        {
            OptionMembers = "Customer","Vendor";
        }
        field(12; "Invoice Entry No."; Integer)
        {
            DataClassification = ToBeClassified;
        }
        field(3; "BC Invoice Number"; code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(13; "Payment Entry No."; Integer)
        {
            DataClassification = ToBeClassified;
        }
        field(7; "Payment Doc. Number"; code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(10; "Validation Error"; text[2048])
        {
            DataClassification = ToBeClassified;
        }
        field(14; "Remaining Amt. Before Apply"; Decimal)
        {
            DataClassification = ToBeClassified;
        }
        field(15; "Remaining Amt. After Apply"; Decimal)
        {
            DataClassification = ToBeClassified;
        }
    }

    keys
    {
        key(Key1; "Entry Type", "Payment Doc. Number")
        {
            Clustered = true;
        }
    }
}

page 82245 "Knocking Off Entries Uploader"
{
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Lists;
    SourceTable = "Knocking Off Entries";

    layout
    {
        area(Content)
        {
            repeater(GroupName)
            {
                field("Entry Type"; Rec."Entry Type")
                {
                    ApplicationArea = all;
                }
                field("Invoice Entry No."; Rec."Invoice Entry No.")
                {
                    ApplicationArea = all;
                }
                field("BC Invoice Number"; Rec."BC Invoice Number")
                {
                    ApplicationArea = all;
                }
                field("Payment Entry No."; Rec."Payment Entry No.")
                {
                    ApplicationArea = all;
                }
                field("Payment Doc. Number"; Rec."Payment Doc. Number")
                {
                    ApplicationArea = all;
                }
                field("Validation Error"; Rec."Validation Error")
                {
                    ApplicationArea = all;
                }
                field("Remaining Amt. Before Apply"; Rec."Remaining Amt. Before Apply")
                {
                    ApplicationArea = all;
                    Caption = 'Invoice Remaining Amt. Before Apply';
                }
                field("Remaining Amt. After Apply"; Rec."Remaining Amt. After Apply")
                {
                    ApplicationArea = all;
                    Caption = 'Invoice Remaining Amt. After Apply';
                }
            }
        }
    }


Step – 2. Now create two action buttons for Export and Import uploader entries –

action("Customer Template Export")
            {
                ApplicationArea = all;
                Image = Export;
                Caption = 'Customer Payment Template';
                trigger OnAction()
                var
                    KnockingOffEntries: Record "Knocking Off Entries";
                begin
                    ExportExcelFormatCustomer(KnockingOffEntries);
                end;
            }
local procedure ExportExcelFormatCustomer(Var KnockingOffEntries: Record "Knocking Off Entries")
    var
        TempExcelBufferLRec: Record "Excel Buffer" temporary;
        ExcelFileNameLbl: Label 'Customer Payment Template';
        DetailsLbl: Label 'Customer Payment Template';
    begin
        TempExcelBufferLRec.Reset();
        TempExcelBufferLRec.DeleteAll();
        TempExcelBufferLRec.NewRow();
        TempExcelBufferLRec.AddColumn(KnockingOffEntries.FieldCaption("Entry Type"), false, '', true, false, false, '', TempExcelBufferLRec."Cell Type"::Text);
        TempExcelBufferLRec.AddColumn(KnockingOffEntries.FieldCaption("Invoice Entry No."), false, '', true, false, false, '', TempExcelBufferLRec."Cell Type"::Text);
        TempExcelBufferLRec.AddColumn(KnockingOffEntries.FieldCaption("BC Invoice Number"), false, '', true, false, false, '', TempExcelBufferLRec."Cell Type"::Text);
        TempExcelBufferLRec.AddColumn(KnockingOffEntries.FieldCaption("Payment Entry No."), false, '', true, false, false, '', TempExcelBufferLRec."Cell Type"::Text);
        TempExcelBufferLRec.AddColumn(KnockingOffEntries.FieldCaption("Payment Doc. Number"), false, '', true, false, false, '', TempExcelBufferLRec."Cell Type"::Text);
        TempExcelBufferLRec.CreateNewBook(DetailsLbl);
        TempExcelBufferLRec.WriteSheet(DetailsLbl, CompanyName, UserId);
        TempExcelBufferLRec.CloseBook();
        TempExcelBufferLRec.SetFriendlyFilename(StrSubstNo(ExcelFileNameLbl, CurrentDateTime, UserId));
        TempExcelBufferLRec.OpenExcel();
    end;
action("Customer Template Import")
            {
                ApplicationArea = all;
                Caption = 'Customer Payment Template';
                Image = Import;
                trigger OnAction()
                begin
                    if Rec.FindFirst() then
                        Error('First Preocess/Delete existing Record, after that you can import data.');
                    ReadExcelSheet();
                    ImportExcelDataCustomer();
                    Message('Done.');
                end;
            }
local procedure ReadExcelSheet()
    var
        FileManagement: Codeunit "File Management";
        Istream: InStream;
        FromFile: Text[100];
    begin
        UploadIntoStream(UploadMsg, '', '', FromFile, Istream);
        if FromFile <> '' then begin
            FileName := FileManagement.GetFileName(FromFile);
            SheetName := TempExcelBuffer.SelectSheetsNameStream(Istream);
        end
        else
            Error(NoFileMsg);
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.OpenBookStream(Istream, SheetName);
        TempExcelBuffer.ReadSheet();
    end;
 local procedure ImportExcelDataCustomer()
    var
        KnockingOffEntries: Record "Knocking Off Entries";
        RowNo: Integer;
        ColNo: Integer;
        LineNo: Integer;
        MaxRow: Integer;
        CustomerLedgerEntry: Record "Cust. Ledger Entry";
    begin
        RowNo := 0;
        ColNo := 0;
        MaxRow := 0;
        LineNo := 0;
        ExcelRecCount := 0;
        TempExcelBuffer.Reset();
        if TempExcelBuffer.FindLast() then begin
            MaxRow := TempExcelBuffer."Row No.";
        end;
        KnockingOffEntries.DeleteAll();
        for RowNo := 2 to MaxRow do begin
            KnockingOffEntries.Init();
            Evaluate(KnockingOffEntries."Entry Type", GetValueAtCell(RowNo, 1));
            Evaluate(KnockingOffEntries."Invoice Entry No.", GetValueAtCell(RowNo, 2));
            Evaluate(KnockingOffEntries."BC Invoice Number", GetValueAtCell(RowNo, 3));
            Evaluate(KnockingOffEntries."Payment Entry No.", GetValueAtCell(RowNo, 4));
            Evaluate(KnockingOffEntries."Payment Doc. Number", GetValueAtCell(RowNo, 5));
            if KnockingOffEntries."Entry Type" = KnockingOffEntries."Entry Type"::Customer then begin
                CustomerLedgerEntry.Reset();
                CustomerLedgerEntry.SetRange("Entry No.", KnockingOffEntries."Invoice Entry No.");
                if CustomerLedgerEntry.FindFirst() then
                    CustomerLedgerEntry.CalcFields("Remaining Amount");
                KnockingOffEntries.Validate("Remaining Amt. Before Apply", CustomerLedgerEntry."Remaining Amount");
            end;
            KnockingOffEntries.Insert();
        end;
        ExcelRecCount := MaxRow - 1;
    end;
 local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): Text
    begin
        TempExcelBuffer.Reset();
        if TempExcelBuffer.Get(RowNo, ColNo) then
            exit(TempExcelBuffer."Cell Value as Text")
        else
            exit('');
    end;

    var
        TempExcelBuffer: Record "Excel Buffer" temporary;
        UploadMsg: Label 'Please choose the excel file!';
        NoFileMsg: label 'No Excel file found';
        ExcelImportLbl: Label 'Excel Imported & Renamed Jobs Nos Successfully.';
        ExcelImportSuccess: Label 'Excel Imported successfully';
        FileName: Text[100];
        SheetName: Text[100];
        ExcelRecCount: Integer;

Step – 3. Now create action button to cross verify records of uploaders by customer ledger entry table –

action("Check Error")
            {
                trigger OnAction()
                var
                    CustLedEntry: Record "Cust. Ledger Entry";
                    CustLedEntry1: Record "Cust. Ledger Entry";
                begin
                    if Rec.FindSet() then
                        repeat
                            if Rec."Entry Type" = Rec."Entry Type"::Customer then begin
                                CustLedEntry.Reset();
                                CustLedEntry.SetRange("Entry No.", Rec."Invoice Entry No.");
                                if CustLedEntry.FindFirst() then begin
                                    CustLedEntry1.Reset();
                                    CustLedEntry1.SetRange("Entry No.", Rec."Payment Entry No.");
                                    CustLedEntry1.SetRange("Document No.", Rec."Payment Doc. Number");
                                    if not CustLedEntry1.FindFirst() then begin
                                        Rec.Validate("Validation Error", Format('Please check Data of Uploader Entry Number' + Format(Rec."Invoice Entry No.")));
                                        Rec.Modify();
                                    end;
                                end
                                else begin
                                    Rec.Validate("Validation Error", Format('Please check Data of Uploader Entry Number :- ' + Format(Rec."Invoice Entry No.")));
                                    Rec.Modify();
                                end;
                            end;
                        until Rec.Next() = 0;
                    Message('Done.');
                end;
            }

Step – 4. Now process of uploader records if no error is found –

action("Process")
            {
                trigger OnAction()
                var
                    KnockingOffEntries: Record "Knocking Off Entries";
                    CustomerLdgEntryInv: Record "Cust. Ledger Entry";
                    CustomerLdgEntryPay: Record "Cust. Ledger Entry";
                    SetAppliesToID: Codeunit "Cust. Entry-SetAppl.ID";
                    PostAppn: Codeunit "CustEntry-Apply Posted Entries";
                    ApplyParams: Record "Apply Unapply Parameters" temporary;
                    CustomerLedEntry: Record "Cust. Ledger Entry";
                    KnockingOffEntries1: Record "Knocking Off Entries";
                begin
                    KnockingOffEntries1.Reset();
                    KnockingOffEntries1.SetFilter("Validation Error", '<>%1', '');
                    if KnockingOffEntries1.FindFirst() then
                        Error('First resolve error or delete entry.');
                    if Rec."Entry Type" = Rec."Entry Type"::Customer then begin
                        KnockingOffEntries.Reset();
                        KnockingOffEntries.SetRange("Entry Type", KnockingOffEntries."Entry Type"::Customer);
                        KnockingOffEntries.SetRange("Validation Error", '');
                        if KnockingOffEntries.FindSet() then
                            repeat
                                CustomerLdgEntryInv.RESET;
                                CustomerLdgEntryInv.SETRANGE("Entry No.", KnockingOffEntries."Invoice Entry No.");
                                CustomerLdgEntryInv.SETRANGE(CustomerLdgEntryInv.Open, TRUE);
                                CustomerLdgEntryInv.SetRange("Document No.", KnockingOffEntries."BC Invoice Number");
                                IF CustomerLdgEntryInv.FINDFIRST THEN BEGIN
                                    CustomerLdgEntryPay.RESET;
                                    CustomerLdgEntryPay.SETRANGE("Entry No.", KnockingOffEntries."Payment Entry No.");
                                    CustomerLdgEntryPay.SETRANGE(CustomerLdgEntryPay.Open, TRUE);
                                    CustomerLdgEntryPay.SetRange("Document No.", KnockingOffEntries."Payment Doc. Number");
                                    IF CustomerLdgEntryPay.FINDFIRST THEN begin
                                        CustomerLdgEntryInv."Applying Entry" := TRUE;
                                        CustomerLdgEntryInv."Applies-to ID" := USERID;
                                        CustomerLdgEntryInv.CALCFIELDS("Remaining Amount");
                                        CustomerLdgEntryInv.VALIDATE("Amount to Apply", CustomerLdgEntryInv."Remaining Amount");
                                        CODEUNIT.RUN(CODEUNIT::"Cust. Entry-Edit", CustomerLdgEntryInv);
                                        SetAppliesToID.SetApplId(CustomerLdgEntryPay, CustomerLdgEntryInv, USERID);
                                        ApplyParams.CopyFromCustLedgEntry(CustomerLdgEntryPay);
                                        PostAppn.Apply(CustomerLdgEntryInv, ApplyParams);
                                    end;
                                END;
                                CustomerLedEntry.Reset();
                                CustomerLedEntry.SetRange("Entry No.", KnockingOffEntries."Invoice Entry No.");
                                if CustomerLedEntry.FindFirst() then
                                    CustomerLedEntry.CalcFields("Remaining Amount");
                                KnockingOffEntries."Remaining Amt. After Apply" := CustomerLedEntry."Remaining Amount";
                                KnockingOffEntries.Modify();
                            until KnockingOffEntries.Next() = 0;
                    end;
                end;
            }

Now you can check your records – remaining balances of invoices.


To get more such useful information, please follow our LinkedIn page and you can also subscribe our you tube page.

YouTube Link: https://www.youtube.com/@bizcentralorbit

LinkedIn Link: https://www.linkedin.com/company/bizcentralorbit/posts/?feedView=all

If you want to book a 1-to-1 live session with any of our expert consultants then click the link: https://bizcentralorbit.com/#One-to

If you want to read next blog “How to Allow Posted Document Modification in Microsoft Dynamics 365 Business Central” then click the link: https://bizcentralorbit.com/how-to-allow-posted-document-modification-in-microsoft-dynamics-365-business-central/

If you want a Tutorial videos of “Add custom Cue Group on Roll Center Page by AL Language” then click the link: https://www.youtube.com/watch?v=pfa7VobkurQ

Raise a support ticket instantly by clicking the link: https://bizcentralorbit.com/contact-us/

46 thoughts on “Automating Customer Ledger Apply Entry/Knock‑Off in Business Central Using Excel Bulk Uploader”

  1. I started looking into rare coins after finding several old pieces.
    The https://groshi.xyz.
    I wanted to find current market information for collectors, but many websites were missing important details.
    On this website I found helpful educational materials covering historical currencies. The content helped me better understand how the coin market changes over time.
    This website is worth bookmarking if you’re interested in numismatics.

  2. Вирішив розібратися, яке холодильне обладнання використовують магазини.
    Саме цей матеріал привернув мою увагу https://tozak.org.ua/2026/06/sekreti-komercijnogo-merchandajzingu-jak-holodilna-konditerska-vitrina-z-pravilnim-led-svitlom-zbilshuie-prodazhi/

    Тут доступно корисні поради щодо вибору холодильних рішень.
    Стаття допомогла краще розібратися в темі.
    Рекомендую переглянути, якщо вас цікавить холодильні вітрини.

  3. News129.online is an online platform that brings together useful information, engaging publications, and current topics. The website provides access to a varied selection of articles, updates, useful information, and stories covering different areas of interest.
    The publication is intended for readers who appreciate informative content, varied topics, and convenient access to fresh materials. Readers can find the website at https://news129.online. Its goal is to make informative and engaging content convenient for readers to discover, while its collection of articles and topics continues to grow.

  4. Криптодепозиты на Vavada принимаются в сетях TRC-20, ERC-20, BEP-20, Solana и Bitcoin с прозрачным курсом конвертации. Витрина трендов показывает автоматы, набирающие популярность у аудитории площадки за последние семь дней. Больше информации по адресу: вавада зеркало Такая прозрачность снимает основные вопросы новичков ещё до первого депозита и формирует базовое доверие к оператору.

  5. Ogni pagina di Dalla parte delle bambine è una scoperta. Elena Gianini Belotti ci fa notare dettagli che abbiamo sempre avuto sotto gli occhi ma non abbiamo mai visto. La ricerca del formato pdf testimonia la volontà di approfondire un testo che non invecchia mai. L’autrice parla dell’importanza di lasciare alle bambine la possibilità di sporcarsi, di correre, di urlare, proprio come i maschi, per sviluppare un rapporto sano con il proprio corpo e con il mondo. Un inno alla libertà fisica e mentale delle donne di domani. https://dallapartedellebambinepdf.site/ Dalla Parte Delle Bambine Gianini Belotti

  6. Un esordio folgorante quello di Alice Basso, che con Scrivere è un mestiere pericoloso ci introduce al mondo di Vani Sarca. Ghostwriter per necessità, investigatrice per vocazione (sua malgrado). Chi digita “Scrivere è un mestiere pericoloso pdf” cerca l’accesso a un mondo fatto di libri, caffè e indagini. La scrittura dell’autrice è visiva e coinvolgente. Vani è un personaggio che buca la pagina, capace di analizzare le persone dai loro tic linguistici. Se cerchi una lettura che stimoli la mente e diverta allo stesso tempo, hai trovato il libro che fa per te. https://scrivereeunmestierepericolosopdf.site/ Scrivere È Un Mestiere Pericoloso Epub Download​

  7. The humor in the book is often physical and slapstick, such as the disruption of the church service by a pinch-bug. These moments are laugh-out-loud funny even a century later. To enjoy these comic interludes, having the book on hand is great for a quick mood lift. A digital version is perfect for those brief moments of downtime. With a Tom Sawyer PDF, you can read a funny chapter while waiting for an appointment. Let Twain’s wit brighten your daily routine with this portable classic. https://tomsawyerpdf.ru/ The Adventures Of Tom Sawyer Scholastic Pdf

  8. Adarlan’s Assassin is a legend. Throne of Glass shows the person behind the legend. Celaena is vulnerable and strong. Her journey is relatable to anyone who has faced hardship. Sarah J. Maas connects with her readers. A throne of glass pdf is a medium for this connection. meet the girl behind the mask. https://throneofglasspdf.store/ Tower Of Dawn Throne Of Glass 6 Epub

  9. Feyre’s deception in the Spring Court is a high-stakes gamble. If she fails, the Night Court falls. The suspense drives many to find the A Court of Wings and Ruin PDF online. The digital book allows for instant access to the story. The plot twists are shocking and well-executed. The world-building is rich and immersive. It is a story that demands to be read in one sitting. Don’t miss out on the conclusion to this phenomenon. https://acourtofwingsandruinpdf.ru/ A Court Of Wings And Ruin Epub Vk Download​

  10. L’eredità letteraria di Alba de Céspedes è racchiusa in pagine che vibrano di vita vera e sentimenti autentici. In questo romanzo, il lettore viene trasportato in un mondo fatto di speranze giovanili che si scontrano con la durezza della vita adulta. La diversità delle protagoniste permette a chiunque di trovare un personaggio in cui rispecchiarsi o con cui empatizzare. La fruizione digitale dei testi classici ha reso più semplice avvicinarsi a capolavori del genere; non è raro infatti che si cerchi “Nessuno torna indietro” in versione pdf per poter godere della lettura su tablet o e-reader. Lasciati guidare dalla mano esperta dell’autrice attraverso i vicoli di Roma e i meandri dell’animo umano, in un viaggio letterario che non ti lascerà indifferente. https://nessunotornaindietropdf.site/ Alba Cespedes Nessuno Torna Indietro

  11. Rule number nine: relatives of your best friend are strictly off-limits. This is the one rule Elle Evans struggles to keep after a fateful night at the carnival. The Kissing Booth by Beth Reekles explores the tension between loyalty to a best friend and following your heart. Whether you are looking for the paperback or searching for The Kissing Booth PDF to read on your tablet, the story remains a gripping tale of coming-of-age drama. It captures the confusion and excitement of first love perfectly, making it a staple in modern young adult fiction. https://thekissingboothpdf.site/ The Kissing Booth Free Pdf Download

  12. A volte basta una macchina vecchia e un amico fidato per cambiare prospettiva sulle cose. Questo è il cuore del romanzo, che celebra la semplicità come chiave per la serenità. La narrazione è ricca di aneddoti divertenti e riflessioni agrodolci. Per chi è abituato a leggere su Kindle o tablet, trovare una vita nuova pdf è un passaggio naturale per arricchire la propria biblioteca digitale. La comodità di poter cercare parole chiave nel testo o salvare le note rende l’esperienza di lettura ancora più interattiva e personale, permettendo di entrare in risonanza profonda con le tematiche trattate dall’autore. https://unavitanuovapdf.site/ Libro Fabio Volo Una Vita Nuova​

  13. La profondità psicologica dei personaggi è il punto di forza di questo capolavoro. Alba de Céspedes scava nell’animo delle sue protagoniste, rivelandone luci e ombre. Il romanzo è un percorso di formazione che coinvolge e appassiona. La lettura digitale, attraverso il file pdf di “Nessuno torna indietro”, permette di apprezzare ogni dettaglio di questa narrazione straordinaria. Un libro che va letto per capire meglio l’universo femminile e per lasciarsi trasportare dalla bellezza di una storia scritta con il cuore. https://nessunotornaindietropdf.site/ Film Nessuno Torna Indietro

  14. A summer romance that lasts a lifetime. The Kissing Booth is a story about the moments that change us. Elle and Noah’s first kiss is just the beginning. If you are searching for the text, a PDF version is a good way to read it. The story is full of ups and downs. It is a romantic journey that is worth taking. https://thekissingboothpdf.site/ The Kissing Booth Beth Reekles Read Online

  15. L’amicizia tra donne è un tema complesso, ed Elena Ferrante lo affronta con maestria in L’amica geniale. La storia di Lila e Lenù è un viaggio emozionante. Trovare L’amica geniale in PDF è il modo migliore per iniziare a leggere questa saga epica. La comodità del digitale permette di godere del libro in ogni situazione. È una narrazione intensa, ricca di sfumature, che vi terrà incollati alle pagine. Un romanzo che parla di identità e di appartenenza. Da non perdere. https://lamicagenialepdf.site/ Ebook L Amica Geniale Pdf

  16. The ghosts are not there to haunt Will; they are there to help him. They are the family and friends he has lost to the same violence he is about to perpetuate. This novel is a conversation between the living and the dead. Readers often search for a Long Way Down PDF to study the poetic forms used. The anagrams and the line breaks are clever and meaningful. It is a book that rewards close reading and critical thinking. https://longwaydownpdf.store/ A Long Way Down Book Nick Hornby Pdf

  17. El mensaje de empoderamiento de Puedes con esto y más es universal. Nacarid Portal Arráez y Chriss Braund han creado una obra que trasciende fronteras. La segunda parte de la saga nos invita a confiar en nuestro proceso. Si eres un ávido lector digital, la búsqueda de de mi para mi pdf te resultará familiar. Tener acceso a estas palabras de aliento en tu dispositivo móvil es una herramienta poderosa para combatir el desánimo y recordar tu potencial ilimitado en cualquier momento y lugar. https://demiparamipdf.site/ De Mi Para Mi Pdf Download

  18. I alpmost nevber ϲomment, hⲟwever i ⅾiⅾ some searching and wound up heгe Automating Customer Ledger Apply
    Entry/Knock‑Օff in Business Central Uѕing Exceel
    Bulk Uploader – Microsoft Business Central Blog – BizCentralOrbit.
    Аnd Iactually do һave some questions for yyou іf it’s allright.
    Coսld itt Ƅe only me or does it аppear lіke a few oof
    tһеse responses ɑppear as іf they are ϲoming from
    brain dead individuals? :-Р And, іf you are writinng oon additional online social sites,Ι ԝould like to keeр up ԝith evеrything neѡ уօu һave tⲟ post.
    Сould yoᥙ list ᧐f tһе ϲomplete urls of your communal sites
    likе your twitteer feed, Facebook рage or linkedin profile?

    Also visit my һomepage … maths Tuition singapore 880

Leave a Comment

Your email address will not be published. Required fields are marked *

0
    0
    Your Cart
    Your cart is empty
    Scroll to Top