It looks like you're new here. Sign in or register to get started.
How to perform multiple field/column filter using OR operator?
For example, we want to filter Value Entry table (through Security Filter or C/AL code) to ["Global Dimension Code 1"='XXX' OR "Salesperson/Purchaser Code"='999']
Thank you in advance.
All filters from different fields are combined with AND.
So you have to use code to skip unwanted records.
Unfortunately there is no easy way to do multi coulomb or filtering in Navision. The easiest way is probably the following:
1. Set filter on the first coulomb.
2. Mark all the records in the filter.
3. Clear the First filter
4. Set the second filter.
5. Mark all the records in the filter.
6. Clear the second filter.
7. Filter Marked Records only.
Performance using this method is really bad. Using the method above I suggest rather that marking the records insert the nominated records into a Navision temp table. You will still take a performance hit while populating the temp table but you save the marked only filter that most likely will consume the most time. Because the Temp Table is in memory, navigating should be fast.
Hope this helps.
@arootman: Thank u for the ideas..though they're not applicable to our case because of the performance hit
Anyway..will this (multiple column filter with OR) be possible with Dynamics NAV ?
But if you want to let it perform, you can first filter both values, using the right keys, put the data in a temp table and run a form on that.
Also, if nothing else helps, you can do it AT POSTING. Like, for example, a sales Value Entry is considered a special sale if it is done by salesperson X or belongs to Global Dimension Project = something. Then you create an option or boolean field called Special Sale and write code in CU22 to populate it according to these conditions. And then just make a key for it a filter for it very simply. You can also use it for security filters. For example, if a user has can modify master data (like credit limit) for customers below a total sale of X or those who are abroad, then either write the code in the OnModify trigger to check it or create a field called Customer Rating or something like that and write a batch job to update this field every night, and just set it in the security filters.
But probably the easiest was to security is to define an owning user and an owning department field to each record you want sophisticated security for.
I'm especially interested on how you put code in OnPreSection (perhaps you mean OnPreDataItem?)
Have you find a way to make filters used are more centralized (in a table or codeunit) instead of hardcoding them on every report that uses Value Entry?
I was thinking of a table (or codeunit) which contains userid/table/filter pair like this:
Table: Value Entry
Filter: "Global Dimension 1 Code"='01 OR "Salespers./Purch. Code"='1010-001'
I already made such table, can retrieve the filter in a report but UNABLE to apply it to "Value Entry" DataItem.
SETFILTER("Global Dimension Code 1",'=%1|<>%2','XXX','XXX');
Plus any other filter in case both values are not found.
Your reply is to a thread that was inactive for more than 16 years! Don't do that.
Your reply does not make any sense. If you filter for a value beeing equal or inequal to a certain value, obviously all values match, because, at least in NAV/BC, there are only these two cases.
In recent versions of NAV/BC, you can use FILTERGROUP(-1) in order to achive what the tread starter asked for.