Hi there, how can I implement the select statement below as a SETFILTER for a Navision list page?

Junav
Junav Member Posts: 3
in NAV Three Tier 
SELECT Field1 FROM Table1 WHERE Field1 <> 'a' OR ( Field1 = 'a' AND Field2 = 1 )

Answers

  • allenyyzhou
    allenyyzhou Member Posts: 142
    Hi

    One way to achieve this is use MARK.

    Table1.RESET;
    Table1.SETFILTER(Field1, '<>a');
    IF Table1..FINDSET THEN
    REPEAT
    Table1.MARK;
    UNTIL Table1.NEXT <= 0;

    Table1.RESET;
    Table1.SETRANGE(Field1, 'a');
    Table1.SETRANGE(Field2, 1);
    IF Table1..FINDSET THEN
    REPEAT
    Table1.MARK;
    UNTIL Table1.NEXT <= 0;

    Table1.MARKEDONLY;

    Page1.SETTABLEVIEW(Table1);
    Page1.RUN;
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    I like the temptable method better. Performance etc. Set the SourceTableTemporary property to yes. Run the Page. It should be empty. If not something is wrong!

    OnOpenPage
    IF Table1.FINDSET(FALSE,FALSE) THEN REPEAT
    If ((Table1.Field1 <> 'a') OR ( (Table1.Field1= 'a') AND (Table1.Field2 = 1))) THEN BEGIN
    Rec := Table1;
    Rec.INSERT;
    END;
    UNTIL Table1.NEXT=0;

    At least this way you know the only way the table is going to be hit, mostly with a full table scan, is when you open the page. If you have sixty gazillion records there, you can tell users to open the Page then go drink a coffee. But I have no idea what happens in database hits if you have the Mark method and then you scroll up and down.

  • allenyyzhou
    allenyyzhou Member Posts: 142
    Hi Miklos

    Yes, I agree, temporary table is better.

  • Junav
    Junav Member Posts: 3
    Hi Miklos_Hollender,

    The solution you've proposed works and is better. However, my list is huge and gets accessed by users so often. speed is thereby decreased significantly.
  • jreynolds
    jreynolds Member Posts: 175
    Logical disjunction (OR) is distributive over logical conjunction (AND) – so A OR (B AND C) is equivalent to (A OR B) AND (A OR C). Therefore, your expression is equivalent to ((Field1 <> ‘a’) OR (Field1 = ‘a’) AND ((Field1 <> ‘a’) OR (Field2 = 1). The left side of the AND is always TRUE and therefore doesn’t really matter. Therefore, you expression is also equivalent to just the right side of the AND.

    This can be handled very nicely in NAV using FILTERGROUP -1:

    Table1.RESET;
    Table1.FILTERGROUP(-1);
    Table1.SETFILTER(Field1,’<>a’);
    Table1.SETRANGE(Field2,1);
    Table1.FILTERGROUP(0);
  • jreynolds
    jreynolds Member Posts: 175
    Sorry for the last post. What was supposed to be a B followed by a closing parenthesis got converted into the emoticon.

Categories