function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ilewi121ilewi121 

SOQL reference parent object in subquery

Working on a trigger to update the lead object. The field displays a count of activities completed within 30 days from the Lead record's CreatedDate.

 

I can't find a clean way to limit my SOQL subquery based on the parent object's CreatedDate field.

 

Any ideas?

 

    public static String ldPrefix =  Lead.sObjectType.getDescribe().getKeyPrefix();
    public static void updateLeadCounts30days(Set<ID> leadIds) {

        List<Lead> Leads = [SELECT ID, CreatedDate, TP_First_30_Days__c, (SELECT ID, ActivityDate FROM Tasks), (SELECT ID, ActivityDate FROM Events) FROM Lead WHERE ID IN :leadIds];
        List<Lead> updateLeads = new List<Lead>();

        for (Lead l : Leads) {

		Date InitDate = l.CreatedDate;
		Date InitDate30 = l.CreatedDate + 30;

		Integer ThirtyTaskCount = [SELECT COUNT() FROM Tasks WHERE ActivityDate <= :InitDate30];            	
		Integer ThirtyEventCount = [SELECT COUNT() FROM Events WHERE ActivityDate <= :InitDate30];
		Integer FirstThirtyDays = ThirtyTaskCount + ThirtyEventCount;

		if (l.TP_First_30_Days__c != FirstThirtyDays) {
			l.TP_First_30_Days__c = FirstThirtyDays;
		}	
		updateLeads.add(l);
	}

 

Best Answer chosen by Admin (Salesforce Developers) 
ilewi121ilewi121

I would still prefer an SOQL solution, but solved this with Apex.

 

 

for (Lead l : Leads) {
    Integer DayCount = 0;
       
    for (Event E : l.Events) {
        if (e.ActivityDate <= l.CreatedDate.addDays(30)) {
            DayCount = DayCount + 1;
            }
        }

    for (Task T : l.Tasks) {
        if (t.ActivityDate <= l.CreatedDate.addDays(30)) {
            DayCount = DayCount + 1;
            }
        }

    if (ThirtyDayCount != 0){
	if (l.ActivityCount_C != DayCount) {
		l.ActivityCount_C = DayCount;
		}
        updateLeads.add(l);
    }

 

All Answers

MiddhaMiddha

You need not query the Tasks and Events again as you have them within the Lead query. you can iterate over them only. In the given code you are querying the Event records again which will query all the Events in the system and not related to that Lead (if that is your requirement, which i doubt).

ilewi121ilewi121

Thank you Gulshan, I realize it is doubled right now. My problem is I don't know how to run a second query to access the records I pull from the first query. I cannot reference fields from the parent SOQL query to isolate records for my child SOQL query in the same line.

 

How would I write these queries so I can compare the Lead Created Date with the Activity Date?

ilewi121ilewi121

I would still prefer an SOQL solution, but solved this with Apex.

 

 

for (Lead l : Leads) {
    Integer DayCount = 0;
       
    for (Event E : l.Events) {
        if (e.ActivityDate <= l.CreatedDate.addDays(30)) {
            DayCount = DayCount + 1;
            }
        }

    for (Task T : l.Tasks) {
        if (t.ActivityDate <= l.CreatedDate.addDays(30)) {
            DayCount = DayCount + 1;
            }
        }

    if (ThirtyDayCount != 0){
	if (l.ActivityCount_C != DayCount) {
		l.ActivityCount_C = DayCount;
		}
        updateLeads.add(l);
    }

 

This was selected as the best answer