Table Relation with multiple filters

Business Central/NAV Developers Forum
1

I have the following table relation on my Table which allows the user to select and item # from the item table. The list of items that you can select from is filtered to only display items there the “Item Category Code” = ‘ROLL STOCK’
I would like to add an ‘OR’ clause to it so that I can add another Item Category Code = ‘Sheet Stock’

Thanks
Yann

field(50084; "Paper Item No."; Code[20]) { 
    TableRelation = Item."No." where ("Item Category Code" = filter ('ROLL STOCK'));
}
1 Like
2

Just extend the filter:

where (“Item Category Code” = filter (‘ROLL STOCK|SHEET STOCK’))

Although, I would suggest to avoid hardcoding filter values in table relations - these values tend to change with time. Much better if the filtering list is stored as a setup in a table and applied in the OnLookup trigger. Of course, setting a table relation is easier that handling it in code, but its simplicity should not lure developers into bad coding practices.

3

Thanks again Alexander,

I swear I tried that originally. Do you know of any Business Central tables that are used for the purposes of storing filter data like that? Or should I just create my own.
Business Central’s “pay per table model” sure forces me to think different when it comes to data storage.

Thanks
Yann

4

There is no specific table to store filters, this is rather something specific for the functionality you are developing.If it’s a filter on item categories, probably you could extend the Inventory Setup table. I would maybe choose a different approach - add a Boolean field to the Item Category table and show only those categories where this field is True.