0

first of all, thank you for your cooperation, right now I'm having problems changing my class to a batch class, the class that I will post below works, it creates a CSV file on "SageExtractFiles__c" entity than it lets you download it if you want to, but when I try to create a CSV with a large chunk of records it throws an error regarding the Limits, that's why I need to change this to a batch class, of course, I will not ask for you to write all the code, but at least some guidelines would be really useful.

Class (right now) :

public with sharing class SageCsvFileController {

public ApexPages.StandardSetController sageFiles {
    get {
        if (sageFiles == null) {
            sageFiles = new ApexPages.StandardSetController(Database.getQueryLocator(
                [Select Id, Name, DatePostedtoSage__c, AccountNumber__c, GoodsAmount__c, TransactionDate__c, PostedtoSage__c FROM SageExtract__c where PostedtoSage__c = false]));
            sageFiles.setPageSize(sageFiles.getResultSize());
        }
        return sageFiles;
    }
    set;
}

public List < SageExtract__c > listSageCtrl {
    get;
    private set;
}

public SageCsvFileController(ApexPages.StandardSetController ctrl) {}


public PageReference exportToExcel() {
    SageExtractFile__c sef = new SageExtractFile__c();
    insert sef;
    SageExtractFile__c insertedSef = [SELECT Id, Name FROM SageExtractFile__c WHERE Id =: sef.Id];
    system.debug(sef);
    List < SageExtract__c > listSage = (List < SageExtract__c > ) sageFiles.getRecords();
    system.debug('listSage :' + listSage.size());
    Map < String, String > accountRecordTypeWithAmountAndDate = new Map < String, String > ();
    if (listSage.size() > 0) {
        for (SageExtract__c s: listSage) {
            system.debug(s);
            s.PostedtoSage__c = true;
            s.DatePostedtoSage__c = system.today();
            s.SageExtractFile__c = insertedSef.Id;
            system.debug('TransactionDate__c'+ s.TransactionDate__c);
            String transDateNoTime = s.TransactionDate__c.day() + '-' + s.TransactionDate__c.month() + '-' + s.TransactionDate__c.year();
            string key = '';
            if (!s.Name.contains('-')) {
                key = s.AccountNumber__c + '_' + s.Name + '-' + transDateNoTime;
            } else {
                key = s.AccountNumber__c + '_' + s.Name.substringBefore(' -') + '-' + transDateNoTime;
            }
            if (accountRecordTypeWithAmountAndDate.containsKey(key)) {
                Decimal amount = Decimal.valueOf(accountRecordTypeWithAmountAndDate.get(key).substringBeforeLast('_'));
                String transDate = accountRecordTypeWithAmountAndDate.get(key).substringAfterLast('_');
                if (String.isBlank(String.valueOf(s.GoodsAmount__c))) {
                    amount += 0;
                } else {
                    amount += s.GoodsAmount__c;
                }
                String value = amount + '_' + transDate;
                accountRecordTypeWithAmountAndDate.put(key, value);
            } else {
                String value = '';
                if (String.isBlank(String.valueOf(s.GoodsAmount__c))) {
                    value = '0_' + transDateNoTime;
                } else {
                    value = s.GoodsAmount__c + '_' + transDateNoTime;
                }
                accountRecordTypeWithAmountAndDate.put(key, value);
            }
        }
    }
    system.debug(accountRecordTypeWithAmountAndDate);
    createCSV(listSage, insertedSef, accountRecordTypeWithAmountAndDate);


    PageReference retURL = new PageReference('/' + insertedSef.Id);
    system.debug(retURL);
    retURL.setRedirect(true);
    return retURL;
}

public void createCSV(List < SageExtract__c > listSage, SageExtractFile__c insertedSef, Map < String, String > accountRecordTypeWithAmountAndDate) {

    List < ContentVersion > cvList = new List < ContentVersion > ();

    String csv = 'AccountCostCenter,AccountDepartment,AccountNumber,TransactionType,TransactionDate,GoodsAmount,Reference,Narrative,UniqueReferenceNumber,Source,UserNumber\n';

    List < CSVLine > csvLines = new List < CSVLine > ();
    for (String key: accountRecordTypeWithAmountAndDate.keySet()) {
        CSVLine c = new CSVLine();
        c.accountCostCentre = '';
        c.accountDepartment = '';
        c.accountNumber = key.substringBefore('_');
        c.transactionType = '1';
        c.transactionDate = accountRecordTypeWithAmountAndDate.get(key).substringAfterLast('_');
        c.goodsAmount = Decimal.valueOf(accountRecordTypeWithAmountAndDate.get(key).substringBeforeLast('_')).setScale(2, System.RoundingMode.HALF_UP);
        string stringToReference = key.substringAfter('_');
        c.reference = stringToReference.substringBefore(' ');
        c.narrative = '';
        c.uniqueReferenceNumber = insertedSef.Name;
        c.source = '3';
        c.userNumber = '22';
        csvLines.add(c);
    }

    for (CSVLine c: csvLines) {
        csv += '"' + c.accountCostCentre + '","' + c.accountDepartment + '","' + c.accountNumber + '","' + c.transactionType + '","' + c.transactionDate + '","' + c.goodsAmount + '","' + c.reference + '","' + c.narrative + '","' + c.uniqueReferenceNumber + '","' + c.source + '","' + c.userNumber + '"\n';
    }

    update listSage;

    ContentVersion cv = new ContentVersion();
    cv.Title = insertedSef.Name;
    cv.VersionData = Blob.valueOf(csv);
    cv.PathOnClient = insertedSef.Name + '.csv';
    cv.FirstPublishLocationId = insertedSef.Id;
    cvList.add(cv);
    insert cvList;      

}

public class CSVLine {

    public String accountCostCentre {
        get;
        set;
    }
    public String accountDepartment {
        get;
        set;
    }
    public String accountNumber {
        get;
        set;
    }
    public String transactionType {
        get;
        set;
    }
    public String transactionDate {
        get;
        set;
    }
    public Decimal goodsAmount {
        get;
        set;
    }
    public String reference {
        get;
        set;
    }
    public String narrative {
        get;
        set;
    }
    public String uniqueReferenceNumber {
        get;
        set;
    }
    public String source {
        get;
        set;
    }
    public String userNumber {
        get;
        set;
    }

}

}

The error that shows when i try to create a CSV with a large number of records:

Too many query locator rows: 10001 Error is in expression '{!exportToExcel}' in component in page sagecsv: Class.SageCsvFileController.__sfdc_sageFiles: line 7, column 1 Class.SageCsvFileController.exportToExcel: line 39, column 1

As the error message shows, I'm calling a method from this class on a Component, on this case when i click a button this process will run.

  • 2
    Which limits are the problem? There's no way we can tell just from looking at your code. Please edit your question to include more information. – sfdcfox Feb 1 at 15:19
  • @sfdcfox what do you need to know more? i put the error message above what do you need to know more, and i will try to explain it to you. – Nuno Carvalho Feb 1 at 15:23
0

Per the doc on StandardSetController, the max query rows is 10000

sageFiles = new ApexPages.StandardSetController(Database.getQueryLocator(
                [Select Id, Name, DatePostedtoSage__c, AccountNumber__c, GoodsAmount__c, TransactionDate__c, PostedtoSage__c FROM SageExtract__c where PostedtoSage__c = false]));

The maximum record limit for StandardSetController is 10,000 records. Instantiating StandardSetController using a query locator returning more than 10,000 records causes a LimitException to be thrown. However, instantiating StandardSetController with a list of more than 10,000 records doesn’t throw an exception, and instead truncates the records to the limit.

If generating a CSV, I think you'll need to use standard SOQL outside of the setController which will have a limit of 50000 (or 1 million if VF page is readonly)

That said, you are likely to blow up heap for very large CSVs

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.