SELECT DISTINCT in C/AL

amr_wafa

Hi all,

This is maybe the biggest issue a C/AL Coder can face, and i've found lots of solutions some good some bad. This is how i do it and till now it is successful for me.

in this code I want to SELECT DISTINCT "No." FROM Purchase Archive

OBJECT Codeunit 50222 Test
{
  OBJECT-PROPERTIES
  {
    Date=05/14/11;
    Time=[ 4:22:54 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            PA1.SETRANGE(PA1."No.",'PO0030314','PO0030380');
            PA1.FINDSET;
            REPEAT
              PA2.RESET;
              PA2.SETRANGE("No.",PA1."No.");
              PA2.FINDLAST;
              PA1 := PA2;
              MESSAGE(FORMAT(PA2));
            UNTIL PA1.NEXT=0;
          END;
  }
  CODE
  {
    VAR
      PA1@1000000000 : Record 5109;
      PA2@1000000001 : Record 5109;

    BEGIN
    END.
  }
}

Test and let me know if u face any problem.

Thank You.

kine

Of course, it will work, but it could be performance problem if thee is too much entries and you are using SQL server. Because in this case, the cache will be a little off...

rhpnt

I would use your suggested way only in extreme and non avoidable situations. If we are talking archived docs then there is the field "Version No." which guarantees uniqueness. I would rather use a report which would skip an entry that was already selected/printed (usage of the right table key important).

amr_wafa

so this way is only recommended with small Tables?

kine

Yes... but the behavior depends on exact build of NAV which you will use. Each version could have different features regarding work with SQL.

kriki

Check also this way : http://www.mibuso.com/howtoinfo.asp?FileID=22 Point "Make a total amount per "Gen. Bus. Posting Group" and "Gen. Prod. Posting Group" of table 15:"G/L Entry"."

geronimo

Dont forget you need to sort in the correct way for it to work. For "no." this isnt a problem but if for example you'd want to group them by city or so you'd need to set a key for city as the findlast wont work right otherwise.

Btw: interesting howto kriki, thanks

eleanor123

Thanks for the information it would be really very helpful for me.

meto89

This is btw even how the Cronus DB does it on Form 6510 in the function AddToGlobalRecordSet:

TempTrackingSpecification.SETCURRENTKEY("Lot No.","Serial No.");
IF TempTrackingSpecification.FIND('-') THEN
  REPEAT
    TempTrackingSpecification.SETRANGE("Lot No.",TempTrackingSpecification."Lot No.");
    TempTrackingSpecification.SETRANGE("Serial No.",TempTrackingSpecification."Serial No.");

...

    TempTrackingSpecification.FIND('+');
    TempTrackingSpecification.SETRANGE("Lot No.");
    TempTrackingSpecification.SETRANGE("Serial No.");
  UNTIL TempTrackingSpecification.NEXT = 0;

Bogdana_Botez

If you're on versions from NAV 2013 up, you can use a query to do this.
There's a NAV design pattern describing it: SELECT DISTINCT with Queries.

oki

If you don't have 2013 & up you can use a while loop for a very quick efficient distinct selection also on tables with many records:

Your object modified:

OBJECT Codeunit 50222 Test
{
  OBJECT-PROPERTIES
  {
    Date=05/14/11;
    Time=[ 4:22:54 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            PA1.SETRANGE(PA1."No.",'PO0030314','PO0030380');
            WHILE (PA1.FINDFIRST) DO BEGIN

              // add your code here:
              MESSAGE(FORMAT(PA1));
           
              PA1.SETFILTER("No.",'>%1&<=%2',PA1."No.",'PO0030380');
            END; // WHILE
          END;
  }
  CODE
  {
    VAR
      PA1@1000000000 : Record 5109;

    BEGIN
    END.
  }
}