multi column filter using OR

dsatria

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.

bostjanl

dsatria wrote:

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.

Bostjan

arootman

Hi

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.

dsatria

@bostjanl: Yes..I think the only way is using code

@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 ?

Marije_Brummel

There is no default way to do it.

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.

Miklos_Hollender

With security filters it won't work. With C/AL, f.e. with a report what I usually do is to write code in OnPreSection on which records to show. Surprisingly it's not so slow. I mean such a report scans all records, but only once, while a report that has filters that are not connected with keys, seems to scan the table multiple times. (It's an ISAM engine, so the design slogan might have been "but you never want to do 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.

Miklos_Hollender

Also, for security filtering, if you have f.e. six conditions and if it's false, false, true, true, false, true then Department A can modify a given Customer record's credit limit, you don't need to create six boolean fields, you can also do it this way: http://www.mibuso.com/forum/viewtopic.php?p=59780#59780

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.

dsatria

Miklos,
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:
Userid: DAVID
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.

FaddyDev

Use this.

SETFILTER("Global Dimension Code 1",'=%1|<>%2','XXX','XXX');
SETFILTER("Salesperson/Purchaser Code",'=%1|<>%2','999','999');

Plus any other filter in case both values are not found.

vaprog

Hi @FaddyDev

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.