+ Start a Discussion
symantecAPsymantecAP 

Help with SOQL query

Hi All 

 

I have a SOQL query . I would like to add a condition to the query. I would want to add a where clause that will exclude accounts that don't have any activity history?

 

 ContactLastActivityDateBatch accBatch = new ContactLastActivityDateBatch ();
    
        accBatch.query ='select Name, Last_visit_subject__c,Last_visit_level_type__c,Last_visit_description__c,Last_visit_date__c, Last_visit_assigned_to__c, Last_visit_record_type__c, Last_visit_purpose__c,  Last_visit_contact_name__c,'
                        +'(Select Subject,Owner.Name,RecordType__c,Purpose__c, whoId ,AccountId,id,ActivityDate,Level_Type__c,Description from ActivityHistories where '
                        +'Custom_Profile_Name__c in (\'Apartments.com House AC\',\'Apartments.com RM User\',\'Apartments.com Standard User\',\'Gannett Standard User\',\'LA Times Standard User\')'
                        +' AND (RecordType__c=\'Standard Event\'  OR  (RecordType__c=\'Event (Contact Optional)\' AND Level_Type__c in (\'Leases 4 Lunch\',\'Mgmt Co - Cold Call Visit\',\'Property - Cold Call Visit\')))'
                        +' AND ActivityDate <= TODAY'
                        +' AND Isdeleted =false'
                        +' order by ActivityDate desc limit 1) from Account';                     

 Thanks in Advance

IspitaIspita

Hi symantecAP,

You want to achieve the following:-

I would want to add a where clause that will exclude accounts that don't have any activity history?

For that you need to do the following you need to identify that the account have a related activity which has stage = completed (activity history)

So for that you can either:-

  • Run a query like this [Select id, Subject,Owner.Name,RecordType__c,Purpose__c, whoId ,AccountId,id,ActivityDate,Level_Type__c,Description from Activity where stage='completed']; and check with the result returned the account id if they accountid exists in the whole resultset you need to exclude it.
  • As you seem to be interest in doing the same in a single query you can write a trigger on "Activity" which set a field say "HasActivityHistory" to true in related account whenever an activity is closed. Then you can run the query by including the clause as "where HasActivityHistory = false"

Hope this helps.

symantecAPsymantecAP

Hi Ispita

 

I am struggling with trigger on Account that checks if it has Activity History, I am creating a new checkbox on the Account Object.

Kindly help