filter on different fields ?

Business Central/NAV Developers Forum
1

its me and my big bad form again … :slight_smile: is their an easy way to make a filter like: show every record out of my big table with a special number in field 1 or this number in field 2 … the problem is that those with the number in field 1 haven’t this number in field 2 and reverse … so i need something like: SELECT * FROM bigbadtable WHERE field1=artnr OR field2=artnr … my only solution would be a manual walk through the table and if their is one it would mark a temporary field called ‘specialfilter’ and after finished it would filter on this … but this isn’t nice :slight_smile: and i don’t think very fast … *** quack ***

2

I miss the “OR” clause as well :slight_smile: Anyway…I would make a third field with “field1++field2” content and update it regularly, when doing Inserts in this table. Then setting a “artnr” filter you’ll get the required result. Dont forget to create an additional key on this field…BUT if the table is really big, this could be a costly one /in terms of db space/. Another consideration is the “artnr” content e.g. it has to be fixed length so this to work. You’ve already got the other solution… good luck, Niki

3

The way I usually approch this problem is to filter on the value in field 1, then MARK all of the records. Then I remove the filter and do the same thing on Field 2. Then set a filter on MARKED = TRUE. This will give you the OR you are looking for. Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117

4

i tried it with the mark-function and it really works fine but … i filter using SETRANGE and after the filtering i have to remove the filter so i use SETRANGE on the fields without a value … after marking all of themn i call the markedonly and works very slow … so are their some features that it filters the marked-records faster ? tmp := artnr; Rec.CLEARMARKS; SETCURRENTKEY(artnr,stammtraegernr); Rec.SETRANGE(artnr, tmp); IF Rec.FIND(’-’) THEN Rec.MARK(TRUE); Rec.SETRANGE(artnr); SETCURRENTKEY(stammtraeger,stammtraegernr,artnr); Rec.SETRANGE(stammtraeger, FALSE); Rec.SETRANGE(stammtraegernr, tmp); IF Rec.FIND(’-’) THEN REPEAT Rec.MARK(TRUE); UNTIL Rec.NEXT=0; Rec.SETRANGE(stammtraeger); Rec.SETRANGE(stammtraegernr); Rec.MARKEDONLY(TRUE); *** quack ***

5

the best replies are those u write yourself … :slight_smile: … okay i inserted an SETCURRENTKEY(id) (my first key in the table) before the last two SETRANGEs and surprise it now runs very good … :slight_smile: … strange but true … *** quack ***