I have the following and dunno how to resolve it.
I want to filter record by Column A & Column B using OR condition. The output SQL should look like this WHERE ColumnA = 'xxx' OR ColumnB = 'xxx'
But the SETRANGE can only generate WHERE ColumnA = 'xxx' AND ColumnB = 'xxx' (not OR condition); SETFILTER is for the same column.
What is the best way to handle this?
Would greatly appreciate if anyone can help?
0
Comments
TBMyTable and TBMyTrueTable are 2 record variables of the same record.
I don't think this code will generate an "OR" statement, but the result will be the one you want.
Obviously this method is slow. Hope someone have something better
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
You could also work with Temp tables.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
we want Column A = 'xxx' OR Columng B = 'xxx' and we can only use ANDs.
so that should be equivalent to NOT ( Column A <> 'xxx' AND Column B <> 'xxx' )
So we can get this:
table.setfilter( A, '<>xxx') ;
table.setfilter( B, '<>xxx') ;
table.findset ;
and we have all the records in the table that are NOT in our filter criteria. Now we have to get that set's complement. There are two ways I see to do that:
1. We can MARK all of these records. Clear the filters. And then toggle the marks on all of them. The remaining marked records are in our intersection of two sets.
2. Get all of table withotu filters into a temporary record. Loop through the 'not' set and delete those records from teh temporary record.
Nice thing about (1) is that you are never looping through records, so for large data sets it should be really fast. (2) avoids using marks, and for most filtering criteria should be faster than Belias method.
Still, if you're looping your complete table ... I think it's a better idea to avoid the extra (first) database call with opposite filters, loop the complete set anyway and put it directly into a temp table.
Afterwards, show the temp table.
Someone should benchmark it.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
Perhaps the simplest thing of all is to create a new boolean field that is true if either field has the given value.
Marking records, then "markedonly" gives you a database call that is unpredictable on SQL Server.
Marking with a boolean will write every update to the database... .
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
Also perhaps both solutions could be combined. -- marking a temporary record instead of the database.
Of course the temp table would need to be populated in the first place -- and but we have do that in either scenario.
But i still wondering that why Navision Team do not buid in this functionality as it seems quite common.
http://www.mibuso.com/forum/viewtopic.php?t=22245
I've asked for something something like this.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n