0

I am trying to create a batch class to create new custom object records related to each Account in the organization. The batch first needs to count all the Asset records for each account, where the end date of the Asset is the prior month or after. The count is then included on each record for the newly created custom object records. I've tried the 2 approaches below, but get 2 different errors. Is there some other way I need to do this?

public class MembChurnRecordsBatch implements Database.Batchable<sObject> {
    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator([SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Asset WHERE AccountId != null)]);
    }

Date PriorMonth = System.today().toStartOfMonth().addMonths(-1);
Date CurrentMonth  = System.today().toStartOfMonth();
Integer ChurnAmt;
Integer NewAmt;
Integer ActiveAmt;

    public void execute(Database.batchableContext context, List<Account> scope) {
        List<Churn_Tracker__c> churnList = new List<Churn_Tracker__c>();

This approach returns an error: caused by: System.ListException: Row with duplicate Id at index: 32 || Class.MembChurnRecordsBatch.execute: line 17, column 1 Line 17 is the Map line below.

        Map<Id, AggregateResult> activeMemb = new Map<Id, AggregateResult>([
            SELECT AccountId Id, COUNT(Id) amt1, UsageEndDate
            FROM Asset 
            WHERE AccountId = :scope AND AccountId != null AND UsageEndDate >=:CurrentMonth AND UsageEndDate != null
            GROUP BY AccountId, UsageEndDate]);

        for(Account record: scope) {
            AggregateResult activeCount = activeMemb.get(record.Id);
                if(activeCount != null) {
                    ActiveAmt = (Integer)activeCount.get('amt1');
                } else {
                    ActiveAmt = null;
                }

            Churn_Tracker__c ct = new Churn_Tracker__c(Account_Name__c = record.Id, Churn_Month__c = PriorMonth, Memberships_Active__c = ActiveAmt);
                churnList.add(ct);
            }
        insert churnList;
    }

This approach returns an error: caused by: caused by: System.LimitException: Too many SOQL queries: 201 || Class.MembChurnRecordsBatch.execute: line 17, column 1 Line 17 is the For loop below.

    for(Account acct :scope) {
        ChurnAmt = 0;

        List<AggregateResult> activeMemb = [SELECT AccountId Id, COUNT_DISTINCT(Id) amt1, UsageEndDate
                                            FROM Asset 
                                            WHERE AccountId = :acct.Id AND AccountId != null AND UsageEndDate >=:CurrentMonth AND UsageEndDate != null
                                            GROUP BY AccountId, UsageEndDate];
            if(activeWeb.size() > 0){
                ActiveAmt = (integer) activeMemb[0].get('amt1');
            }else{
                ActiveAmt = 0;
            }
            Churn_Tracker__c ct = new Churn_Tracker__c(Account_Name__c = record.Id, Churn_Month__c = PriorMonth, Memberships_Active__c = ActiveAmt);
                churnList.add(ct);
            }
        insert churnList;
    }


public void finish(Database.BatchableContext bc)
{
    BatchSchedule__c b = BatchSchedule__c.getOrgDefaults();
        b.Scheduled_Id__c = system.scheduleBatch(new MembChurnRecordsBatch(),'ChurnBatch 1'+System.currentTimeMillis(),2);
    upsert b;
}

}

1 Answer 1

1

in your first approah can you remove usageEndDate from query.

old query

Map<Id, AggregateResult> activeMemb = new Map<Id, AggregateResult>([
            SELECT AccountId Id, COUNT(Id) amt1, UsageEndDate
            FROM Asset 
            WHERE AccountId = :scope AND AccountId != null AND UsageEndDate >=:CurrentMonth AND UsageEndDate != null
            GROUP BY AccountId, UsageEndDate]);

new query :

Map<Id, AggregateResult> activeMemb = new Map<Id, AggregateResult>([
            SELECT AccountId Id, COUNT(Id) amt1
            FROM Asset 
            WHERE AccountId = :scope AND AccountId != null AND UsageEndDate >=:CurrentMonth AND UsageEndDate != null
            GROUP BY AccountId]);
6
  • Thanks so much, that worked! I have a new issue now because it's creating 4 duplicate custom object records for each account when it should only create 1 per account.
    – J. Neilan
    Nov 21, 2021 at 17:26
  • 1
    by looing at your code, it does not look like it will create duplicate records. is it possible that you ran batch class multiple times and it created records in each batch run. you can verify it by looking at created date of duplicate records. Nov 21, 2021 at 17:35
  • 1
    Moreover only create custom object record if it needs to be created . e.g. Churn_Tracker__c ct = new Churn_Tracker__c(Account_Name__c = record.Id, Churn_Month__c = PriorMonth, Memberships_Active__c = ActiveAmt); include this in a if condition and check if record already exists or not Nov 21, 2021 at 17:35
  • Thanks! I' resetting all to make sure it only runs once. As far as the IF statement, do you mean to us that to check if the Id is already in the churnList to be inserted?
    – J. Neilan
    Nov 21, 2021 at 17:48
  • 1
    yes, you will need to query exisiting records and check if records for particular account and Churn_Month__c exists or not. a more optimal solution can be creation of a unique field based on your critieria for example concat of accountid + month+ year whcih you will also provide while creating records. and performing an upsert operation with this field instead of insert. unique field logic will depend on your use case. Nov 21, 2021 at 17:52

You must log in to answer this question.

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