--- title: "The multi-select picklist that could not survive its own success" description: "A multi-select picklist caps at 150 values, cannot be rolled up, joined or filtered properly, and there are roughly 390 US metro areas. Replacing one with a junction object, seeded from the Census delineation file." source: "https://noconaconsulting.com/blog/multi-select-picklist-to-junction/" --- # The multi-select picklist that could not survive its own success _Architecture · Doorvana · Garage door manufacturing & service_ A multi-select picklist caps at 150 values, cannot be rolled up, joined or filtered properly, and there are roughly 390 US metro areas. Replacing one with a junction object, seeded from the Census delineation file. Nocona Consulting · September 23, 2026 · 6 min read When I built the Account layer for [Doorvana](https://noconaconsulting.com/work/), 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. Rule of thumb A multi-select picklist is fine for a short, stable list that nobody reports across. The moment the values need to be counted, joined, or own any data of their own, they are records, not strings. ## What replaced it A `Market__c` object for the metro areas, and an `Account_Market__c` junction joining accounts to them. _The picklist stored a string on the Account and nothing could relate to it. The junction gives every account-to-market pairing its own record, so roll-up summaries count covering accounts, reports group cleanly by market, and each market carries its own CBSA code, state and active flag._ 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. force-app/main/default/objects/Account\_Market\_\_c/fields/Account\_\_c.field-meta.xml ```xml Account__c MasterDetail Account Markets Account_Markets 0 false ``` `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. Read on a master-detail child needs read on the master — in the same permission set Granting access to `Account_Market__c` in a permission set requires read on `Account` in **that same permission set**. Splitting them across two sets does not satisfy it. The base permission set had to pick up Account read it did not previously need. 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: | Old value | 2023 delineation | CBSA | | --- | --- | --- | | Houston-The Woodlands-Sugar Land | Houston-Pasadena-The Woodlands | 26420 | | Austin-Round Rock | Austin-Round Rock-San Marcos | 12420 | 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 the error that wastes twenty minutes ```plaintext 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. - Picklists - Junction objects - Reporting - Data loading - Permission sets Disclosure: Doorvana's owner is also a co-owner of Nocona Consulting. This is a real engagement between two separate companies, but not an arm's-length one — and not an independent reference. [More on the work page](https://noconaconsulting.com/work/). ## FAQ ### What is the limit on a Salesforce multi-select picklist? A multi-select picklist allows a maximum of 150 active values, and a single record can hold up to 100 of them. The harder limits are not numeric: multi-select values cannot be rolled up with a roll-up summary field, cannot be joined to another object, and filter behaviour in reports is awkward enough that most teams give up and export to a spreadsheet. ### When should a multi-select picklist be replaced with a junction object? Replace it as soon as the values need to be reported on, rolled up, joined, or owned as records in their own right. A junction object gives each pairing its own row, which means roll-up summaries work, reports group cleanly, and each value can carry its own fields such as a code, a state, or an active flag. A multi-select picklist is fine for a small, stable list nobody reports across. ### Why does granting read on a master-detail child in a permission set fail? Read access on a master-detail child object requires read access on the master object in that same permission set. Splitting them across two permission sets fails validation. If a permission set grants access to a junction object, it has to grant read on both parents as well. ### Why does a Salesforce Bulk API data load fail with LineEnding is invalid on user data? The Salesforce CLI declares LF line endings to the Bulk API regardless of what the file actually contains, so a CSV saved with Windows CRLF line endings is rejected with that error. Convert the file to LF before loading. Nothing about the data itself is wrong, which is what makes the message misleading. ### Should a Salesforce picklist of metro areas store names or codes? Store the code and treat the name as a label. Metropolitan statistical area names change: the 2023 Census delineation renamed Houston-The Woodlands-Sugar Land to Houston-Pasadena-The Woodlands and Austin-Round Rock to Austin-Round Rock-San Marcos. A record keyed on the name breaks at the rename; one keyed on the CBSA code does not.