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 ([email protected])
    }
    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
      [email protected] : TEMPORARY Record 50005;
      [email protected]1000 : TextConst 'ENU=error: record could not be found.';

    PROCEDURE [email protected]([email protected] : Integer;[email protected] : Integer;[email protected] : Boolean);
    VAR
      [email protected] : RecordRef;
      [email protected] : ARRAY [2] OF Variant;
      [email protected] : Integer;
      [email protected] : Dialog;
      [email protected] : Boolean;
      [email protected] : RecordID;
    BEGIN
      CLEAR(GData);
      GData.DELETEALL;

      WITH RecRef DO BEGIN
        OPEN(TableID);

        IF IsProgress THEN
          Progress.OPEN('[email protected]@@@@@@@@@@@@@@@@@@@@@@@@@@');

        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 [email protected]([email protected] : RecordID) : Boolean;
    BEGIN
      CLEAR(GData);
      GData.SETCURRENTKEY(Record);
      GData.SETRANGE(Record,PRecord);
      EXIT(NOT GData.ISEMPTY);
    END;

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

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

    PROCEDURE [email protected]();
    VAR
      [email protected] : RecordRef;
      [email protected] : RecordID;
      [email protected] : 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 [email protected]([email protected] : Variant;[email protected] : 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 [email protected]([email protected] : Variant) : Integer;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Decimal;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Code[20];
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Text[250];
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Integer;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Boolean;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Char;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Date;
    BEGIN
      EXIT(Val);
    END;

    LOCAL PROCEDURE [email protected]([email protected] : Variant) : Time;
    BEGIN
      EXIT(Val);
    END;

    BEGIN
    {
      Yaroslav Gaponov ([email protected])
    }
    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"...