Group data in C/AL code

(0) Share

Report

Posted on by Community Member

How can I loop a result dataset to group per Document No.? Let me explain what I'm doing:

I'm currently working to create a report that sums the Debit and Credit of all the General Ledger Entries group by the Docyment No., so far I have been able to group and get the actual SUM of the fields. The problems is that it only takes the first document No.

Here is what I have so far in code:

SETRANGE("Document No.", "G/L Entry"."Document No.");
SETRANGE("External Document No.", "G/L Entry"."External Document No.");
CALCSUMS("Debit Amount");
CALCSUMS("Credit Amount");
Total_Debit  := "Debit Amount";
Total_Credit := "Credit Amount";

This code executes correctly, but it only returns the first document.

Am I missing something?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

Suggested answer

keoma 32,729 on at

Like (0)

Report

edit the report layout and add the needed grouping to the data line.

follow www.youtube.com/watch

Was this reply helpful? Yes No
Verified answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (1)

Report

William,

For this scenario you can use the queries, below link as an example

msdn.microsoft.com/.../hh169472(v=nav.70).aspx

or you can use a temporary table and update/insert that table by looping through the set. i.e. the temporary table will have the Document No. has primary key other column as debit and credit amount, while looping you will check the table has that document no. if so you add the amount if not will create a new one and will update in next occurrence.

If this is for a report then you can achieve it in report using grouping easily.

Was this reply helpful? Yes No
Verified answer

Tharanga Chandrasekara 23,118 on at

Like (2)

Report

Use Query to achieve this. Using query you can group the table data as your requirement.

community.dynamics.com/.../152.select-distinct-with-queries

Was this reply helpful? Yes No
Tomas Zaleckis on at

Like (0)

Report

Hi Williams

Have a look at report 7315 "Calculate Whse. Adjustment", in "Integer - OnAfterGetRecord" grouping is done on Warehouse Entry table onm combination of fields "Item No.", "Variant Code", "Location Code", "Bin Code", "Unit of Measure Code", "Lot No.", "Serial No.".

You can use this as example to create grouping on "Document No." and "External Document No." for your scenario.

Tomas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Suresh, I ended up doing that. Everything is working now thanks you for your time.

Was this reply helpful? Yes No
Bogdana Botez on at

Like (0)

Report

[]

Was this reply helpful? Yes No
keoma 32,729 on at

Like (0)

Report

i think you wanted to upload an image. didn't work.

Was this reply helpful? Yes No