web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Business Central forum / How do I get the remai...
Business Central forum

How do I get the remaining unpaid balance on a sales invoice via the API?

(1) ShareShare
ReportReport
Posted on by Community Member

I'd like to use the API to query the remaining unpaid balance on sales invoices. Whether I can get the unpaid balance directly or have to calculate it myself by summing up payments made on the invoice doesn't matter, as long as I can get there somehow.

The API provides a "customerPayments" endpoint, but even after applying payments to multiple sales invoices, querying that endpoint still just gives me an empty data set.

Any tips for achieving this would be greatly appreciated!

Categories:
Business Central
  • Community Member Profile Picture
    Community Member on at

    Also: I've been able to locate the actual payments themselves via the "generalLedgerEntries" endpoint, but those records don't refer back to an invoice, so I feel like I'm at a dead end there, too.

  • DHric Profile Picture
    DHric 10 on at

    We are in the same boat!  Any assistance with this would be greatly appreciated.

  • Community Member Profile Picture
    Community Member on at

    @DHric, I've found a solution that, while less than ideal, is working for me for now. I created a custom API page as follows:

    page 50122 "My Cust. Ledger Entry Page"
{
    // Metadata
    PageType = API;
    Caption = 'customerLedgerEntries';
    APIPublisher = 'myPublisher';
    APIVersion = 'v1.0';
    APIGroup = 'mygroup';
    EntityName = 'customerLedgerEntry';
    EntitySetName = 'customerLedgerEntries';
    SourceTable = "Cust. Ledger Entry";
    DelayedInsert = true;
    ODataKeyFields = "Entry No.";

    // Fields
    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field(entryNo; "Entry No.")
                {
                    Caption = 'entryNo';
                    ApplicationArea = All;
                }
                field(customerNo; "Customer No.")
                {
                    Caption = 'customerNo';
                    ApplicationArea = All;
                }
                field(documentType; "Document Type")
                {
                    Caption = 'documentType';
                    ApplicationArea = All;
                }
                field(documentNo; "Document No.")
                {
                    Caption = 'documentNo';
                    ApplicationArea = All;
                }
                field(amount; Amount)
                {
                    Caption = 'amount';
                    ApplicationArea = All;
                }
                field(remainingAmount; "Remaining Amount")
                {
                    Caption = 'remainingAmount';
                    ApplicationArea = All;
                }
                field(invoiceGuid; msInvoiceGuid)
                {
                    Caption = 'invoiceGuid';
                    ApplicationArea = All;
                }
            }
        }
    }

    // Variables
    var
        msInvoiceGuid: Guid;
        meCustomerLedgerEntryDocumentTypes: Option " ",Payment,Invoice,"Credit Memo","Finance Charge Memo",Reminder,Refund;

    trigger OnAfterGetRecord();
    var
        rSalesInvoice: Record "Sales Invoice Header";
    begin
        if "Document Type" <> meCustomerLedgerEntryDocumentTypes::Invoice then exit;
        if not rSalesInvoice.Get("Document No.") then exit;

        msInvoiceGuid := rSalesInvoice.Id;
        exit;
    end;
}

    When I query it, I filter it using $filter=documentType eq 'Invoice' to get only invoice records. I opted to determine the actual invoice GUID in the OnAfterGetRecord trigger because that's what we track in our system (since the document number changes when the invoice is posted). The remaining balance will be in "remainingAmount" (or you can do "amount - remainingAmount" to get the paid amount, if you'd rather have that value.

    As a disclaimer: I am not an AL developer, so while I've been able to figure out how to do a few things, there's no guarantee that this is remotely the best way to solve this problem. It already seems a little ridiculous to have to go to this length just to query an invoice balance, doesn't it?

  • DHric Profile Picture
    DHric 10 on at

    Thank you for the update!

    It certainly is more than a little ridiculous that 1, invoices do not have a remaining balance, and 2, customerPayments API returns nothing.

    Best regards!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Business Central

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans