Your Salesforce Report Is Quietly Missing Records (Blame the Import)

While preparing reports for a nonprofit client, I ran a routine check I’ve learned to never skip — and found 233 records that no correctly-built report would ever count. Nothing was deleted, nothing was hidden by permissions. The records were sitting in plain sight with values their own fields don’t allow.

The worst offender: 152 business records whose Type read “Accomodation and Food Services.” Note the spelling. The picklist’s real value is spelled correctly — so a dashboard filtered to the real value reports a community of restaurants and caterers as if it barely exists.

How a field ends up with an illegal value

Picklists feel like they enforce themselves: the form shows a fixed list, a user picks one, done. And through the user interface, that’s true.

But bulk imports don’t go through the user interface. Data Loader, the Import Wizard, integration syncs, a migration done by a long-gone contractor — these write whatever string is in the source file. Unless a picklist is explicitly marked Restrict picklist to the values defined in the value set, Salesforce accepts the import without complaint. A typo in a spreadsheet column in 2024 becomes a permanent ghost value in 2026.

The same thing happens when an admin renames or deactivates a picklist value without updating existing records: the old string stays on every record that had it, now matching nothing.

Why nobody notices

Ghost values are invisible in exactly the places people look:

• Reports filtered on the proper values skip them silently. No error, no “233 records excluded” warning. The total is just wrong.
• Dashboards group them into nothing — or into a confusing duplicate bucket (“Accomodation…” and “Accommodation…” as two slices).
• Open one record individually and it looks fine — the bad value displays like any other. You only see trouble if you try to edit and save, when the picklist suddenly demands a legal choice.

The aggregates lie while every individual record looks healthy — the most dangerous failure mode reporting has.

The ten-minute check

For each picklist that matters to your reporting (program type, status, demographics):

1. Build a quick summary report on the object, grouped by that field — with no filters at all. Every distinct stored value appears as a group, including the ghosts.
2. Compare the groups against Object Manager → the field → Values. Anything in the report that isn’t an active value in the field is a ghost.
3. Tally how many records each ghost holds before deciding anything — a ghost with three records is a cleanup chore; a ghost with 152 is quietly distorting every number you show the board.

(If you have an admin who can query: SELECT Field__c, COUNT(Id) FROM Object__c GROUP BY Field__c does step 1 in one line.)

The fix is a mapping, not a migration

Each ghost value needs one decision: which real value does it become? Sometimes it’s obvious — the typo maps to the correct spelling, “Ownership” maps to “Own.” Sometimes the ghost is actually a legitimate category nobody added to the picklist, and the right fix is to add/reactivate the value rather than overwrite history.

Then it’s a standard bulk update: export the affected record IDs, set the corrected value, load. An hour of admin work, most of it spent on the mapping decisions — which belong to whoever owns the data, not whoever runs the tool.

Two habits prevent the next batch: check Restrict picklist to the values defined in the value set on fields where stray values would hurt, and run the ten-minute check before building any new report or dashboard. A report is only as honest as the values underneath it — and the values are only as clean as your messiest import.