I'm trying to write a query that gives me the name of users and the total number of ids assigned to them.

Here I've Daily_Task__c a custom object with a lot of fields, here I'm trying to get the total number of cases assigned to a user and I'm trying the below query

SELECT count(id), User_Name__c FROM Daily_Task__c group by User_Name__c 

when I'm running this query I get the error as below.

[object Object]: User_Name__c FROM Daily_Task__c group by User_Name__c ^ ERROR at Row:1:Column:58 field 'User_Name__c' can not be grouped in a query call

but when I do the below query it is working fine

SELECT id, User_Name__c FROM Daily_Task__c order by User_Name__c 

it is showing the list of all the ids and users(sorted).

please let me know on where am I going wrong with the group by case and how can I fix it.

Here is what I see when I run System.debug(Daily_Task__c.User_Name__c.getDescribe()); in my developer console.

00:32:47:018 USER_DEBUG [1]|DEBUG|Schema.DescribeFieldResult[getByteLength=3900;getCalculatedFormula=Daily_Update__r.Ass_Usr___r.First_Name__c;getCompoundFieldName=null;getController=null;getDefaultValue=null;getDefaultValueFormula=null;getDigits=0;getFilteredLookupInfo=null;getInlineHelpText=null;getLabel=DE Name;getLength=1300;getLocalName=User_Name__c;getMask=null;getMaskType=null;getName=User_Name__c;getPrecision=0;getReferenceTargetField=null;getRelationshipName=null;getRelationshipOrder=null;getScale=0;getSoapType=STRING;getSobjectField=User_Name__c;getType=STRING;isAccessible=true;isAggregatable=true;isAiPredictionField=false;isAutoNumber=false;isCalculated=true;isCascadeDelete=false;isCaseSensitive=false;isCreateable=false;isCustom=true;isDefaultedOnCreate=false;isDependentPicklist=false;isDeprecatedAndHidden=false;isDisplayLocationInDecimal=false;isEncrypted=false;isExternalId=false;isFilterable=true;isFormulaTreatNullNumberAsZero=true;isGroupable=false;isHighScaleNumber=false;isHtmlFormatted=false;isIdLookup=false;isNameField=false;isNamePointing=false;isNillable=true;isPermissionable=true;isQueryByDistance=false;isRestrictedDelete=false;isSearchPrefilterable=false;isSortable=true;isUnique=false;isUpdateable=false;isWriteRequiresMasterRead=false;]

Thanks

  • 1
    What type of field is User_Name__c? – David Reed Oct 9 at 18:06
  • @DavidReed, thanks for the quick response, it is of type string. – Rakesh K Oct 9 at 18:09
  • 1
    is that User_Name__c field encrypted? – Santanu Boral Oct 9 at 18:10
  • Hi @User_Name__c, sorry for asking this, how will I know if it is :(, when I run my order by query, the names are shown as it is. – Rakesh K Oct 9 at 18:12
  • Can you post the rest of the line with your query? Are you storing the result as a List<AggregateResult>? Are there any other fields in the query, or is it actually just User_Name__c? – Shane Steinfeld Oct 9 at 18:33
up vote 3 down vote accepted

The describe information you were able to extract shows

isGroupable=false

which is a proximate, if not very useful, answer. In this case, the real problem blocking you from using this field in a GROUP BY appears to be that it is a cross-object formula field:

Daily_Update__r.Ass_Usr___r.First_Name__c

The documentation on GROUP BY Considerations does not make clear exactly which field types inherently are and are not groupable, but it makes some amount of naive sense to me that cross-object formula fields wouldn't be groupable - there'd certainly be a performance cost involved, at minimum.

As a simple illustration that this is the cause, I created a custom formula field Account_Name__c on Contact, with the formula Account.Name. Having done, so the query

SELECT count(Id) FROM Contact GROUP BY Account_Name__c

returns the same error you're seeing, despite that

SELECT count(Id) FROM Contact GROUP BY Account.Name

is perfectly legal. Since Account.Name is not encrypted, long text, rich text, or anything else fancy, it looks like the mere fact of being a cross-object formula makes the field non-groupable.

Edit: I've discovered that the conclusion above is incorrect, as detailed in another answer. It is not the cross-object nature of the formula field, but its type, that determines groupability. String formula fields are not groupable.

  • Ohk David, that's a lot to digest :(. Is there any work around for that? – Rakesh K Oct 9 at 19:18
  • 1
    @RakeshK If you're running this in Apex, you could change the query to select (select ID from Daily_Tasks__r), Ass_Usr__r.First_Name__c from Daily_Update__c where ID in (select Daily_Update__c from Daily_Task__c), then iterate over the collection and check record.Daily_Tasks__r.size() for each one. The where clause limits the result to Daily Update records that have at least one child Daily Task. – IllusiveBrian Oct 9 at 19:35
  • I don't know offhand what the depth limit is on grouping across relationships. You could try grouping by the Daily_Update__r.Ass_Usr___r.First_Name__c, use a different query structure like IllusiveBrian suggests, or make other changes in your org to copy the grouping value onto each task with a Workflow Rule or similar. – David Reed Oct 9 at 19:43

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.