Delivered to the gate, needed on ward 4: address data in automated processes

The tracking says delivered. The ward says nothing arrived.

Address bugs are the only data problem I know of where the system reports success, the carrier reports success, and the goods still end up in the wrong place.

A medical supplier we worked with ships to fourteen hospitals. Orders arrive by email, through a portal, and for the three largest accounts straight out of their purchasing system. An automation reads the order, creates it in the ERP, prints the delivery note and books the parcel with the carrier. It had run for two years.

Then wards started calling about missing deliveries that tracking showed as delivered, with a timestamp and a signature. The parcels were in the building. They sat in central goods-in, some for eleven days, because nothing on the label said where they were supposed to go next.

The order said "Klinikum Nord, Ward 4B, Dr Weiler, Building C, delivery ramp 2". The ERP record said "Klinikum Nord, Ostring 12, 44807 Bochum". Everything between the company name and the street had been dropped by a mapping written eighteen months earlier, because the source sent the recipient as one block of text and the mapping took the first line as the company and the last as the city.

Nothing failed. No run went red. The address was formally valid and the carrier delivered it exactly as instructed.

Free text in, structured fields out

Almost every address problem starts with a decision nobody made deliberately: the address travels through the pipeline as prose.

It happens easily. A contact form has a three-line "address" box. A portal sends `shipping_address` as a string with line breaks. A PDF order gets parsed and the extractor hands back a paragraph. In each case a human could read it instantly and an automation can only guess.

That works right up until something downstream wants structured fields, and something always does. Carriers want street and house number separately. The ERP has field lengths. Route planning wants coordinates. Accounting wants a country code for VAT.

So somebody writes a parser. Usually a regex in a Make module or a function node in n8n, built one afternoon, tested against six addresses from the customer list. It works, so it stays.

What the parser gets wrong

Take the last token as the house number and you have already broken all of these, and every one is a real address:

Hauptstraße 12a. Am Hasenberg 4-6. Berliner Allee 3 / 5. Industriestraße 44b, gate 3. In der Rehwiese 9 1/2. PO Box 12 04 60. Große Straße 4, c/o Meier. Straße 17, which is an actual street name in Wolfsburg and regularly arrives with the number missing. And every UK address, where the number comes first and the postcode contains letters.

"3 / 5" becomes "5" and the parcel goes to the wrong building. "4-6" becomes a house number some systems reject outright. "2. OG" gets read as a house number because it is the last digit in the line.

We measured this once on a wholesaler's 14,000 stored addresses: the parser was wrong on 3.1 percent of them, about 430 records, and roughly 120 of those were broken badly enough to threaten delivery. Three percent sounds like a footnote. At two hundred parcels a day it is six people-hours a week of somebody sorting it out.

The better answer is usually to not parse at all. If the source can send structured fields, ask for them. A portal form with separate inputs for street, number, addition, postcode, city and country costs half a day and removes the parser permanently. If you are stuck with a third party API that only returns a string, ask the vendor whether structured fields exist somewhere. Often they do and are simply undocumented.

Where you cannot avoid splitting, use a real address service rather than your own rules, and store both the parsed fields and the original text. The original is your insurance. When a complaint arrives three months later you want to know what the customer wrote, not what your regex made of it.

More parts than the target has fields

The second failure is capacity, not parsing. The source supplies more address components than the destination can hold, and the automation silently decides what to drop.

Most ERPs offer name 1, name 2, sometimes name 3, plus street, postcode, city, country and one addition field. Hospital data routinely carries six components: operator, hospital, department, ward, contact, delivery instruction. Six into three does not go, and somebody has to decide what merges and what disappears.

Left to the mapping, the decision is always the same: field one to name one, field two to name two, ignore the rest. That is exactly what happened above.

Made deliberately, it looks different. You decide which components actually affect delivery and those go first. For a hospital that is the ward, not the department. For a trade customer delivering to sites it is the project reference. For a chain it is the branch number in the format that is physically on the wall at goods-in. That conversation belongs with the logistics team, not with IT, and it takes about an hour.

And when a record genuinely does not fit, the automation should say so. A queue that collects a handful of cases a week is fine. Silent truncation is not.

Bill-to, ship-to, and the ramp

Everyone knows billing and delivery addresses differ. We still find workflows that model exactly one address object, because every test customer had the same one.

Both directions hurt. Print the delivery address on the invoice and it goes to a branch or a building site instead of accounts payable, and payment slips by weeks. Ship to the billing address and the parcel sits at head office while the goods are needed on site.

Large customers need more than two. A hospital group has an invoice recipient (the operating company, often with its own VAT number), a receiving site, and within that site a delivery point that depends on the goods, since refrigerated items and dressings do not go to the same door.

The underlying modelling error is storing addresses as properties of the customer. What holds up is a set of addresses per customer, each with a role, a validity period and a default flag, with the order referencing a specific address rather than the customer.

Documents must freeze what the customer record forgets

A customer moves. Someone updates the CRM. What happens to last year's orders?

If your documents resolve the address by reference, all of them change retroactively. The March invoice now shows an address where the customer could not be reached in March. That is harmless until an audit or a dispute over an undelivered shipment, at which point you can no longer reconstruct where you actually shipped.

Same rule as prices and exchange rates: a document freezes the values valid when it was created. Delivery address on the delivery note, billing address on the invoice, both as copies.

The reverse failure is just as common. An automation creates a new address on every order because the customer typed it slightly differently in the portal. After eighteen months a mid-sized account has forty addresses, six of them current and thirty-four of them typos, and sales can no longer find the right one in the dropdown. Normalise before comparing (lowercase, unify Str./Straße/Strasse, strip punctuation and spaces) and reuse the match instead of inserting.

The first shipment across a border

German assumptions hold until the first foreign order.

Postcodes are five digits in Germany, four in Austria and Switzerland, four digits plus two letters in the Netherlands, alphanumeric with a space in the UK, and Ireland went without them for decades. A required field validated against five digits locks out real customers; a field with no validation lets every typo through.

Country is the one we repair most often. Grown datasets contain "Deutschland", "deutschland", "DE", "GER", "D", "BRD", empty (domestic is the default) and "Germany" for anyone created through an English-language portal. Six spellings of one country means every report by country is wrong and every shipping rule needs exceptions.

Store country as a two letter ISO code everywhere, no exceptions. Show "Germany" in the interface if you like; save and transmit `DE`. It is the cheapest cleanup on this list and the effect is immediate.

Validation that corrects without telling you

Address validation is a good idea with a dangerous default.

Most services will not only check an address but fix it. "Bahnhofstr 12" becoming "Bahnhofstraße 12" is helpful. A street that does not exist in that town becoming the closest match in the neighbouring town is not. House number 112 quietly becoming 12, because 12 exists and 112 does not, is worse than the original typo: the record now looks clean, carries a validation flag, and nobody will ever question it again. The customer at 112 simply never receives anything.

We separate the two. The service may normalise, meaning spelling, abbreviations and capitalisation. It may not guess. As soon as the result is a suggestion with a similarity score rather than an exact match, the record goes to a review queue and a person decides. In the datasets we have worked with that is one to three percent of addresses, twenty minutes of somebody's day, and it replaces a class of error that otherwise runs undetected for months.

Log every automatic change: previous value, service, timestamp. Without that log you cannot later tell whether a broken address came from the customer or from your own pipeline.

Label limits, and the geocoder that always answers

Two things at the end of the chain deserve more attention than they get.

Shipping APIs use fixed field lengths, commonly thirty to fifty characters per name line across two or three lines. "Klinikum Nord Gemeinnützige Krankenhausbetriebsgesellschaft mbH" is 62 characters. Some APIs reject the call, which is the friendlier behaviour because you notice. Others truncate, and whatever should have gone on the next line is gone. Check lengths and character sets before you submit, propose a sensible shortening (drop the legal form, do not chop the last 22 characters) and put the case in front of a human.

Geocoding fails even more quietly. A geocoder almost always returns a result, and when it cannot find the address it returns the centre of town, which in a mid-sized city can be four kilometres off. The route looks optimised and the driver ends up phoning dispatch from a roundabout. Every usable geocoding result includes a precision level, and that field is almost never checked in no-code workflows because it sits further down the response object. Anything below street level belongs in a review queue, not in a route. And for hospitals, depots and industrial sites, the postal address is not where you can unload; the delivery point needs its own coordinate, set once by someone who has actually been there.

Twenty test cases and an afternoon

Before an address workflow goes live we run it against a fixed set of cases, every one of them taken from a real incident: a 70 character company name; a recipient with three name components; house numbers 4a and 4-6 and an address with none; a PO box; a c/o address; Austria with a four digit postcode, the Netherlands with "1012 AB", the US with a state; a postcode with a leading zero; names with umlauts and accents; a delivery address that differs from the billing address; a customer who moved between order and invoice; and an address that deliberately does not exist, to see what validation does with it.

Half an hour to run, reusable in every later project, and it replaces the three months you would otherwise spend discovering the same cases one at a time in production.

If you want to know where you stand right now, take ten orders from last week, ideally from your largest accounts, and follow the recipient address through every stage: how it arrived, what the ERP holds, what got printed on the delivery note, what went to the carrier, what is on the label. Wherever something is missing that was present one step earlier, you have found it. It is usually a single mapping, and usually one somebody built on a Friday afternoon two years ago.

Then ask the logistics team how many shipments a month they correct by hand before they go out. That number is in no report, and somebody there knows it to within ten.