Apex Code Development You need to sign in to do that
Don't have an account?
Suryanarayanan Nagarajan 5 query on 'Case' business object
Case Description:
I am trying to execute below query on 'Case' business object.
SELECT Id,CaseNumber,ContactId,Owner.Id,Owner.DeveloperName FROM Case
While executing above query we are getting below error
INVALID_FIELD:
CaseNumber,ContactId,Owner.Id,Owner.DeveloperName FROM Case
^
ERROR at Row:1:Column:41
No such column 'DeveloperName' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Note that 'Case' has relationship name as 'Owner' and this relationship has a reference to 'User' and 'Group'. Group object has a built in field 'DeveloperName'. User build does not have 'DeveloperName' field.
I suspect that this might be happing because of multiple references (Group, User) on 'OwnerId' field and Salesforce query engine may not be able to figure out how to derive Owner.DeveloperName value.
Is there way I can successfully execute above query?
I am trying to execute below query on 'Case' business object.
SELECT Id,CaseNumber,ContactId,Owner.Id,Owner.DeveloperName FROM Case
While executing above query we are getting below error
INVALID_FIELD:
CaseNumber,ContactId,Owner.Id,Owner.DeveloperName FROM Case
^
ERROR at Row:1:Column:41
No such column 'DeveloperName' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Note that 'Case' has relationship name as 'Owner' and this relationship has a reference to 'User' and 'Group'. Group object has a built in field 'DeveloperName'. User build does not have 'DeveloperName' field.
I suspect that this might be happing because of multiple references (Group, User) on 'OwnerId' field and Salesforce query engine may not be able to figure out how to derive Owner.DeveloperName value.
Is there way I can successfully execute above query?
Below query will work and you can get owner name. Best Regards,
Mithun.
Thanks for the details. The query above will not work as it is.
Will you please let us know what fileds you exactly want as output of the above mentioned query.
The developer name on Group object will return either the Public group name or Role name.
Thanks,
Pratik
like name is both on user and group .
I am expecting to get the developer name (public group name/id e.g. Grp00Gx0000001gy6b) from Group object which is linked to the Case object.
In other community post it was suggested to use formula field. then query as
Owner:User.UserRole.DeveloperName. This also did not work. Error as shown below
MALFORMED_QUERY:
CaseNumber,ContactId,Owner.Id, Owner:User.UserRole.DeveloperName from
^ERROR at Row:1:Column:46
unexpected token: ':' (Bind variables only allowed in Apex code)
Alternatively, I had tried adding new formula field (value as $UserRole.DeveloperName) in 'Case' object and use the formula field in query. This kind of query (SELECT Id,CaseNumber,ContactId,Owner.Id, DeveloperName__c FROM Case) does NOT throw any error but it does NOT return the developerName. Note that DeveloperName__c is custom field of type formula with values as $UserRole.DeveloperName)
I will provide you a solution in apex code but you are not going to like it:
List<Case> allTickets = [Select CaseNumber,ContactId,Owner.Id FROM Case];
// I) Prepare Id sets to store the ids
Set<Id> ticketOwnerGroupIds = new Set<Id>();
Set<Id> ticketOwnerUserIds = new Set<Id>();
for(Case ticket : allTickets)
{
// check if ticket has a group as owner
if(String.valueOf(ticket.OwnerId).startsWith('00G'))
ticketOwnerGroupIds.add( ticket.OwnerId );
else
ticketOwnerUserIds.add( ticket.OwnerId ):
}
// II) Get all the bull**** associated with the Ids fished from the OwnerId field
Map<Id, Group>queuesMap = new Map<Id, Group>([SELECT Id, Name FROM Group WHERE Id IN :ticketOwnerGroupIds]);
Map<Id, User>usersMap = new Map<Id, User>([SELECT Id, Name FROM User WHERE Id IN :ticketOwnerUserIds]);
// III) Now let's say you want to print out info
String info = '';
for(Case ticket : allTickets)
{
String ownerName = '';
if(String.valueOf(ticket.OwnerId).startsWith('00G'))
ownerName = queuesMap.get( ticket.OwnerId );
else
ownerName = usersMap.get( ticket.OwnerId );
info += ticket.CaseNumber + ' ' + ownerName;
}
// IV) Success... I think???
// You might also want to consider making a class that will map each field of the result 1 by one
Alternitively ignore the DeveloperName field and just get the name:
SELECT Id, CaseNumber, Owner.Name FROM Case
^ this query will run
Also please remember that in the reports feature you can do this more easily and get all the data you might want by creating a report type of your own.
Hope this helps!
Best Regards,
Alex.