+ Start a Discussion
Suryanarayanan Nagarajan 5Suryanarayanan 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?
MithunPMithunP
Hi Suryanarayanan Nagarajan 5,

Below query will work and you can get owner name.
Select CaseNumber,ContactId,Owner.Id,Owner.Name FROM Case
Best Regards,
Mithun.
 
PratikPratik (Salesforce Developers) 
Hi Suryanarayanan,

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
 
Vishnu VaishnavVishnu Vaishnav
It's not possible bcoz you only use those fields which are same in both object(user,group).
like name is both on user and group . 


 
Sagar BiyaniSagar Biyani
I am replying this comment on behalf of Suryanarayanan Nagarajan (Original submitter)

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)
 
Alexandru IleanaAlexandru Ileana
Hello,

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.