ProblemsReferenceLeaderboard
Upgrade
Sign inSign up
0165

Find the Duplicate Customers

Algorithm
Medium
text
record
normalization
customer

Find the Duplicate Customers

Years of imports and hasty data entry left the Customer table with the same company entered two or three times — Contoso, Ltd. next to CONTOSO LTD, identical VAT numbers spaced and dashed differently. Before the cleanup project can merge anything, it needs a finder that spots the duplicates reliably.

Requirements

Create a codeunit named "Duplicate Customer Finder" with two public procedures:

procedure Normalize(Value: Text): Text
procedure FindDuplicatesOf(CustomerNo: Code[20]): List of [Code[20]]

Normalize rules — all graded:

  1. Keep only the letters A–Z / a–z and the digits 0–9; every other character — spaces, tabs, punctuation, symbols, anything else — is removed.
  2. Uppercase the kept letters: 'Contoso, Ltd.' becomes 'CONTOSOLTD', 'gb-123 456' becomes 'GB123456'.
  3. A value containing no letters or digits normalizes to an empty text ('').

FindDuplicatesOf receives the "No." of an existing customer and returns the numbers of every other customer in the Customer table that is a duplicate of it:

  1. Two customers are duplicates when their normalized Name values are equal, or their normalized "VAT Registration No." values are equal.
  2. An empty normalized value never makes a match, on either field: customers whose VAT numbers are blank (or punctuation-only) are not duplicates for that reason alone, and the same holds for names.
  3. Each duplicate appears in the list exactly once, even when it matches on both name and VAT number.
  4. The list is sorted ascending by "No." and never contains the customer you asked about. An empty list means no duplicates.

What the tests check

The tests call Normalize on fixed and randomized values and compare the result exactly. For FindDuplicatesOf, they insert customers with crafted names and VAT numbers, then compare the returned list — content and order — against the expected customer numbers: name-only matches, VAT-only matches, a customer matching on both fields at once, blank and punctuation-only values that must not match, and a randomized pair so a solution hardcoded on the fixed examples fails. The company already contains other (demo) customers — your finder scans the real Customer table, and the seeded test customers are the only ones that can match.

Learn More

Hint 1
Duplicates only reveal themselves after both sides pass through the same normalization — compute the given customer's normalized name and VAT keys once, then compare every other customer's normalized fields against those two keys.
Hint 2
For Normalize, visit the value one character at a time: indexing a Text with [i] yields a Char you can compare against ranges such as 'A'..'Z' and '0'..'9', and uppercasing the whole value first means every kept letter is already uppercase. (DelChr can also get you there, but it deletes a listed set of characters rather than keeping one.)
Hint 3
For the finder: Get the customer, compute its two keys, then FindSet over Customer with a filter excluding its own No. — the primary-key scan already visits customers in ascending No. order. Guard each comparison so an empty key never matches, and combine the name and VAT checks with a single 'or' so a customer matching on both fields is added only once.
ALBusiness Central 28.5
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.