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

letayoknow

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 each employer code and the sum of the total contributions for that employer to variables from C/AL code

da_neal

1. Use temporary table to store calculated totals
2. Use Query to calculate total
3. Create flowfield in Employee table to calculate totals
4. Use MS Sql Query with dotnet

letayoknow

Can you please share a sample code?

da_neal

IF Entry.FINDSET THEN
  REPEAT
    IF TotalTemp.GET(Entry.Code) THEN BEGIN
      TotalTemp.Amount += Entry.Amount;
      TotalTemp.MODIFY;
    END ELSE BEGIN
      TotalTemp.Code := Entry.Code
      TotalTemp.Amount := Entry.Amount;
      TotalTemp.INSERT;
    END;
  UNTIL Entry.NEXT = 0;