multi column filter using OR

dsatria
dsatria Member Posts: 80
edited 2006-09-14 in NAV/Navision Classic Client
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.

Comments

  • bostjanl
    bostjanl Member Posts: 107
    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
    arootman Member Posts: 2
    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
    dsatria Member Posts: 80
    @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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    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
    Miklos_Hollender Member Posts: 1,598
    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
    dsatria Member Posts: 80
    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
    FaddyDev Member Posts: 2
    edited 2023-03-08
    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
    vaprog Member Posts: 1,161
    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.

Categories