Dynamics 365 Business Central: How to quickly identify the first transaction date and the number of days since the last sale with a customer

Hi, Readers.
Today I would like to share another mini tip about Business Central, how to quickly identify the first transaction date and the number of days since the last sale with a customer.

I was asked this question recently. Actually, you don’t need complex customizations to gain deeper customer insights. In this post, I’ll show you a straightforward, standard way in Business Central to identify a customer’s first transaction date and calculate the days since their last purchase—all without writing a single line of code. Let’s see more details.

On the Customer List or Customer Card, choose Customer > Statistics.

Choose Show more.

Then, you can identify the first transaction date by checking the Customer Since field in the header.

You can also find this information in the Lifetime (since) field under the Sales tab.

Meanwhile, the ‘Days Since Last Sale’ field shows you exactly how many days have passed since the customer’s last purchase.

Let’s take a deeper look at the standard process.

The First Transaction Date field is a standard field in table 18 Customer.

DaysSinceLastSale is a variable in page 151 “Customer Statistics.”

So, we can easily add these two fields to other pages, such as the Customer List.

Very simple. Give it a try!!!😁

Source code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)

pageextension 50128 CustomerListExt extends "Customer List"
{
    layout
    {
        addafter(Name)
        {
            field("First Transaction Date"; Rec."First Transaction Date")
            {
                ApplicationArea = All;
                ToolTip = 'Displays the date of the first transaction for the customer.';
            }
            field(DaysSinceLastSale; CalcDaysSinceLastSale())
            {
                ApplicationArea = All;
                Caption = 'Days Since Last Sale';
                ToolTip = 'Specifies the number of days since the last sale was made to the customer.';

                trigger OnDrillDown()
                var
                    CustLedgEntry: Record "Cust. Ledger Entry";
                begin
                    FilterCustLedgEntryToLastSale(CustLedgEntry);
                    Page.RunModal(0, CustLedgEntry);
                end;
            }
        }
    }


    local procedure CalcDaysSinceLastSale(): Integer
    var
        CustLedgerEntry: Record "Cust. Ledger Entry";
    begin
        if FilterCustLedgEntryToLastSale(CustLedgerEntry) then
            exit(WorkDate() - CustLedgerEntry."Posting Date");
    end;

    local procedure FilterCustLedgEntryToLastSale(var CustLedgerEntry: Record "Cust. Ledger Entry"): Boolean
    begin
        CustLedgerEntry.SetCurrentKey("Posting Date");
        CustLedgerEntry.SetRange("Customer No.", Rec."No.");
        CustLedgerEntry.SetFilter("Sales (LCY)", '>%1', 0);
        CustLedgerEntry.SetRange(Reversed, false);
        if CustLedgerEntry.FindLast() then begin
            CustLedgerEntry.SetRecFilter();
            exit(true);
        end;
    end;
}

END

Hope this will help.

Thanks for reading.

ZHU