In Navision, the Item table has a field called "Location Filter". If you put in a single location code or a piped set of location codes (i.e. WI-OEM|NC-OEM), you can get sum index fields (like "Quantity on Hand", "Qty. on Purch Order, etc.) to calculate based on your Location Filter. You can't, however, filter using wildcards (i.e. *-OEM). Is there some way you can create that piped filter by somehow applying the wildcard filter to the location table in your options and outputting that result to a NL function to get inventory values for those specific location codes?
What I'm trying to do is create a report that gives only the items in stock that have inventory quantities for specific divisions, which have multiple location codes. For example: we have 3 divisions that have 5 locations each. I want the report to be able to give me items with quantites in all of the OEM locations, or all of the WI locations. I currently offer a report that will give the proper results for all fields, but I can't filter the inventory quantities because I'm summing them up from the Stockkeeping Units right now, and I can't think of a way to filter out the zero inventory items.
My report is attached
right now I'm just returning all items (within filter range) and was trying to filter by non-zero Inventory, but without the Location Filter I'll be checking for non-zero quantities for all locations, rather than the filter locations.
Any Ideas?
Thanks!!!
Date
Votes
1 comment
-
Jet Reports Historic Posts When in jet, using a "rows" function:
ex:
D6 —-> =NL("Rows";"Country/Region";"Code";"Code";"B*")
you get a small table with your codes you need to filter.
On that table you use a NP join:
D8 —-> =NP("Join";$D6:$D7;"|")
This will result in a string that looks like a NAV filter: "BE|BG|BN|BR|"
To remove the last "|" you can use the excel string funtions:
=LEFT(D8;LEN(D8)-1)
I hope this answers your question
Please sign in to leave a comment.