SELECT DISTINCT in C/AL

amr_wafaamr_wafa Member Posts: 23
edited 2014-11-12 in NAV Tips & Tricks
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.

Comments

  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rhpntrhpnt Member Posts: 688
    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_wafaamr_wafa Member Posts: 23
    so this way is only recommended with small Tables?
  • kinekine Member Posts: 12,562
    Yes... but the behavior depends on exact build of NAV which you will use. Each version could have different features regarding work with SQL.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • krikikriki Member, Moderator Posts: 9,094
    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"."
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • geronimogeronimo Member Posts: 90
    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 :)
  • eleanor123eleanor123 Member Posts: 1
    Thanks for the information it would be really very helpful for me.
  • meto89meto89 Member Posts: 10
    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_BotezBogdana_Botez Member, Microsoft Employee Posts: 4
    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.
    Bogdana Botez
    Microsoft Dynamics NAV
  • okioki Member Posts: 46
    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.
      }
    }
    
Sign In or Register to comment.