function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ChickenOrBeefChickenOrBeef 

How to include archived tasks in relationship SOQL query?

Hey everyone,

I have a SOQL query of the Contact object that includes a relationship query of the Tasks associated to the Contacts:
  
FOR(Contact c1 : [SELECT
                         	Id,Last_Contacted__c
                            (SELECT 
                             	Id,Completed_Time_Formula__c 
                             FROM 
                             	Tasks 
                             WHERE 
                             	CreatedById != '005A0000004PQwZ' AND Completed_Time_Formula__c != NULL 
                             ORDER BY Completed_Time_Formula__c
                             DESC LIMIT 1)
                          FROM
                         	Contact
                          WHERE
                          	Id In :contactsInTrigger]){

        //code here

}

Right now the Task query doesn't seem to include archived tasks. I tried to include "ALL ROWS" somewhere in the Task query, but I kept getting errors. Then I tried adding "(IsArchived = TRUE OR IsArchived = FALSE)" to the query, but that didn't seem to include archived tasks when I tested the code.

Is there a way to include all Tasks (both archived and unarchived) in a Task relationship query? Am I doing it wrong?

Thanks,
Greg
Best Answer chosen by ChickenOrBeef
UC InnovationUC Innovation
Hi Greg,

Here's some documentation from salesforce that tells you how to use ALL ROWS.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_query_all_rows.htm

So according to that documentation, the ALL ROWS should go at the end of the query. Try adding it at the end of your entire query like this: 
[SELECT Id,Last_Contacted__c, 
    (SELECT Id,Completed_Time_Formula__c FROM Tasks
        WHERE CreatedById != '005A0000004PQwZ' AND Completed_Time_Formula__c != NULL 
        ORDER BY Completed_Time_Formula__c DESC LIMIT 1)
    FROM Contact WHERE Id In :contactsInTrigger ALL ROWS]

 

All Answers

UC InnovationUC Innovation
Hi Greg,

ALL ROWS should be the answer to your problem. What kind of errors are you getting, and where did you include ALL ROWS?
ChickenOrBeefChickenOrBeef
Hey UC,

I tried adding ALL ROWS to the following four places:
  
FOR(Contact c1 : [SELECT
                         	Id,Last_Contacted__c
                            (SELECT 
                             	Id,Completed_Time_Formula__c 
                             FROM 
                             	Tasks
                                ALL ROWS
                             WHERE 
                             	CreatedById != '005A0000004PQwZ' AND Completed_Time_Formula__c != NULL 
                             ALL ROWS
                             ORDER BY Completed_Time_Formula__c
                             ALL ROWS
                             DESC LIMIT 1
                             ALL ROWS)
                          FROM
                         	Contact
                          WHERE
                          	Id In :contactsInTrigger]){

        //code here

}

Obviously I didn't try all four places at once, but those are the places where I tried. And before I could save my code I would see an error that says "expecting a right parentheses, found 'ALL'".

-Greg
UC InnovationUC Innovation
Hi Greg,

Here's some documentation from salesforce that tells you how to use ALL ROWS.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_query_all_rows.htm

So according to that documentation, the ALL ROWS should go at the end of the query. Try adding it at the end of your entire query like this: 
[SELECT Id,Last_Contacted__c, 
    (SELECT Id,Completed_Time_Formula__c FROM Tasks
        WHERE CreatedById != '005A0000004PQwZ' AND Completed_Time_Formula__c != NULL 
        ORDER BY Completed_Time_Formula__c DESC LIMIT 1)
    FROM Contact WHERE Id In :contactsInTrigger ALL ROWS]

 
This was selected as the best answer
ChickenOrBeefChickenOrBeef
But wouldn't putting the ALL ROWS at the end of the query apply it to the Contact query instead of the Task relationship query? I need the query to include the archived Tasks. I don't care about Archived/Deleted Contacts.

Or would putting the ALL ROWS at the very end apply it to both the Contact query AND the Task relationship query?

-Greg
UC InnovationUC Innovation
It would probably apply it to both. Try adding isArchived=false && isDeleted=false to the where clause of contact to make sure you don't pick up those contacts.
ChickenOrBeefChickenOrBeef
Hey UC,

Adding "ALL ROWS" to the end of the query ended up working! The Task relationship query retrieves the archived Tasks.

Thanks!
-Greg