2

I need to maintain a record for each month up to 10 years away, so 120 records ahead. This might be extended in the future!

The code

global with sharing class AccountingPeriodCreationService  {

 public static List <AcctSeed__Accounting_Period__c> accountingPeriod;
 public static Date currentDate;
 public static Date firstDayOfCurrentMonth;
 public static Date lastDayOfCurrentMonth;

 public void createdAPLogic(){


    for(Integer i = 0; i < 120; i++){
    currentDate = Date.today();
    firstDayOfCurrentMonth = currentDate.addMonths(i).toStartofMonth();
    lastDayOfCurrentMonth = currentDate.addMonths(i + 1).toStartofMonth().addDays(-1);

        List<AcctSeed__Accounting_Period__c> checkAccountingPeriodAlreadyExist = [SELECT Id, AcctSeed__Start_Date__c, AcctSeed__End_Date__c FROM AcctSeed__Accounting_Period__c where AcctSeed__Start_Date__c = :firstDayOfCurrentMonth];

        if(checkAccountingPeriodAlreadyExist.isEmpty()){
            accountingPeriod.add(
                new AcctSeed__Accounting_Period__c(
                Name = 'Name',
                AcctSeed__Status__c = 'Status',
                AcctSeed__Start_Date__c = firstDayOfCurrentMonth,
                AcctSeed__End_Date__c = lastDayOfCurrentMonth
                )
            );
        }
    }

        System.debug( 'accountingPeriod: ' + accountingPeriod.size());
        List<Database.SaveResult> accountingPeriod = Database.insert(accountingPeriod, false);


 } 
}

The logic

For each loop iteration, I'm getting the first day and the last day of the month. Storing it in a variable. Then a query to see if a record with the same first date exists (there should only ever be 1 instance). If it doesn't, add the record to a list.

Then I insert the list at the end.

The problem

Is essentially that I'm going to hit limits and I know there are better ways to code this. If I'm correct, I'll have to store all current records into a map, then loop over that map X amount of times to check if it already exists?

Could I get some assistance on how to most efficiently do this please?

Any insight is always appreciated! Thank you

4

Is essentially that I'm going to hit limits and I know there are better ways to code this. If I'm correct, I'll have to store all current records into a map, then loop over that map X amount of times to check if it already exists?

This is a pretty good summary. Looking at your code, I think what you need to change is this.

for(Integer i = 0; i < 120; i++){
    currentDate = Date.today();
    firstDayOfCurrentMonth = currentDate.addMonths(i).toStartofMonth();
    lastDayOfCurrentMonth = currentDate.addMonths(i + 1).toStartofMonth().addDays(-1);

Instead of going straight to the generation of the Accounting Periods, let's generate a Set representing the key data points that define an already-existing Accounting Period. If that is the start date, for example, build a Set<Date>, and populate it with all the values you generate for firstDayOfCurrentMonth.

    List<AcctSeed__Accounting_Period__c> checkAccountingPeriodAlreadyExist = [SELECT Id, AcctSeed__Start_Date__c, AcctSeed__End_Date__c FROM AcctSeed__Accounting_Period__c where AcctSeed__Start_Date__c = :firstDayOfCurrentMonth];

This query is the key limits problem. It needs to be run exactly once, outside the for loop. If you generate a Set<Date> firstDaysOfMonths as mentioned above, you could change the query to

    List<AcctSeed__Accounting_Period__c> checkAccountingPeriodAlreadyExist = [SELECT Id, AcctSeed__Start_Date__c, AcctSeed__End_Date__c FROM AcctSeed__Accounting_Period__c where AcctSeed__Start_Date__c IN :firstDaysOfMonths];

Now we could, for fast O(1) access to determine if a given Accounting Period exists, convert this List into a Map<Date, AcctSeed__Accounting_Period__c> apMap, where the keys are the start dates, and then look at that Map while we iterate over the numbers 1 to 120 again to determine which records need to be created.

I actually think there's a faster way, though. Iterate over this list, and remove from the Set<Date> all those records you already found:

for (AcctSeed__Accounting_Period__c ap : checkAccountingPeriodAlreadyExist) {
    firstDaysOfMonths.remove(ap.AcctSeed__Start_Date__c);
}

Then, lastly, you can iterate over the remaining Start Dates that you generated in the first loop, build an Accounting Period for each one, and finally insert a single list of all the generated Accounting Periods.

If my thesis that the Start Date is unique was false, you can still follow something like this process; you'd just make changes to use a different field or combination of fields as your unique key, and the logic might be slightly more complex.

  • Wow. Took me a bit to digest that, I really appreciate it and don't get any deploying issues! There is one scheduling issue with a supposed invalid date which I will look at tomorrow and test... All debugs look absolutely perfect though – David Picksley Dec 14 '18 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.