"Nice" Error with SETSELECTIONFILTER() (only under

garak

Now it's time, that i post an error.

The following error is reproducable on Cronus 4.03.

What i have:
4.03 (Client, Database)
SQL Server 2005

Import the textfile (Form 50013) into your cronus or other SQL database (if this error comes under SQL 2000, i doesn't check).

The Form is based on table 27. In the form you see a textbox with a Filterstring (*farb*).
These fitlersting is a filter for the Search Description.
Insert a filterstring, or don't change the value of the filterstring, press then Button "Press 1.".
Then select a line (blue marked). Note, the Search description of these line must >= 21 chars.
Then press button "Press 2."
Then press button "Press 3."
Then press button "Press 4."

Enjoy the errormessage like:

---------------------------
Microsoft Business Solutions-Navision
---------------------------
The Item table contains a field with a Code or Text value that is too large for the SQL Server data type:

Record: "No.='FARBE, SCHWARZ DFEWFWEF'"
Field: No.='FARBE, SCHWARZ DFEWFWEF'
SQL type: VARCHAR(20)

---------------------------
OK   
---------------------------

The reason: with button 1. we change the key for an sorting to "Search Description". But the setselectionfilter means, we use the PK.

Under the Native database all work fine .....

OBJECT Form 50013 Free Form 16
{
  OBJECT-PROPERTIES
  {
    Date=15.08.08;
    Time=10:59:09;
    Modified=Yes;
    Version List=FREI;
  }
  PROPERTIES
  {
    Width=15180;
    Height=10010;
    SourceTable=Table27;
    OnOpenForm=BEGIN
                 Text := '*farb*';
               END;

  }
  CONTROLS
  {
    { 1000000000;TableBox;220 ;220  ;14960;6050  }
    { 1000000002;TextBox;1905 ;1980 ;1700 ;440  ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="No." }
    { 1000000003;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000002;
                                                 InColumnHeading=Yes }
    { 1000000004;TextBox;3605 ;1980 ;4400 ;440  ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=Description }
    { 1000000005;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000004;
                                                 InColumnHeading=Yes }
    { 1000000006;TextBox;6085 ;1980 ;5610 ;440  ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Search Description" }
    { 1000000007;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000006;
                                                 InColumnHeading=Yes }
    { 1119402000;TextBox;7700 ;1320 ;1700 ;440  ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=STRLEN("Search Description") }
    { 1119402001;Label  ;0    ;0    ;0    ;0    ;ParentControl=1119402000;
                                                 InColumnHeading=Yes }
    { 1000000001;TextBox;2420 ;7150 ;5170 ;440  ;SourceExpr=Text }
    { 1000000008;CommandButton;10780;6380;2200;550;
                                                 CaptionML=[DEU=Press 1.;
                                                            ENU=Press 1.];
                                                 OnPush=BEGIN
                                                          SETCURRENTKEY("Search Description");

                                                          SETFILTER("Search Description",Text);
                                                        END;
                                                         }
    { 1000000009;CommandButton;10780;7700;2200;550;
                                                 CaptionML=[DEU=Press 2.;
                                                            ENU=Press 2.];
                                                 OnPush=BEGIN
                                                          GetSelection();
                                                        END;
                                                         }
    { 1000000011;CommandButton;10780;8470;2200;550;
                                                 CaptionML=[DEU=Press 3.;
                                                            ENU=Press 3.];
                                                 OnPush=VAR
                                                          SelItem@1000000000 : Record 27;
                                                        BEGIN
                                                          RESET;
                                                        END;
                                                         }
    { 1000000010;TextBox;440  ;7700 ;7260 ;440  ;SourceExpr='Filter:' + GETFILTERS + ' Key:' + CURRENTKEY }
    { 1000000012;CommandButton;10780;9240;2200;550;
                                                 CaptionML=[DEU=Press 4.;
                                                            ENU=Press 4.];
                                                 OnPush=BEGIN
                                                          GetSelection();
                                                        END;
                                                         }
    { 1000000013;Label  ;8030 ;7040 ;7150 ;440  ;HorzAlign=Center;
                                                 ForeColor=16711680;
                                                 FontSize=10;
                                                 FontBold=Yes;
                                                 LeaderDots=No;
                                                 CaptionML=[DEU=2. Select a Line(s), it must be "blue";
                                                            ENU=2. Select a Line(s), it must be "blue"] }
    { 1000000014;Label  ;440  ;7150 ;1870 ;440  ;CaptionML=DEU=Filter (Search Descr) }
  }
  CODE
  {
    VAR
      Text@1000000000 : Text[30];

    PROCEDURE GetSelection@1000000001();
    VAR
      SelItem@1000000000 : Record 27;
    BEGIN
      CurrForm.SETSELECTIONFILTER(SelItem);

      IF SelItem.FINDSET THEN BEGIN
        REPEAT
          GET(SelItem."No.");
          //mark(true);
        UNTIL SelItem.NEXT = 0;
      END;

      //markedonly(true);
    END;

    BEGIN
    END.
  }
}

It's a nice :bug: ;-)
And the joke is, if you click in the TableBox before you press button 4, all works fine :roll:

Regards

wic

I'm not sure I understand the whole thing (I didn't try) but it looks like some of the problems with the Native LENGHT and SQL length of fields that we encounter when converting a Native DB to a SQL DB.
We had this for about 4 years ago...
The problem is how NAV calculates the field lenght and how SQL does. I don't remember exactly but there is something like nav counts char and SQL bytes or something like that.
Does this give you further infos?

DaveT

Hi Garak,

Looks like a :bug: in the setselectionfilter. If you add a SETCURRENTKEY("Search Description"); under button 3 after the reset it works fine. Looks like the setselectionfilter applies the filter to the first element of the key that is currently selected. e.g. press 1,2,3 and then set the sort (standard shift+F8 ) to Cost is Adjusted,Allow Online Adjustment - it tries to put the filter on the boolean cost is adjusted :shock:

wic

morality: I should try your code! [-(

DaveT

wic wrote:

morality: I should try your code! [-(

:? not sure what you mean