In report with data items Purchase Line and BOM Component, I filter BOM Component lines on ‘Parent Item No’: SETFILTER(“Parent Item No.” , “Purchase Line”.“No.”); When running the report on a Purchase line with an item ‘36403016ATEX(T4)’ I receive an error ‘The filter is invalid’. How to avoid this?
Did you try :
SETFILTER("Parent Item No." ,'%1', "Purchase Line"."No."); or in your case I would even use the following code:
SETRANGE("Parent Item No." , "Purchase Line"."No.");
Hi Thomas, The second one does the job, and is also the easiest! Thanks!
Not just easier, but also faster in execution.
To set a RANGE in Navision - and a single value is also a range - takes less time for the database engine to execute than to analyse a filter which should be set to the field as it can be as complicate as : (BLA??&<>BLAXX)|(BLB??&<>BLBXX)
Hi Thomas, I am not sure if this is new information, if so can you let me know where you found this out? In earlier versions of Navision, the SETRANGE function had alwasy been implemented by internally mapping the SETRANGE to an equivilent SETFILTER function, thus althought the difference was marginal (and probably immesurable), a SETFILTER was faster than SETRANGE.
I’m not sure anymore where I got this information from (but it is already 6 years ago). I tested it myself in a database and it seemed to really perform better. Even as you say: the difference was not too big and will only be noticable when running very long batch jobs. On a SQL server I could imagine that it does not make a difference anymore, but this answer can be given by a Navision programmer from Denmark.
I was told by one of the origianl C/SIDE developers that they had coded SETRANGE as simply converting to an equivilent SETFILER in the compiler, but that was many years ago, I was wondering if it was changed in newer versions?
I think we had this discussion not long ago. This hasn’t changed in c/side since the begining and is also the same for native and SQL versions. It is as David said. After the AL language has done some mappings, SETFILTER internally uses a database helper function (lets says SetFilter2) directly. SETRANGE internally uses an additional helper function (lets say SetRange2) which then uses the SetFilter2 used by SETFILTER. Therefore technically SETFILTER is faster than SETRANGE for the same expression (i.e. when the FILTER expression matches that possible in a SETRANGE, not when the expression is more complex, like that mentioned earlier). But it will not be measurable in any way because of the time taken for todays processors to stack parameters and invoke a c++ function call (the c++ compiler may even have removed the function call altogether).
So appearantly it was just faster during my tests because I first measured the SETFILTER and AFTER that I measured the SETRANGE ? I restarted the client and the server but maybe the Operating system still had some of the database in cache. [V] But thx, Robert, good have a clarification for that now. I do not remember anymore whom to blame for that. But if I remember …
Ah I think you were not testing the speed of the function call, but in fact the server performance in returning records in a filtered range. In which case yes cache will make a huge difference.