mibuso.com
Home› NAV/Navision Classic Client

Undocumented Feature or NAV is right?

zeninolegzeninoleg Member Posts: 236
edited 2009-07-29 in NAV/Navision Classic Client
Good Day,
it has been a while since i posted here since I can find answers to all my issues on this forum. And I thank you all for that!

I have come up witht he very interesting problem. I am not sure if this is a bug or NAV just works taht way. In anycase I think it is very important to put it here since I definatelly was not aware of this issue.

Here is the scenario. I am constructing the date filter by adding the periods to the string - like that:
(01/01/07..31/01/07)|(01/04/08..30/01/08)
The I take this filter and I apply this to the table taht has some sales data in it. The fioelds in the table are like that:
Date,Customer,Salesperson,Amount
And primary key is Date,Customer,Salesperson

Then i loop though filtered set of records and insert values in the temporary table.

The porblem started when somebody noticed that in certain cases some vaues are missing. After some investigation i have discovered the source:
When the filter is "(01/01/07..31/01/07)|(01/04/08..30/04/08)" everything works properly
When the filter is "(01/04/08..30/04/08)|(01/01/07..31/01/07) " it is missing values.
the order of the sort in both cases is acending.
Does that mean that if I am filtering on the Primary key column I should always obey "logical" order of the filter? To my mind this is a bit strange cause the order of the values in the "OR" expression should not influence the results of the sort.....

I am using NAV 5.1 on SQL 2005

*Update*
interestingly enough the issue was resolved in the second case by replacing FINDSET(FALSE,FALSE) before looping with FIND('-')
Best Regards,
Oleg

Comments

  • garakgarak Member Posts: 3,263
    and what for recs you are miss? How looks your datas?
    daterec.reset;
daterec.SETRANGE("Period Type",daterec."Period Type"::Tag);
daterec.SETRANGE("Period Start",01012007D,30042008D);
if daterec.findset(false,false) then begin
  repeat
    date := daterec."Period Start";
    Cust := format(daterec."Period No.");
    salespers := daterec."Period Name";
    insert;
  until daterec.next = 0;
end;
setfilter(Date,'(01/04/08..30/04/08)|(01/01/07..31/01/07)');
      date Cust salespers 01.01.07 1 MONTAG 02.01.07 2 DIENSTAG 03.01.07 3 MITTWOCH 04.01.07 4 DONNERSTAG 05.01.07 5 FREITAG 06.01.07 6 SAMSTAG 07.01.07 7 SONNTAG 08.01.07 1 MONTAG 09.01.07 2 DIENSTAG 10.01.07 3 MITTWOCH 11.01.07 4 DONNERSTAG 12.01.07 5 FREITAG 13.01.07 6 SAMSTAG 14.01.07 7 SONNTAG 15.01.07 1 MONTAG 16.01.07 2 DIENSTAG 17.01.07 3 MITTWOCH 18.01.07 4 DONNERSTAG 19.01.07 5 FREITAG 20.01.07 6 SAMSTAG 21.01.07 7 SONNTAG 22.01.07 1 MONTAG 23.01.07 2 DIENSTAG 24.01.07 3 MITTWOCH 25.01.07 4 DONNERSTAG 26.01.07 5 FREITAG 27.01.07 6 SAMSTAG 28.01.07 7 SONNTAG 29.01.07 1 MONTAG 30.01.07 2 DIENSTAG 31.01.07 3 MITTWOCH 01.04.08 2 DIENSTAG 02.04.08 3 MITTWOCH 03.04.08 4 DONNERSTAG 04.04.08 5 FREITAG 05.04.08 6 SAMSTAG 06.04.08 7 SONNTAG 07.04.08 1 MONTAG 08.04.08 2 DIENSTAG 09.04.08 3 MITTWOCH 10.04.08 4 DONNERSTAG 11.04.08 5 FREITAG 12.04.08 6 SAMSTAG 13.04.08 7 SONNTAG 14.04.08 1 MONTAG 15.04.08 2 DIENSTAG 16.04.08 3 MITTWOCH 17.04.08 4 DONNERSTAG 18.04.08 5 FREITAG 19.04.08 6 SAMSTAG 20.04.08 7 SONNTAG 21.04.08 1 MONTAG 22.04.08 2 DIENSTAG 23.04.08 3 MITTWOCH 24.04.08 4 DONNERSTAG 25.04.08 5 FREITAG 26.04.08 6 SAMSTAG 27.04.08 7 SONNTAG 28.04.08 1 MONTAG 29.04.08 2 DIENSTAG 30.04.08 3 MITTWOCH

    So, when i filter with setfilter(Date,'(01/04/08..30/04/08)|(01/01/07..31/01/07)'); all rec, but not 01.08.08 and 01.02.07, will be displayed.

    PS: im sure you filter often on "Customer No." and "Sales Person Code" and then on the date.
    So, its better when the key is "Customer No.", "Salesperson Code" (or first the salesperson if you start with this ever) and then the date.
    Do you make it right, it works too!
  • zeninolegzeninoleg Member Posts: 236
    Hi Rene, thanks for your reply.
    I am missing part of the later period, so in your example I will be missing maybe 01/04/08. This is bizzare...

    Just to answer your other question - in our company we are very often try to find total sales for the specific period and we can have more than 1 salesperson attached to the customer, that is why we put Date first and then SP and Customer.

    PS: I have even put a counter inside my looop and display it after the loop is done along with COUNT of the records in the table, the counter is lower....


    Here is your example with the problem;
    First make sure that the form is NOT on the temporary table. Then enter this code in your OnOpenForm trigger. Run it and get the different counts

    daterec.RESET;
daterec.SETRANGE("Period Type",daterec."Period Type"::Date);
daterec.SETRANGE("Period Start",010107D,311208D);
IF daterec.FINDSET(FALSE,FALSE) THEN BEGIN
  REPEAT
    date := daterec."Period Start";
    cust := FORMAT(daterec."Period No.");
    salespers := daterec."Period Name";
    INSERT;
    
    date := daterec."Period Start";
    cust := FORMAT(daterec."Period No.") + '1';
    salespers := daterec."Period Name" + '2';
    INSERT;
    
    date := daterec."Period Start";
    cust := daterec."Period Name";
    salespers := FORMAT(daterec."Period No.");
    INSERT;
  
  UNTIL daterec.NEXT = 0;
END;

SETFILTER(date,'(01/01/08..30/04/08)|(01/01/07..01/04/07)');

IF FINDSET(FALSE,FALSE) THEN BEGIN
  REPEAT
    Counter +=1;

  UNTIL NEXT = 0;
END;

MESSAGE('Counter %1, Count %2', Counter, COUNT);
    Best Regards,
    Oleg
  • garakgarak Member Posts: 3,263
    i get 636 Recs. and thats ok. For every date there are 3 recs inserted.
    And the filter '(01/01/08..30/04/08)|(01/01/07..01/04/07)' gets 212 recs from table Date * 3 = 636
    How do you loop through the recs? Which Find statement and is there somewhere a filter before?
    Do you make it right, it works too!
  • kinekine Member Posts: 12,562
    Are you somehow changing values in the record on which you are looping? For me it looks like the cursor get lost because some changes of filters or values...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garakgarak Member Posts: 3,263
    kine wrote:
    Are you somehow changing values in the record on which you are looping? For me it looks like the cursor get lost because some changes of filters or values...

    thats right. In my oppinion the courser could here the problem. Maybe a wrong filter or some changes of the fields that are filtered so that NAv think he is at the end (next=0)
    Do you make it right, it works too!
  • zeninolegzeninoleg Member Posts: 236
    Thanks for your replies.
    No, I am not changing any primary key values or any values at all. Just straight read.

    When I run this code the count is different. - I get Counter - 501 - Count 636 
    OBJECT Table 50179 test1
{
  OBJECT-PROPERTIES
  {
    Date=27/07/09;
    Time=[ 3:17:23 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  FIELDS
  {
    { 1   ;   ;date                ;Date           }
    { 2   ;   ;cust                ;Code10         }
    { 3   ;   ;salespers           ;Code10         }
  }
  KEYS
  {
    {    ;date,cust,salespers                     ;Clustered=Yes }
  }
  CODE
  {

    BEGIN
    END.
  }
}

OBJECT Form 50100 count test
{
  OBJECT-PROPERTIES
  {
    Date=27/07/09;
    Time=[ 3:16:01 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=9790;
    Height=6710;
    TableBoxID=1000000000;
    SourceTable=Table50179;
    OnOpenForm=BEGIN
                 daterec.RESET;
                 daterec.SETRANGE("Period Type",daterec."Period Type"::Date);
                 daterec.SETRANGE("Period Start",010107D,311208D);
                 IF daterec.FINDSET(FALSE,FALSE) THEN BEGIN
                   REPEAT
                     date := daterec."Period Start";
                     cust := FORMAT(daterec."Period No.");
                     salespers := daterec."Period Name";
                     INSERT;

                     date := daterec."Period Start";
                     cust := FORMAT(daterec."Period No.") + '1';
                     salespers := daterec."Period Name" + '2';
                     INSERT;

                     date := daterec."Period Start";
                     cust := daterec."Period Name";
                     salespers := FORMAT(daterec."Period No.");
                     INSERT;

                   UNTIL daterec.NEXT = 0;
                 END;

                 SETFILTER(date,'(01/01/08..30/04/08)|(01/01/07..01/04/07)');

                 IF FINDSET(FALSE,FALSE) THEN BEGIN
                   REPEAT
                     Counter +=1;

                   UNTIL NEXT = 0;
                 END;

                 MESSAGE('Counter %1, Count %2', Counter, COUNT);
               END;

  }
  CONTROLS
  {
    { 1000000000;TableBox;220 ;220  ;9350 ;5500 ;HorzGlue=Both;
                                                 VertGlue=Both }
    { 1000000001;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=date }
    { 1000000002;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000001;
                                                 InColumnHeading=Yes }
    { 1000000003;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=cust }
    { 1000000004;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000003;
                                                 InColumnHeading=Yes }
    { 1000000005;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=salespers }
    { 1000000006;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000005;
                                                 InColumnHeading=Yes }
    { 1000000007;CommandButton;2530;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 Default=Yes;
                                                 PushAction=LookupOK;
                                                 InvalidActionAppearance=Hide }
    { 1000000008;CommandButton;4950;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 Cancel=Yes;
                                                 PushAction=LookupCancel;
                                                 InvalidActionAppearance=Hide }
    { 1000000009;CommandButton;7370;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 PushAction=FormHelp }
  }
  CODE
  {
    VAR
      daterec@1000000000 : Record 2000000007;
      Counter@1000000001 : Integer;

    BEGIN
    END.
  }
}
    Best Regards,
    Oleg
  • kinekine Member Posts: 12,562
    There were some bugs in plain NAV 5.00SP1 (we have som problems with loops too),try to use latest build...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garakgarak Member Posts: 3,263
    mhm, this could be the problem. i tested it with one of the last Hotfixes because we had some other probs with the Sp1.
    Maybe this https://mbs.microsoft.com/knowledgebase ... -US;957276 could help.

    Here a list of all known Hotfixes for 5.0SP1 (german)
    http://blogs.msdn.com/german_nav_develo ... ack-1.aspx

    and here the same list from Waldo's website (english).
    http://dynamicsuser.net/blogs/waldo/arc ... dated.aspx

    Regards
    Do you make it right, it works too!
Sign In or Register to comment.