Hi all, I need to filter the FORM 31 (Item List) so that the showed items are only those referenced in another table (n-m table). Just to be clear; I have the followin custom table: Table name: Season 1 | Season Code | Code | 10 2 | Item No. | Code | 20 The Season table could contains for example: Season Item Code No. 10 | 1000 10 | 1001 10 | 1002 20 | 1000 20 | 1003 The Item List form must show only the Items linked to the Season Code 10 (the season code must be set externally to the form). I hope I’ve been clear. Thanks in advance. a.cavalieri
It’s not possible to filter the FORM 31. It isn’t possible to define a field in Item table that contains the Season Code, because you have more season codes for one item. Starting-point is the table Season. You could make a form with SourceTable Season. In the OnAfterGetRecord you can put a Item.GET(Season.“Item No.”);. Add columns to the form and refer to the fields in the Item table.
You need some kind of join to do that in a “nice” way - but Navision does not support joins. I would create a new form: *read all item-no’s in Season-table and insert those into a temporary table *use this temporary table as source in the form (take a look at form 344 Navigate og search this site for how that is done)
Ok. Thats was I supposed to. Thanks for the tips.
Temporary Tables are always an option (possibly the best one), but I think you can do this with a couple of fields in the Item Table: 1 - Season Filter - Flowfilter 2 - Season Exists (Boolean) - Flowfield Exists in Season Table where Season Code = Season Filter and Item No. = No. You can then filter the form for Season Exists = True (You would have to set the FlowFilter when you call the form). Note that this method can be slow if you have a large amount of data or your keys are not optimized.
I use this method that Chris sugggests quite often, and it works quite well. Definitely its better than having to populate a temporary table, and if you balance your keys and filters right, it can be faster. The important thing is to try to pre filter the table with a data field, before filterring on the flow field, in that way you at least can have a partial key selected. (Obviously you can’t use the folw filter as a part of the key).