Hi,
I am trying to use Jet to get the quantity on hand at the end of 2007. Can someone guide me with code on how to do this?
Thanks,
Susan
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Susan,
You don't say for what database you are using Jet Reports, but I'm going to assume Navision. There are a couple of ways to get the Quantity on Hand for an Item, but if you want to get this as of a specific date, you cannot use the "Quantity on Hand" field on the Item table. Instead, you have to use the "Net Change" field on the Item table. Using the "Net Change", you can write your formulas as follows;
In Cell D3 12/31/2007
In Cell D6 =NL("Rows","Item")
In Cell E6 =NF($D6,"Net Change","Date Filter",NP("DateFilter",,D3)) -
Jet Reports Historic Posts Hello Sherman,
your solution confuses me because I could not believe it worked but I could also not believe that you would suggest a non-working-solution. I tried the solution on our database and it worked of course. The problem is that I do not understand how it can work. As far as my understanding of the item table goes it contains one record per item and that for every item you can retrieve only one fixed value with the NF function. The report shows that different datefilters can return different Net change values for the same item. How does it do that?
Best regards,
Arnoud -
Jet Reports Historic Posts The field "Net Change" in the Item Table is what Navision calls a "flowfield". This is a calculated field based on values from a detailed table.
Standard Navision gives us the possibility to put filters on these flowfields (the so called flowfilters) which can be used in Jet Reports as well because these flowfilters are actual fields in the table. What you enter in the flowfilter field is used to filter the values in the flowfields where the filter is applicable.
One of these flowfilters is the "Date Filter" which is available for most flowfields in Navision, but not for i.e. Inventory. Net Change on the other hand does have the Date Filter implemented.
In Jet Reports if you retrieve a flowfield with the NF function, you can use these flowfilters to filter the flowfield.
Hope you understand all the flowing and the filtering :-)
Greetz,
Kenny -
Jet Reports Historic Posts This awesome. I love this site. Thank you for the quick reply, I have been struggling for weeks. I'll be back here often.
Susan -
Jet Reports Historic Posts One more question. How do I filter by location?
Yes we are on Navision.
Thanks,
Susan -
Jet Reports Historic Posts Hey Susan,
Here is a list of all the fields that you can filter the Net Change field by in a standard Navision 4.0 database;
Global Dimension 1 Filter
Global Dimension 2 Filter
Location Filter
Drop Shipment Filter
Date Filter
Variant Filter
Lot No. Filter
Serial No. Filter
So, to use the previous exampleIn Cell D3 12/31/2007
In Cell D6 =NL("Rows","Item")
In Cell E6 =NF($D6,"Net Change","Date Filter",NP("DateFilter",,D3))
If we change our cell E6 to
=NF($D6,"Net Change","Date Filter",NP("DateFilter",,$D$3),"Location Filter",$D$4)
and cell D4 contains the location we want, we now get a balance for a specific date for a location. -
Jet Reports Historic Posts Hi
I would like to calculate cost of the quantity on hand on a given date. (Navision)
Quantity I can get using the netchange from Item table.
In item ledger entry I have "Remaining Quantity" but it is only for today.
Quantity on hand can be from several purchases with different costs,
so I would like to calculate sum of cost at a given date (First In First Out).
For instance if I have quantity on hand 1000 on Oct. 31 - I want to go back
to last purchases before Oct. 31 for instance:
Purchase 1, Oct. 27: 700 pieces * cost 1
Purchase 2, Oct. 25: 500 pieces * cost 1.2
Total cost (700*1+(1000-700)*1.2) = 1060.
Any way to solve this using Jet ?
Any help will be highly appreciated
Please sign in to leave a comment.