The multi-select picklist that could not survive its own success

When I built the Account layer for Doorvana, one of the fields was Markets__c — a multi-select picklist on an existing global value set of Texas metro areas, so a builder or dealer could be tagged with everywhere it operates.

It deployed clean. It was also wrong, and the reason is worth more than the fix.

Multi-select picklists are a reporting dead end

The numeric limits are the least of it. A multi-select picklist tops out at 150 active values and 100 selected on any one record. Doorvana’s list was 29 metros, comfortably inside both.

The real problems are structural:

• You cannot roll up a multi-select. There is no roll-up summary that counts how many accounts cover a given metro, because there is no child record to count.
• You cannot join on it. Nothing else in the org can relate to a picklist value.
• Filtering is miserable. includes and excludes exist, but grouping a report by a multi-select produces a row per unique combination — “Dallas;Fort Worth;Austin” is one group, “Austin;Dallas” is another. That is not a report anyone reads twice.
• The values carry nothing. A metro area has a code, a state, and a lifecycle. A picklist value is a string.

So the first question anyone will ask of this data — which dealers cover Houston? — has no clean answer.

The ceiling was closer than it looked

The value set was 29 metros, all Texas. That felt safe next to a 150 limit.

Except that 29 is the count of Texas metros, not the count of metros. There are 393 metropolitan statistical areas in the United States. The day Doorvana serves a dealer in Oklahoma or Louisiana, that list grows toward a number a multi-select picklist physically cannot hold. The cap is not a distant theoretical limit; it is roughly two and a half times the real-world list size.

What replaced it

A Market__c object for the metro areas, and an Account_Market__c junction joining accounts to them.

Two master-detail relationships rather than lookups, with Account as the primary. That choice buys two things: the junction inherits Account’s sharing rather than needing its own model, and a roll-up summary on Market__c can count covering accounts — which was the whole point.

<?xml version="1.0" encoding="UTF-8"?>
<CustomField xmlns="http://soap.sforce.com/2006/04/metadata">
    <fullName>Account__c</fullName>
    <label>Account</label>
    <type>MasterDetail</type>
    <referenceTo>Account</referenceTo>
    <relationshipLabel>Markets</relationshipLabel>
    <relationshipName>Account_Markets</relationshipName>
    <relationshipOrder>0</relationshipOrder>
    <reparentableMasterDetail>false</reparentableMasterDetail>
</CustomField>

relationshipOrder is 0, which is what makes Account the primary master. It is one line and it decides the sharing model.

The permission set trap

The first deploy of the junction failed, and the error was not obvious.

A related one, for the same file: required fields take no fieldPermissions entry at all. Market_Code__c is required, so it has none — and adding one to “fix” a perceived gap makes the deploy fail.

Seeding it with real codes, not invented ones

An empty object is not a migration. The markets had to come from somewhere, and the somewhere matters.

Market_Code__c holds CBSA codes pulled from the Census Bureau’s 2023 delineation file. All 26 active Texas metros in the old value set matched the delineation by exact title. The two inactive values resolved to renamed successors:

That is the argument for codes, made concretely. Two of twenty-eight names changed inside a few years. Anything keyed on the name would have broken; the codes did not move.

Other - Out of Area has no CBSA equivalent, so it carries the synthetic code OTHER — the one row where the field is not a real CBSA code, which is worth writing down before somebody assumes otherwise.

27 records upserted on Market_Code__c as the external ID.

Two loading gotchas

ERROR running force:data:bulk:upsert:
LineEnding is invalid on user data. Expecting LF but found CRLF

The CLI declares LF to the Bulk API regardless of what is actually in the file. A CSV saved with Windows line endings fails with a message that sounds like a data problem and is not. Convert to LF and it loads unchanged.

The second: deploying a global value set with values removed is additive. Deleting values from the XML does not delete them from the org. The two renamed metros had to be deployed as isActive=false rather than removed — the value set ends up with 29 values, two inactive, which is the honest representation anyway since historical records may reference them.

What it cost, and what it bought

The rebuild was one object, one junction, three fields, a permission set edit, and a data load. Call it an afternoon.

What it bought: which dealers cover Houston is now a report rather than a project. Markets can be retired with the Active__c flag without deleting history. And when Doorvana’s coverage crosses a state line, the list grows past 150 without anybody having to redesign anything under pressure.

The original field is marked superseded rather than deleted, because the sandbox has no Account records to migrate and there was no reason to be dramatic about it. If it had held real data, this would have been a migration rather than an afternoon — which is the actual lesson. The multi-select was not wrong because it was badly built. It was wrong because it was the kind of decision that gets more expensive every week it survives.