Sorting without key (own index)

Yaroslav_Gaponov

Hi

Sometimes need sort some table by field without create addition key. I tried to do this in C/AL.
Thank you for your attention.

Below you can look for my result.

OBJECT Table 50005 Sorting Data
{
  OBJECT-PROPERTIES
  {
    Date=24-11-08;
    Time=[12:00:00 ];
    Version List=Sorting;
  }
  PROPERTIES
  {
  }
  FIELDS
  {
    { 1   ;   ;No.                 ;Integer       ;TableRelation=Integer;
                                                   AutoIncrement=Yes }
    { 2   ;   ;Record              ;RecordID       }
  }
  KEYS
  {
    {    ;No.                                     ;Clustered=Yes }
    {    ;Record                                   }
  }
  CODE
  {

    BEGIN
    {
      Yaroslav Gaponov (yagaponov@yahoo.com)
    }
    END.
  }
}

OBJECT Codeunit 50005 Sorting Mgt.
{
  OBJECT-PROPERTIES
  {
    Date=24-11-08;
    Time=[12:00:00 ];
    Version List=Sorting;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            Test();
          END;

  }
  CODE
  {
    VAR
      GData@1001 : TEMPORARY Record 50005;
      Text001@1000 : TextConst 'ENU=error: record could not be found.';

    PROCEDURE Sorting@1(TableID@1000 : Integer;FieldID@1001 : Integer;IsProgress@1005 : Boolean);
    VAR
      RecRef@1002 : RecordRef;
      Val@1004 : ARRAY [2] OF Variant;
      I@1003 : Integer;
      Progress@1006 : Dialog;
      Found@1007 : Boolean;
      RecID@1008 : RecordID;
    BEGIN
      CLEAR(GData);
      GData.DELETEALL;

      WITH RecRef DO BEGIN
        OPEN(TableID);

        IF IsProgress THEN
          Progress.OPEN('Sorting@1@@@@@@@@@@@@@@@@@@@@@@@@@@');

        FOR I := 1 TO COUNT DO BEGIN

          IF IsProgress THEN
            Progress.UPDATE(1,ROUND(10000*I/COUNT,1));

          // search a new first record...
          IF FIND('-') THEN
            REPEAT
              RecID  := RecRef.RECORDID;
              Val[1] := RecRef.FIELD(FieldID).VALUE;
              Found := IsRecordID(RecRef.RECORDID);
            UNTIL (NEXT = 0) OR NOT Found;

          // search a new minimum record...
          IF NOT Found THEN BEGIN
              REPEAT
                Val[2] := RecRef.FIELD(FieldID).VALUE;
                IF Cmp(Val[1],Val[2]) THEN
                  IF NOT IsRecordID(RecRef.RECORDID) THEN BEGIN
                    RecID  := RecRef.RECORDID;
                    Val[1] := Val[2];
                  END;
              UNTIL NEXT = 0;

              // add a new index record...
              CLEAR(GData);
              GData."No."  := I;
              GData.Record := RecID;
              GData.INSERT;
          END;

        END;

        IF IsProgress THEN
          Progress.CLOSE();

        CLOSE();
      END;
    END;

    LOCAL PROCEDURE IsRecordID@2(PRecord@1000 : RecordID) : Boolean;
    BEGIN
      CLEAR(GData);
      GData.SETCURRENTKEY(Record);
      GData.SETRANGE(Record,PRecord);
      EXIT(NOT GData.ISEMPTY);
    END;

    PROCEDURE First@3() Result : Text[250];
    BEGIN
      CLEAR(GData);
      Result := Text001;
      IF GData.FIND('-') THEN
        Result := FORMAT(GData.Record);
      EXIT(Result);
    END;

    PROCEDURE Next@4() Result : Text[250];
    BEGIN
      Result := Text001;
      IF GData.NEXT > 0 THEN
        Result := FORMAT(GData.Record);
      EXIT(Result);
    END;

    PROCEDURE Test@5();
    VAR
      refRec@1000 : RecordRef;
      recID@1001 : RecordID;
      I@1002 : Integer;
    BEGIN
      CASE STRMENU('Country,Sales Header',0) OF
        1: Sorting(DATABASE::Country,2,TRUE);           // Example: sorting Country table by Name field.
        2: Sorting(DATABASE::"Sales Header",19,TRUE);   // Example: sorting Sales Header table by Document Date field.
      END;
      IF EVALUATE(recID,First) THEN
         REPEAT
          I := I + 1;
          IF refRec.GET(recID) THEN
            MESSAGE('%1. <%2> <%3>',I,FORMAT(refRec.FIELD(1).VALUE),FORMAT(refRec.FIELD(2).VALUE));
         UNTIL NOT EVALUATE(recID,Next);
    END;

    LOCAL PROCEDURE Cmp@6(Val1@1000 : Variant;Val2@1001 : Variant) Result : Boolean;
    BEGIN
      CASE TRUE OF
        Val1.ISINTEGER:  EXIT(ToInt(Val1)    >   ToInt(Val2));
        Val1.ISDECIMAL:  EXIT(ToDec(Val1)    >   ToDec(Val2));
        Val1.ISTEXT:     EXIT(ToText(Val1)   >   ToText(Val2));
        Val1.ISCODE:     EXIT(ToCode(Val1)   >   ToCode(Val2));
        Val1.ISBOOLEAN:  EXIT(ToBool(Val1)   >   ToBool(Val2));
        Val1.ISOPTION:   EXIT(ToOption(Val1) >   ToOption(Val2));
        Val1.ISCHAR:     EXIT(ToChr(Val1)    >   ToChr(Val2));
        Val1.ISDATE:     EXIT(ToDate(Val1)   >   ToDate(Val2));
        Val1.ISTIME:     EXIT(ToTime(Val1)   >   ToTime(Val2));
      END;
    END;

    LOCAL PROCEDURE ToInt@7(Val@1000 : Variant) : Integer;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToDec@8(Val@1000 : Variant) : Decimal;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToCode@9(Val@1000 : Variant) : Code[20];
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToText@10(Val@1000 : Variant) : Text[250];
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToOption@11(Val@1000 : Variant) : Integer;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToBool@13(Val@1000 : Variant) : Boolean;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToChr@15(Val@1000 : Variant) : Char;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToDate@16(Val@1000 : Variant) : Date;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE ToTime@17(Val@1000 : Variant) : Time;
    BEGIN
      EXIT(Val);
    END;

    BEGIN
    {
      Yaroslav Gaponov (yagaponov@yahoo.com)
    }
    END.
  }
}

bvbeek

Good job =D>

arcull

Looks very interesting, but unfortunatelly I don't understand how is it supposing to work. As a test you provided the sorting of country table by Name, but after running the CU I get no records sorted in the temporary table "GData". I gues I am missing something. Would you bee so kind to explain it briefly, how to use it. Thank you very much.

Yaroslav_Gaponov

Hi

This not tool - just bit of C/AL code for use in your projects, if you need sorting some table without key.

DenSter

So instead of creating the key you need, you are suggesting we program filling a temporary table and then filter a record set based on that other table? Wouldn't it be much easier to just create the key you need? I'm not saying this is worthless, just having a hard time figuring out a day to day scenario of how we'd use this.

Miklos_Hollender

I figure the point is to avoid the unnecesary cost of maintaining rarely used indexes, but, I think it's easier just to create any key you need and turn off the MaintainSQLIndex property.

kriki

You can also create a new table with all the fields and keys you need.
If you use the table ONLY as a temporary table, IT IS NOT NECESSARY TO PUT IT IN THE LICENSE!

Yaroslav_Gaponov

Hi

Ok, you right...
...but if you want another sorting algorithm - not just alphabetically?


You have some hard sorting field and hard algorithm like value "1 Yaroslav Gaponov" and I need sorting as "Gaponov Yaroslav 1"...