Setting Filters

Business Central/NAV Developers Forum
1

If these are the records in a table(A and B are fields): A B 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 Is there a way to set a filter for A = 1 OR B = 2 and select the following records: A B 1 1 1 2 1 3 2 2 3 2 Thanks. Pari Somasundaram

2

Unfortunately the only way Navision will combine filters on different fields in a table is with a logical AND. You could run through the records in the table and mark those satisfying your condition and then use MARKEDONLY. If the “1” and “2” in your example are constants and not run-time values then you could create a third field to contain the result of the OR condition. This field can be set by the OnValidate triggers for fields A and B.

3

Hi, If you can relate the two tables with a table relation on a new field in table B which will store the value of the corresponding record in Table A. Like in the Sales order header and Sales order Lines which are related by No. (sales order header) and Sales Order No. (sales order line) then you can setfilters on A and B using the related field. If you dont want to use this then you have to use the Marking of records based on condition. Best regards, Prashanth pbg@navilogic.com

4

Just forget this one :-). I think i had an aneurysm!! Edited by - SNielsen on 2001 Oct 22 15:08:07

5

quote:

Originally posted by SNielsen: Then set the filter as this: ‘A=1|B=2’. /Soren

:slight_smile: You can use temporary tables too. If you have a lot of rec’s in table use temporary table, else use mark.

6

quote:

Originally posted by SNielsen: I might have read Pari’s posting wrongly, but i think there is only one (1) table being mentioned:

quote:

If these are the records in a table(A and B are fields): A B 1 1 1 2 1 3

Then set the filter as this: ‘A=1|B=2’. where ‘|’ is the logical OR, and ‘&’ is the logical AND. Hope this helps you Pari. /Soren

‘A=1|B=2’ I don’t understand how this will work because SETFILTER is used for one field at a time only. I guess marking the record will work. I have not used temporary tables yet. Could someone briefly explain how it can be used in this case? Thankyou. Pari Somasundaram

7

I am trying to use MARK to solve this problem. The way I’ve coded is: SETRANGE(A,1); IF FIND(’-’) THEN REPEAT MARK(TRUE); UNTIL NEXT = 0; SETRANGE(A); // to remove filters on field A SETRANGE(B,2); IF FIND(’-’) THEN REPEAT MARK(TRUE); UNTIL NEXT = 0; MARKEDONLY := TRUE; Please advise if this is correct. Pari Somasundaram.

8

Using temp tables:


VAR: TempTable
CODE:
TempTable.DELETEALL();

SETRANGE(A,1);
IF FIND('-') THEN 
REPEAT
  TempTable.COPY(Rec);
  TempTable.INSERT();
UNTIL NEXT = 0;
SETRANGE(A);

SETRANGE(B,2);
IF FIND('-') THEN
REPEAT
  TempTable.COPY(Rec);
  IF TempTable.INSERT() THEN; //rec can exist in table
UNTIL NEXT = 0;

Now in TempTable are all needed recs.

9

There are a number of solutions to this problem, 3 are mentioned above, i.e. a third boolean field, a temporary table, or using marks. Another option that I use sometimes, is to put code in the OnNextRecord trigger:


**OnNextRecord(Steps : Integer) : Integer** 
REPEAT
  ResultSteps := NEXT(Steps);
UNTIL 
  (ResultSteps = 0) OR 
  (A = 1) OR 
  (B = 2);
EXIT(ResultSteps);

It is a bit messy, but the results can be better from the user point of view. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________