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
KevinRussellKevinRussell 

SOQL Query record count with Date field filter

I want to query the selected Contact's Interaction records with a Sub_type__c = 'Attend Event' AND Start_Date_Time__c within one year of the current date.
If I have a record count = 0 I know there has not been a record of attending an Event in the past year.  I will set the Contact checkbox to FALSE.
If I have a record count of >= 1, they came to an event within the past year.  I will set the Contact checkbox to TRUE.

 

Can anyone give me a hand for the counting records part of the code?  Here is what I have so far:

 

Thanks

 

Kevin

// Update Contact field: "Attended_Event_Within_Past_Year__c" to TRUE when a new Interaction__c record is inserted or updated. 
trigger Contacts_Attended_Event_Within_Past_Year_Checkbox_test on Interaction__c (after insert, after update) {

// Will store Contact record ID
map< id, contact > contacts = new map< id, contact >();

integer recordcount = 0;

// Create trigger for new or selected Interaction__c record
for(Interaction__c record:trigger.new)        



//I want to query the selected Contact's Interaction records with a Sub_type__c = 'Attend Event' AND Start_Date_Time__c within one year of the current date.
//If I have a record count = 0 I know there has not been a record of attending an Event in the past year.  I will set the Contact checkbox to FALSE.
//If I have a record count of >= 1, they came to an event within the past year.  I will set the Contact checkbox to TRUE.


if(recordcount >= 1)

     // Update checkbox field on the Contact record to TRUE
     contacts.put(record.contact__c, new contact(id=record.contact__c, Attended_Event_Within_Past_Year__c = TRUE));    
Else {
     // Update checkbox field on the Contact record to TRUE
     contacts.put(record.contact__c, new contact(id=record.contact__c, Attended_Event_Within_Past_Year__c = FALSE));    

}     


update contacts.values(); 

}

 

 

 

 

Sean TanSean Tan

You want to avoid SOQL in the loop (the pseudo code you put seems like it would be querying inside the trigger contexts loop).

 

To query on records in the last year, you can use the LAST_N_DAYS date literal field expression. See here for more details on the date literals:

 

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_dateformats.htm

 

Lastly, here's an attempt at what you're asking for... (note I didn't use the LAST_YEAR date literal as based off what I read, it's not what you want)

 

// Update Contact field: "Attended_Event_Within_Past_Year__c" to TRUE when a new Interaction__c record is inserted or updated. 
trigger Contacts_Attended_Event_Within_Past_Year_Checkbox_test on Interaction__c (after insert, after update) {
	
	// Will store Contact record ID
	map< id, contact > contacts = new map< id, contact >();
	
	// Create trigger for new or selected Interaction__c record
	for(Interaction__c record:trigger.new)        
	{
		contacts.put(record.Contact__c, null);
	}
	
	//Get the Interaction records where the start date time is greater then or equal to the last 365 days...
	for (Interaction__c record : [ SELECT Contact__c FROM Interaction__c WHERE Sub_Type__c = 'Attend Event' AND Start_Date_Time__c >= LAST_N_DAYS:365 AND Contact__c IN :contacts.keySet() ])
	{
		contacts.put(record.Contact__c, new Contact__c(Id=record.Contact__c, Attended_Event_Within_Past_Year__c=true));
	}
	
	for (Id key : contacts.keySet())
	{
		//If the previous loop did not add a contact we'll have to add one in with the attended event flag to false
		Contact c = contacts.get(key);
		
		if (c == null)
		{
			contacts.put(key, new Contact(Id=key, Attended_Event_Within_Past_Year__c = false));
		}
	}
	
	update contacts.values(); 
	
}