CODEWITHSUNDEEP
microsoft-dynamicsmicrosoft-dynamics-nav
hopeforall
hopeforall

Reputation: 421

grouping table by a field and finding sum in Dynamics NAV 2018

I'd like to group a table by a field- employer code and then calculate the sum of a decimal field- total contribution in that group from C/AL code. Here's my table structure

Employer No_    Total Contribution
PRTEMP005022    1817.64
PRTEMP005022    1782
PRTEMP005022    2049.3
PRTEMP005022    1568.16
PR0000247148    47750.62
PR0000247148    47532.81
PU0000400011    5314.52
PU0000400011    5314.52
PU0000400011    17225.83
PU0000400011    4509.61
STRV00000000    6088.72
STRV00000000    4065.36
STRV00000000    2191.18
STRV00000000    3485.42
STRV00000000    4709.77

How can I assign the employer code and the sum of the total contributions for that employer to variables from C/AL code

Upvotes: 0

Views: 978

Answers (1)

PhantomLight
PhantomLight

Reputation: 1

I'll assume that you have the employers in a seperate table called Employers. Otherwise you will have to create the values to group by in a temporary table (table Name/Value Buffer is userful there).

Employer.RESET();
//Employer.SETRANGE(...  filtering if needed
IF Employer.FINDSET() THEN BEGIN
  REPEAT
    YourTable.RESET();
    YourTable.SETRANGE("Employer No.", Employer."No.");
    YourTable.CALCSUMS("Total Contribution");
    TotalContribution := YourTable."Total Contribution";
    EmployerNo := YourTable."Employer No.";
  UNTIL Employer.NEXT() = 0;
END;

Please be aware that the variables TotalContribution and EmployerNo will be overwritten on every iteration of the loop so you will have to do something with them.

Upvotes: 0

Related Questions