2

- I have a 3 levels deep case hierarchy:

Case level 1 (1.createdate, 1.closedate, 1.type, 1.subtype)
|
+---> Case level 2 (2.createdate, 2.closedate, 2.type, 2.subtype)
      |
      +---> Case level 3 (3.createdate, 3.closedate, 3.type, 3.subtype)

- want to create a SOQL query to use in Klipfolio that shows:

1.createdate, 1.closedate, 1.type, 1.subtype, 2.createdate, 2.closedate, 3.createdate, 3.closedate

Query underneath works... but only one level deep...

SELECT CaseNumber , Type , Sub_type__c , CreatedDate , ClosedDate , Duration_min__c , Status , Overall_Process_Status__c , (SELECT CaseNumber , Type , Sub_type__c , CreatedDate , ClosedDate , Duration_min__c , Status , Overall_Process_Status__c FROM Cases WHERE Type='DBA') FROM Case WHERE Casenumber = '00133138'

5
  • 1
    What is the SOQL you are trying and what error do you receive? This would be helpful information.
    – user6861
    Commented Feb 20, 2014 at 20:20
  • Hi John, thanks for your quick reply. I'm used to sql so need to readjust to SOQL...
    – user6963
    Commented Feb 20, 2014 at 20:53
  • Hi John, thanks for your quick reply. I'm used to sql so need to readjust to SOQL... SELECT CaseNumber, Type, Sub_type__c, CreatedDate, ClosedDate, Duration_min__c, Status, Overall_Process_Status__c, (SELECT CaseNumber, Type, Sub_type__c, CreatedDate, ClosedDate, Duration_min__c, Status, Overall_Process_Status__c FROM Cases WHERE Type='DBA') FROM Case WHERE Casenumber = '00133138' works... but is top down and thus only 1 level deep max... tried other statements from stackexhange but get 'unexpected token 'set''trying (salesforce.stackexchange.com/questions/14213/…)
    – user6963
    Commented Feb 20, 2014 at 20:59
  • Do you have a custom field on Case that is a recursive lookup back to Case? Commented Feb 21, 2014 at 2:00
  • Please update your question with the updated information. This will make the SOQL more legible (i.e. you can format it) and make it easier to provide an answer to your question.
    – user6861
    Commented Feb 21, 2014 at 15:47

1 Answer 1

0

In SOQL, you generally have to go up instead of down, so the appropriate SOQL would be something like:

SELECT Id, CaseNumber, Type, Sub_type__c, CreatedDate, ClosedDate, Duration_min__c, 
       Status, Overall_Process_Status__c, Parent.Id, Parent.CaseNumber, Parent.Type, 
       Parent.Sub_type__c, Parent.CreatedDate, Parent.ClosedDate, 
       Parent.Duration_min__c, Parent.Status, Parent.Overall_Process_Status__c, 
       Parent.Parent.Id, Parent.Parent.CaseNumber, Parent.Parent.Type, 
       Parent.Parent.Sub_type__c, Parent.Parent.CreatedDate, Parent.Parent.ClosedDate, 
       Parent.Parent.Duration_min__c, Parent.Parent.Status, 
       Parent.Parent.Overall_Process_Status__c 
FROM Case 
WHERE Parent.Parent.CaseNumber = '00133138' OR 
      Parent.CaseNumber = '00133138' OR
      CaseNumber = '00133138'

This will find all cases where case number is 00133138, the parent case is 00133138, or the parent's parent case is 00133138.

You can go through four levels of relationships. You may need to map "duplicate" rows so that you get a full picture of the data. Without much experience with Klipfolio, I can't really speculate how or if this data set would be supported, but this is how you'd write a single SOQL query.

1
  • 1
    Thanks! I new it must be something 'easy'. This did the trick. Thanks again!
    – user6963
    Commented Feb 27, 2014 at 21:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .