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

Junav

SELECT Field1 FROM Table1 WHERE Field1 <> 'a' OR ( Field1 = 'a' AND Field2 = 1 )

allenyyzhou

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

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

Hi Miklos

Yes, I agree, temporary table is better.

Junav

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

Logical disjunction (OR) is distributive over logical conjunction (AND) – so A OR (B AND C) is equivalent to (A OR 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

Sorry for the last post. What was supposed to be a B followed by a closing parenthesis got converted into the emoticon.