How to Group and Consolidate General Journal Lines Using Query Object in Business Central.

Hi All,

I received a question from a fellow developer to create consolidation entries from general Journal Lines.

In this blog we will discuss how to customize business central the correct way.

How you can write code to sum up lines with multiple fields in a journal for consolidation.

Short Answer

You can consolidate General Journal Lines in Business Central by using a Query Object to group records by Posting Date, Account No., and Dimension Set ID, aggregate amounts using SUM, and then insert the consolidated results into a target journal batch using AL code.

Why This Problem Comes Up Frequently

Business Central developers often face consolidation requirements when working with General Journal Lines. A typical scenario looks like this:

• Multiple journal lines exist in a source batch

• Several lines share the same:

  • Posting Date

  • Account No.

  • Dimension Set ID

• These lines must be combined into a single line

• Amounts should be summed

• Consolidated lines should be inserted into a target batch

• Source lines should be deleted after consolidation

This exact requirement recently came up in a real Facebook developer discussion, where the developer initially tried solving it using record loops—even after applying strict batch filters.

That approach quickly becomes:

• Complex

• Error-prone

• Hard to maintain

• Inefficient for larger datasets

---

The Common (Wrong) Approach: Looping Records

Many developers attempt to solve consolidation by:

• Looping through Gen. Journal Line

• Comparing keys manually

• Using temporary tables or dictionaries

• Writing nested loops

While this can work, it introduces several problems:

• Duplicate logic

• Difficult debugging

• Poor readability

• Performance issues as data grows

Most importantly, Business Central already provides a better tool for this exact use case.

---

The Correct Tool: Query Object in Business Central

A Query Object is designed specifically to:

• Read data efficiently

• Group records by one or more fields

• Aggregate numeric values using functions like:

  • SUM

  • COUNT

  • MIN

  • MAX

Instead of looping records and grouping manually, you let the query engine do the heavy lifting.

---

Defining the Consolidation Logic

In this scenario, the consolidation key is:

• Posting Date

• Account No.

• Dimension Set ID

And the aggregated field is:

• Amount (SUM)

The Query Object is configured to:

• Use Gen. Journal Line as the data source

• Apply grouping on the consolidation key

• Apply SUM aggregation on the Amount field

• Filter by the Source Batch Name

This ensures that:

• Only relevant records are read

• Each unique key combination appears once in the result

• Amounts are already consolidated before AL code processes them

---

Reading Query Results in AL Code

It is important to clarify a common misconception:

Query Objects are read-only.

You cannot insert or modify records directly from a Query Object.

The correct pattern is:

1. Execute the Query

2. Loop through the Query results

3. Create new Gen. Journal Lines in the Target Batch

4. Insert consolidated records

5. Delete source batch lines (if required)

This separation keeps the solution:

• Clean

• Predictable

• Easy to explain and maintain

---

Moving Data Between Batches Safely

In the demonstrated solution:

• Source batch remains untouched until consolidation completes successfully

• Consolidated lines are inserted into a different batch

• Only after successful insertion are source lines deleted

This reduces the risk of:

• Partial consolidation

• Data loss

• Inconsistent journal states

---

Why Query Object Is Better Than Record Looping

Aspect	Record Loop	Query Object
Grouping	Manual	Built-in
Aggregation	Custom logic	Native SUM
Readability	Complex	Clean
Performance	Degrades with size	Optimized
Maintainability	Low	High

For grouping and aggregation scenarios, Query Object is the correct architectural choice.

---

When You Should NOT Use a Query Object

To be clear, Query Objects are not a silver bullet.

Avoid them when:

• You need row-by-row validation logic

• You must update records directly

• Simple indexed filtering is sufficient

However, for consolidation and reporting logic, Query Objects are exactly what you should use.

---

Final Thoughts

This consolidation scenario is not theoretical—it reflects real-world Business Central development challenges.

If you:

• Work with General Journals

• Handle financial data consolidation

• Write AL customizations for performance and clarity

Then mastering Query Objects is non-negotiable.

The accompanying video walks through:

• The actual Query Object

• The AL code

• A full working demo

• Best practices and pitfalls

---

Watch the Full Video Tutorial

🎥 Video: How to Group & Consolidate Gen. Journal Lines Using Query Object in Business Central

---

Want More Business Central Developer Content?

• Subscribe to the YouTube channel

• Join the Business Central developer community

• Explore advanced AL, performance, and API topics

Regards,

Saurav Dhyani

MVP | NAVUG-AllStar | Edhate Consulting