Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Group data in C/AL code
Microsoft Dynamics NAV (Archived)

Group data in C/AL code

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • keoma Profile Picture
    keoma 32,675 on at
    RE: Group data in C/AL code

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

  • Bogdana Botez Profile Picture
    Bogdana Botez on at
    RE: Group data in C/AL code

     []

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Group data in C/AL code

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

  • Tomas Zaleckis Profile Picture
    Tomas Zaleckis on at
    RE: Group data in C/AL code

    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

  • Verified answer
    TharangaC Profile Picture
    TharangaC 23,116 on at
    RE: Group data in C/AL code

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

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

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: Group data in C/AL code

    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.

  • Suggested answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: Group data in C/AL code

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

    follow www.youtube.com/watch

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans