The "Case" of the Missing Group By: A Salesforce SOQL Mystery

This image has nothing to do with the post, our founder is just in Cambodia right now 😉

If you’ve ever tried to write a SOQL query and thought, "I’ll just group these results by Case Number, because I am a rational human being," Salesforce has a very special middle finger reserved just for you.

In the world of Salesforce, the CaseNumber field is a bit of a peacock. It looks like a string, it acts like a string, but the moment you try to use it in a GROUP BY clause, Salesforce throws a tantrum. It turns out that despite being the primary "name" of a Case, CaseNumber is a calculated auto-number field, and the underlying architecture treats it as a "non-groupable" entity.

We encountered this head-on while building Factal. Our LLM was doing its best - bless its silicon heart - and generated perfectly logical queries like:

SELECT CaseNumber, COUNT(Id) FROM Case GROUP BY CaseNumber

Result: A big, fat SOQL error.

To solve this, we upgraded the Factal Deterministic Harness to track "Groupability" as a first-class citizen in our metadata. Our system treats the LLM as a creative engine that operates inside a high-security deterministic cage.

Here is how we handle it:

The Informed LLM: We inject "groupability" metadata directly into the model’s context, so it knows which fields are forbidden fruit before it even starts typing.

The Deterministic Correction Layer: If the LLM produces a query that violates these obscure platform rules, our system intercepts it. We use a hard-coded logic layer to validate the query against the actual physical capabilities of the target API, automatically correcting the GROUP BY clauses before the user ever sees an error.

The moral of the story? Just because a field has a name doesn't mean it has an identity. In Salesforce, some fields are just too special to be grouped.