Date Filter & Location Filter on Item Table

I am trying to make inventory report using Item table. Is there any ways to use the “Date Filter” field to filter the data for certain period of date? For example as of 12/31/2005 or 01/31/2006. The report is ready and the only problem that I have is I can’t filter the date as the report displaying the same figures no matter what date filter that I put in; I think Navision captures the data as of today’s date. Please help. BIG Thanks, ZEN [:)]

Hi The filter will affect different fields, If I am right, The “inventory” field will always Ignore the date filter. I am not sure off hand if there is an “Inventory at Date” field ? So you can use the “Net Change”, this will show you the sum value in the date range, I think that field name is correct, as I am at home. There are similar fields within the system for Item and G/L, The G/L ones are: If you enter 12/31/2005…01/31/2006 you will have 3 values Balance = Not affected by Date Filter Balance at date = up to 01/31/2006 Net Change = 12/31/2005 to 01/31/2006 You would enter …01/31/2006 “up to and including”, for your report

— Sorry David you are correct, I am deleting this reply. I mixed up some developer threads and this one. I think we really need to make it mandatory for memebrs to state weather they are NSCs or Endusers etc. —

Steady this is an End User Question, Create New fields ?? If I am Correct the field already exists ? Correct me if I am wrong! [:D] If not a simple bit of code on pre report to getrangemax Like Item.SETRANGE(Item.“date Filter”,0D,GETRANGEMAX(Item.“Date Filter”); This will Give you Zero Date to the Maximum Date, so you can just put in 12/31/2006 for up to and including 12/31/2006 (American Dates), but if you do not put a date in it will error! You can test this with IF GETFILTER(Item.“Date Filter”) = ‘’ THEN ERROR(‘You must Enter a Date Filter’); Then use the Item.“Net Change” Field on your report, you can change the label to “Inventory”! No Extra fields, no upgrade problems, just simple code! [Sigh…]

oops.[B)]

David, This is excellent; the problem is solved. Big thanks! If I want to make another inventory report (from Item table) but summarized by Location Filter, is it possible? The layout is very simple; only consist of location code, qty on hand and inventory value. Something like this: =================================== Location, Qty on hand, Inv. Value =================================== NY, 78,789, 1,200,000.00 LA, 56,897, 985,789.89 etc… Please helps. ZEN

quote:

David, This is excellent; the problem is solved. Big thanks! If I want to make another inventory report (from Item table) but summarized by Location Filter, is it possible? The layout is very simple; only consist of location code, qty on hand and inventory value. Something like this: =================================== Location, Qty on hand, Inv. Value =================================== NY, 78,789, 1,200,000.00 LA, 56,897, 985,789.89 etc… Please helps. ZEN
Originally posted by Zen Bodhi - 2006 Apr 19 : 20:07:22

You can use the wizard here and use the “Item Ledger Entry”, this will give you what you want and more filtering options, I will take you through it “step by step”. Note: There are several Financial Fields in the Item ledger, “Expected Cost” and “Actual Cost” being two of them, and then there is the Sales Cost Values, if the goods have only been received, then there will be no Actual Cost, only Expected Costs, so you may need to do some more work, to make sure that if you have purchased 10 and sold 5 the stock value is just 5 units, and have the two values as Expected Cost is a liability (Expectation), you have not paid for these goods yet. First do you homework, look at the fields on the Item ledger, and list the Financial value fields, that match the Remaining Quantity, when your happy proceed. New Report using the wizard Choose “Item Ledger Entry” Select your Fields “Location code”, “Item No.”, “Remaining Quantity” and the Financial Fields. Select a Key that starts with - “Location Code”, “Item No.”, ect: It will ask you if you want grouping, “yes”, If you only want one total line for each “location” Just pick the first grouping level “Location code”, but I will show you how to enhance this, to two levels. If want a two level report select the first two elements of the key “Location code”, “Item No.”. It will now ask you for the sum total fields Choose the “Remaining Quantity” and Financial Fields. Create the report, open it View Sections First we need to Hide all the detail lines Go to the Line Detail Section View CAL Code, Add this code CurrReport.SHOWOUTPUT(FALSE); If we have only one group level that is it. We want to choose if we show the values by Item. Create One Cal Globals. ShowItem - Type - Boolean So now go to the Group Header Section for Item View CAL Code; You will see some code like CurrReport.ShowOutput(TotalsCausedBy("Item No.)); After This code add the code CurrReport.SHOWOUTPUT(ShowItem); Now do the same for the Group Footer Item, here you will see code already CurrReport.CREATETOTALS(“Item No.”); and CurReport.ShowOutput(TotalsCausedBy("Item No.)); After This code add the code CurrReport.SHOWOUTPUT(ShowItem); View Request Form Drop On a Check box, source expression = ShowItem drop on a Label Caption “Show Item Values” View code for the form, Add Code to Section OnInit - ShowItem=FALSE; Nearly There if we want a grand total we need to add a report footer Copy the Decimal fields from the Location Group footer Paste them into the Report footer add a label Grand Total or something Preview the report, adjust the section how you want them! Also look at the Value Entries table, you could do the same report from there if the key exists! [:D] Done [8D]

David, I am highly appreciating your response. I read very carefully the explanation. This is awesome! Thank you, ZEN

quote:

David, I am highly appreciating your response. I read very carefully the explanation. This is awesome! Thank you, ZEN
Originally posted by Zen Bodhi - 2006 Apr 20 : 17:33:19

Maybe we should put a few Examples in a Downloadable Document or presentation, as the Grouping and showing Output is not well documented, plenty of graphics so you can see whats going on! [:D] Anyone know of any freeware for recording a presentation? [:p]

David, Is it some kind of graphic chart in excel [?] It would be great if that’s possible. ZEN