Filter Field1 OR Field2

Hi,

Is it possible to make a filter that does:
Filter((Field1 = ‘value’) OR (Field2 = ‘value’))

Like a WHERE clause in SQL
( select * from tabel where col1 = value or col2 = value )

Short answer: No.

To get the same result, you have to use the MARK feature in NAV:

Record.SETRANGE(Field1, Value1);
IF Record.FIND(’-’) THEN
REPEAT
Record.MARK(TRUE);
UNTIL Record.NEXT = 0;
Record.RESET;
Record.SETRANGE(Field2, Value1);
IF Record.FIND(’-’) THEN
REPEAT
Record.MARK(TRUE);
UNTIL Record.NEXT = 0;
Record.RESET;
Record.MARKEDONLY(TRUE);

Hmm, can’t really get this working. Seems like the RESET function resets not only the filter but also removes the marks.

Is there a function for removing all filters on a table in C/AL, like when i press Ctrl+Shift+F7 in the GUI.

Instead of Setrange and Reset, just use setfilter and Mark…but be carefull displaying Marked Only on big recordsets can really slow performance.

I use the SETFILTER function and no more the reset but when I set a new filter it some how removed the marks.

Code is like this:

Contact.SETFILTER(Contact.Dist, ‘JA’);
IF Contact.FIND(’-’) THEN BEGIN
REPEAT
Contact.MARK(TRUE);
UNTIL Contact.NEXT = 0;
END;
Contact.SETFILTER(Contact.“Int. Sal. Code”, ‘WEE’);
IF Contact.FIND(’-’) THEN BEGIN
REPEAT
Contact.MARK(TRUE);
UNTIL Contact.NEXT = 0;
END;
Contact.MARKEDONLY(TRUE);

When running this only records from the second filter criteria (Int Sal Code = WEE) is marked. When running it with the second part removed I get records from the first criteria aswell. My gues is that the second filter setting removes the marks creadet in the first.

I would personally not use MARK in code. Instead I’d use a temporary record variable and insert records into it while looping.

Agree with Denstar on this one. Marks are a pain in the proverbial.

Temp Tables are the way ahead. You can run them on Forms and reports.

I’ve used Mark only once in the last 3 years, and that was as a last resort.

Saying that, marks are useful in the Object designer [:)]

For an example of Temporary Tables in Forms see Form 542

/TH

I also would never recommend the Mark function in this particular case, but…

One thing about Marks that developers forget (or maybe didn’t read in the manuals). The MARK is created in a temp file. The MARK is linked to the primary key, never to any secondary keys. So for any chance of performance, either you must sort the form by primary key, OR the secondary key you are using must be filtered on those keys, and that filter must filter to a very very small number of records.

In the case of a very large record, keep in mind that for either Temp record or Marks, you are going to have to open every record just to open th eform, so both will be slow.

Reitterating what nicbartie said Marks do not belong on large record sets.

Joeri, is this to do something specific, or just a general inquiry?

because if you have specific issue to resolve, there is often a different solution than doing the OR.

I agree on not using the MARK-function. Using an OR-statement in NAV could be done like this:

SETCURRENTKEY();
SETRANGE(Field,<Condition_1>);

IF FIND(’-’) THEN
REPEAT
IF <Condition_2> THEN
fnProcess;
UNTIL NEXT = 0;

Use the best key, set a range on the [one] field used in the key. Then read all records, but filter out those not to use on the fly.

found something better.

look at : http://navisionfreak.blogspot.com/2006/08/filtering-fields-on-form-using-operator.html

Greetings Remco