Hello! Just a question about setting a filter. Let’s say I have a tabel called X. One of the fields in this table is called A, an other one is called B. What I want to do is to get all the records in the table where A has a specific value OR B has a specific value. How do I do that? With SETFILTER or SETRANGE I can only filter A AND B. With regards, Anolis Sittard Netherlands Edited by - anolis on 2001 Jul 18 12:02:36
See MARK and MARKEDONLY function. This is the only way to do OR operation between fields. ------------------------ EX: X.SETFILTER(A,‘10’); IF X.FIND(’-’) THEN REPEAT X.MARK(TRUE); UNTIL X.NEXT=0 X.SETRANGE(A); X.SETFILTER(B,‘20’); IF X.FIND(’-’) THEN REPEAT X.MARK(TRUE); UNTIL X.NEXT=0; X.SETRANGE(B); X.MARKEDONLY(TRUE); --------------------- Bye
You could also create a temporary table record e.g. X.SETFILTER(A,‘10’); IF X.FIND(’-’) THEN REPEAT Xtmp:=X; if xtmp.insert then; UNTIL X.NEXT=0 X.SETRANGE(A); X.SETFILTER(B,‘20’); IF X.FIND(’-’) THEN REPEAT xtmp:=x; if xtmp.insert then; UNTIL X.NEXT=0; X.SETRANGE(B); of course depending on the tablesize, what you need to do with the filtered data etc. Regards
Please note that MARKing records is not an adviseable approach if you’re planning to process a large number of records. As I’ve found out by experience that MARKing in Navision is rather slow. Often filling a Temporary table makes more sense in terms of performance … tarek_demiati@ureach.com
You’re right, Marking records is much more slower than filling temporary Table. But if you have to modify some fields, the difference (test performed on a table with 100000 records) between MArking and TemporaryTable-Getting Records is only 3 seconds! This is the code: recArt.SETFILTER(myField1,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArt.MARK(TRUE); UNTIL recArt.NEXT=0; recArt.SETRANGE(myField1); recArt.SETFILTER(myField2,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArt.MARK(TRUE); UNTIL recArt.NEXT=0; recArt.SETRANGE(myField2); recArt.MARKEDONLY(TRUE); IF recArt.FIND(’-’) THEN REPEAT recArt.myDate:=TODAY; recArt.MODIFY; UNTIL recArt.NEXT=0; t1:=TIME-000000T; t1:=t1-t; MESSAGE(FORMAT(t1)); // this take 2’.11’’ t:=TIME-000000T; recArt.SETFILTER(myField1,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArtTmp.INIT; recArtTmp:=recArt; recArtTmp.INSERT; UNTIL recArt.NEXT=0; recArt.SETRANGE(myField1); recArt.SETFILTER(myField2,‘Yes’); IF recArt.FIND(’-’) THEN recArtTmp.INIT; recArtTmp:=recArt; recArtTmp.INSERT; REPEAT UNTIL recArt.NEXT=0; recArt.SETRANGE(myField2); IF recArtTmp.FIND(’-’) THEN REPEAT recArt.GET(recArtTmp.“Nr.”); recArt.myDate:=TODAY; recArt.MODIFY; UNTIL recArtTmp.NEXT=0; t1:=TIME-000000T; t1:=t1-t; MESSAGE(FORMAT(t1)); // this take 2’.08’’ Bye
I’m sorry, there is a bug in my code: this is right code… MArking is faster than TemporaryTable-Getting Records!!! 2’.11’’ VS 2’.43’’ This is the code: recArt.SETFILTER(myField1,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArt.MARK(TRUE); UNTIL recArt.NEXT=0; recArt.SETRANGE(myField1); recArt.SETFILTER(myField2,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArt.MARK(TRUE); UNTIL recArt.NEXT=0; recArt.SETRANGE(myField2); recArt.MARKEDONLY(TRUE); IF recArt.FIND(’-’) THEN REPEAT recArt.myDate:=TODAY; recArt.MODIFY; UNTIL recArt.NEXT=0; t1:=TIME-000000T; t1:=t1-t; MESSAGE(FORMAT(t1)); // this take 2’.11’’ t:=TIME-000000T; recArt.SETFILTER(myField1,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArtTmp.INIT; recArtTmp:=recArt; recArtTmp.INSERT; UNTIL recArt.NEXT=0; recArt.SETRANGE(myField1); recArt.SETFILTER(myField2,‘Yes’); IF recArt.FIND(’-’) THEN REPEAT recArtTmp.INIT; recArtTmp:=recArt; recArtTmp.INSERT; UNTIL recArt.NEXT=0; recArt.SETRANGE(myField2); IF recArtTmp.FIND(’-’) THEN REPEAT recArt.GET(recArtTmp.“Nr.”); recArt.myDate:=TODAY; recArt.MODIFY; UNTIL recArtTmp.NEXT=0; t1:=TIME-000000T; t1:=t1-t; MESSAGE(FORMAT(t1)); // this take 2’.08’’ Bye
Hi, I’m “only” a N3.56 developer, so I don’t know much about Financials. But, what about selecting the primary key? Filtering on marked records is much faster on the primary key. So is it in N3.56 … Is it the same in Financials? Greetings, Marc
I believe that’s pretty much true for almost every major database engine around. A record will always be retrieve quicker if you know the primary key rather than retrieving it using a secondary key.
quote:
Originally posted by mru: Hi, I’m “only” a N3.56 developer, so I don’t know much about Financials. But, what about selecting the primary key? Filtering on marked records is much faster on the primary key. So is it in N3.56 … Is it the same in Financials? Greetings, Marc
Would not a secondary key would be a lot faster? X.SETCURRENTKEY(A); X.SETRANGE(A,‘10’); IF X.FIND(’-’) THEN REPEAT X.MARK(TRUE); UNTIL X.NEXT=0 X.SETRANGE(A); X.SETCURRENTKEY(B); X.SETRANGE(B,‘20’); IF X.FIND(’-’) THEN REPEAT X.MARK(TRUE); UNTIL X.NEXT=0 X.SETRANGE(B); X.MARKEDONLY(TRUE); Shame there is not a MARKALL! David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk Edited by - David Cox on 2001 Jul 19 20:44:03
quote:
What I want to do is to get all the records in the table where A has a specific value OR B has a specific value.
If the table is too large for the MARKing, you might simply insert a boolean or option field whos value depends on A and B. You might set this variable ONINSERT and ONMODIFY and use as filter in your form or report. ------- With best regards from Switzerland Marcus Fabian
Thank you very much for all the replies! It all are very usefull tips, but I found another way: SETCURRENTKEY(field) SETRANGE(field,A) IF FIND(’-’) THEN REPEAT IF B=value THEN what needs to be done. UNTIL NEXT=0; Ofcours this cannot always be used, but it worked in my situation. Thanks again! With regards, Anolis Sittard Netherlands
This is not an OR operation between fields! It’s an AND operation… Bye
You’re right. Sorry, it is that Monday morning problem… This is the code: SETCURRENTKEY(field) SETRANGE(field,A); REPEAT do whatever needs to be done UNTIL NEXT=0; SETRANGE(field,B); REPEAT do whatever needs to be done UNTIL NEXT=0; With regards, Anolis Sittard Netherlands
Martin… this is a Black Monday! You have to remove filters from first field!
It’s Monday for sure, but The filters are on the same field, so the second filter will replace the first. The Navision helpfile (SETRANGE): " If you call this function with a field that already has a filter, the system removes that filter before it sets the new one." I guess there is a mondaymorning for everyone… Anolis Sittard Netherlands
Hi Martin, but thats not the situation you’ve described in your question. There you talk about two different fields called A and B and not of values A,B??? In the situation you describe with your code you can use SETFILTER(field, ‘%1|%2’,‘A’, ‘B’) REPEAT … UNTIL NEXT = 0; This should do the work. If you need to filter on two different fields, Marco is right. You need to remove the filter from the first one before filtering on the second! Greetings, Frank
You’re right. I think I’d better go home. Indeed, I did remove the filter. I tried the paste the code here and now I have a general protection fold … and I didn’t save that huge report I was working on… It’s a hard life. Anolis Sittard Netherlands