Total Without a Loop

Performance

Easy

ledger-entries

sql-budget

Total Without a Loop

The service desk has a "lifetime sales" lookup: type a customer number, get the total they have ever spent. For small customers it is instant; for the big retail chains it visibly hangs, and the trace shows why — the lookup drags every single ledger entry of the customer across the wire just to add them up, one row at a time. The number is right. The bill for computing it is not.

Your job: the same number, a handful of rows.

Requirements

Create a codeunit named "Customer Sales Total" with one public procedure:

procedure TotalSales(CustomerNo: Code[20]): Decimal

Rules:

Return the sum of "Sales (LCY)" across every Cust. Ledger Entry whose "Customer No." equals CustomerNo.
Entries belonging to other customers must never leak into the total — the tests plant some.
Negative entries (credit memos) reduce the total; they must not be skipped.
A customer with no ledger entries at all returns exactly 0. The procedure never raises an error — the number passed in may not even correspond to an existing Customer record, and the answer is still 0.
The row budget: one call must read at most 10 rows from the database, no matter how many entries the customer has. Grading measures SessionInformation.SqlRowsRead around a single call for a customer holding well over a hundred entries — an implementation that fetches every entry so AL can add them up reads them all and fails. Let the database do the adding.

What the tests check

The grading tests create customers and mock their ledger entries with amounts generated fresh every run, so hardcoded answers fail. Decoys are planted: a second customer whose entries must stay out of the total, and a negative entry that must reduce it. One test asks for a customer with no entries and expects exactly 0; another passes a number that matches no Customer record at all and still expects 0, not an error. The budget test seeds 120+ entries, warms the caches with one throwaway call, then invalidates the server's data cache — a repeated call served from cache memory costs zero SQL, so cached reads can't smuggle the row-by-row loop past the budget — and snapshots SessionInformation.SqlRowsRead around a second call: the total must still be exact and the call must stay within the 10-row ceiling. The tests run in a real company with existing data, so your result must be driven purely by the customer number and the rules above.

Learn More

Hint 1

The budget counts rows read from the database, not lines of AL. A loop that fetches every entry so your code can add them up reads one row per entry — with hundreds of entries it can never fit. Ask the database one question whose answer is already the total.

Hint 2

A record variable can delegate the arithmetic to SQL: one built-in record method computes the sum of a numeric field over the current filters in a single statement and reads a single row — no FindSet, no repeat.

Hint 3

SetRange "Customer No.", call CalcSums on "Sales (LCY)", then read "Sales (LCY)" straight off the record variable — after CalcSums the field holds the filtered sum instead of a row's value.

ALBusiness Central 28.4

Press Compile to check your code compiles — Submit runs the tests.

The code editor is desktop-only

Open this problem on a computer to write and run code. Reading the description, tests and discussion works fine here.