STEFANO DEMILIANI

Tuning Dynamics NAV to the max: GROUP BY and DISTINCT

I know that this is not the first time that I write about this topic, but you can think at this as a “personal mission”: I want GROUP BY and DISTINCT on C/AL!!

If you’re a NAV developer and you know a bit of SQL, you can understand that a big lack on C/AL is the possibility to make a GROUP BY and DISTINCT query in an “atomic manner”. Let’s see a simple example (but that comes from a real case):

You’ve a table with data like these:

and you need to perform these tasks:

1. group these data by the field called “Cod. Centro”
2. calculate the sum of “Importo” for every “Cod. Centro”
3. display the results on a form (“Cod. Centro” must be distinct and “Importo” must be the total).

If you work directly via T-SQL, you can perform this task simply with this query:

select distinct [Cod_ Centro], sum(Importo) as Total
from
[NAV$Mov_ Vis_ Analisi CoAn]
where [Codice Visualizzaz_ Analisi]='DRG'
group by [Cod_ Centro]

and you’ll obtain the result as follow:

If you work directly with NAV and C/AL, you’ve to write something like these:

//Raggruppo anche per VOCE
IF MovAnalisi.FINDSET(FALSE,FALSE) THEN
REPEAT
BEGIN
  IF MovAnalisi.MARK=FALSE THEN
  BEGIN
    //Save the primary key of the record that I want to summarize
    NumMov:=MovAnalisi.Nr;

    MovAnalisi2.COPYFILTERS(MovAnalisi);
    MovAnalisi2.SETRANGE("Dim. Voce",MovAnalisi."Dim. Voce");
    MovAnalisi2.SETRANGE("Val. Dim. Voce",MovAnalisi."Val. Dim. Voce");
    TotQta:=0;
    TotImporto:=0;
    IF MovAnalisi2.FINDSET THEN
    REPEAT
    BEGIN
      TotQta+=MovAnalisi2.Quantità;
      TotImporto+=MovAnalisi2.Importo;
      //Mark the record
      IF MovAnalisi.GET(MovAnalisi2.Nr) THEN
        MovAnalisi.MARK(TRUE);
    END
    UNTIL MovAnalisi2.NEXT=0;

    //Here you can write the summarized record

    //Re-position to the original record
    MovAnalisi.GET(NumMov);