How to Minimize GL Summary Records

Overview

GoldFinch uses GL Summary records to generate financial reports in real time.

Salesforce limits an Apex transaction to retrieving 50,000 records in a single SOQL query. For most organizations, this limit is sufficient. However, organizations that use multiple Accounting Variables may exceed this limit when running financial reports such as the Balance Sheet or Trial Balance.

One way to reduce the number of GL Summary records is to decrease the number of Accounting Variables retained in historical summaries. By consolidating older GL Summary records, GoldFinch can significantly reduce the total number of summary records without affecting the financial totals or report accuracy.

Custom Setting

The compression process is controlled by the following custom setting:

CustomGLSummaryCompression

Format

YYYY/MM/Variable1/Variable2/Variable3/Variable4

where:

• YYYY – Cutoff year
• MM – Cutoff month
• Variable1 – Yes or No
• Variable2 – Yes or No
• Variable3 – Yes or No
• Variable4 – Yes or No

Example

2022/12/Yes/No/No/No

This means that for GL Entries dated on or before December 2022:

• Accounting Variable 1 is retained.
• Accounting Variables 2, 3, and 4 are excluded from the recreated GL Summary records.

As a result, multiple historical GL Summary records can be consolidated into fewer records while preserving the required reporting dimensions.

Compression Procedure

Step 1. Configure CustomGLSummaryCompression

Configure the CustomGLSummaryCompression custom setting by specifying:

• The cutoff year and month.
• Which Accounting Variables (1–4) should be retained for GL Entries on or before the cutoff date.

These settings determine how historical GL Summary records will be recreated.

Step 2. Delete Historical GL Summary Records

Delete all GL Summary records dated on or before the configured cutoff date.

When a large number of GL Summary records are deleted, Salesforce may require 24–48 hours or longer to automatically clear the GL Summary lookup field on related GL Entry records.

Known Salesforce Lookup Issue

Salesforce may leave orphaned lookup IDs after the referenced GL Summary records have been deleted.

Symptoms include:

• The GL Summary lookup displays Unknown Record when editing a GL Entry.
• Clicking the lookup returns No records found.
• A SOQL query still returns a value in the GL_Summary__c field even though the referenced record no longer exists.

To minimize this issue, delete historical GL Summary records in smaller batches whenever possible.

Step 3. Clear Orphaned GL Summary Lookups (Optional)

If you do not want to wait for Salesforce to automatically clear the lookup fields, deploy a batch routine to remove orphaned lookup references.

The batch should update only GL Entry records that satisfy both of the following conditions:

• The GL Summary lookup field is populated.
• The referenced GL Summary record no longer exists.

public class GLSummaryOrphanCleanupBatch
    implements Database.Batchable<SObject> {

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([
            SELECT Id
            FROM GL_Entry__c
            WHERE GL_Summary__c != null
            AND GL_Summary__c NOT IN (
                SELECT Id FROM GL_Summary__c
            )
        ]);
    }

    public void execute(
        Database.BatchableContext bc,
        List<GL_Entry__c> scope
    ) {
        CompanyMgmt.systemCall = true;

        for (GL_Entry__c entry : scope) {
            entry.GL_Summary__c = null;
        }

        update scope;
    }

    public void finish(Database.BatchableContext bc) {
    }
}

Run the batch:

Database.executeBatch(
    new GLSummaryOrphanCleanupBatch(),
    200
);

After the batch completes, verify that no GL Entry records contain orphaned GL Summary lookup values.

Step 4. Recreate GL Summary Records

Run either of the following routines:

• Daily Adjust Cost
• BatchGLPost

These routines recreate GL Summary records according to the CustomGLSummaryCompression settings.

Historical GL Entries will be summarized using only the retained Accounting Variables, reducing the total number of GL Summary records while preserving the required reporting information.

Important Considerations

Before performing GL Summary compression:

• Back up or export the affected GL Summary records.
• Test the entire process in a sandbox before running it in production.
• Delete only GL Summary records. Do not delete GL Entry records.
• Delete large numbers of GL Summary records in smaller batches whenever possible.
• Verify that all orphaned GL Summary lookup references have been cleared before recreating summaries.
• Allow the summarization process to complete before running financial reports.
• Compare the Balance Sheet, Income Statement, and Trial Balance before and after compression.

Expected Result

The compression process should:

• Reduce the total number of GL Summary records.
• Improve the performance of financial reports.
• Help prevent Salesforce's 50,000-row SOQL query limit from being exceeded.
• Preserve the same total debit, credit, and net balances as before compression.