Phys. Inventory Journal and Lot No.'s

I’m analysing Navision 3.7 for future use. Item tracking is very important for us therefore I’m checking the relation between the physical inventory journal and the lot no.'s. I can calculate the inventory but what I’m missing is an automatic calculation of the item tracking lines of the calculated inventory. No I have to insert all the item tracking lines manually. Am I something missing or is this the case? If so, what kind of solutions are there for this problem and are there other people who have to deal with this problem? Please let me know what your experiences are. Thanks in advance.

Hi, Why you have to insert Item Tracking for each line. You need to enter Item Tracking Line for only those lines which physical count is not equal to Quantity in system. For an example: Suppose System counted inventory 10 and inventory in your warehouse (i.e physical count) is 11. If you want to update system inventory up-to-date you have to post 1 quantity. If you don’t specify manually then How system will know what is Lot No. for 1 quantity, which is sort in your system?

Hi Tel Whilst the physical inventory journal populates the quantitiy it believes is in the system, it does not populate the serial or lot numbers. So no, you are not missing something, this is how it works as standard. I am sure a modification to this affect would not be too onerous. Afterall the serial and lot numbers are under the ellipses in item tracking to see. However your difficulties naturally arise in a physical count when you have less or more than expected, then you will need to verify each serial or lotted item, and define those that have come on or departed. This then makes the control of this that little more tricky.

Thanks for your reply Rajeshj. I understand what you’re saying and I am aware that you have to insert tracking lines when the inventory is not up to date. However I’ve expected that when you calculate the inventory it also specified the item tracking lines. For example my inventory for item X is 100. That inventory consists of Lot A (30), B (40), C (30). I’ve expected that I could see those three lines back in tracking lines and that I have to insert the difference on item tracking lines. For example Lot A shows columns with 30 and 29 (counted items), therefore the dfference of Lot A is one. In this case you can also create counting lists based on lot no.'s/item no. instead of an item no.

Hi, You can create a report ( the easiest way to grouping record) and can link it to Physical Inventory Journal to show the lot wise item availability of selected item in Journal. That can help you to reconcile inventory lotwise.

Hello SBWEAVER, Also thanks for your reply. You’re saying it corredtly…the physical inventory journal populates the quantitiy it believes is in the system. This quantities consists of lots which are also in the system. Therefore I’ve expected to system to come up with those entries automatically…unfortunately :frowning: The probleem is indeed when there are differences. You’ve expected therefore the columns Qty. (calculated) and the column Qty. (Phys. Inventory) in the item tracking lines. The differences you fill in on item level is the sum of the differences you fill in on item tracking level. When there’s a lot physically but not in the system anymore you can create a new lot no. in the item tracking lines.

Hi Tel They did it if you use bins - a new line in the journal for each bin, however the item tracking section is lacking. If you ask in the developer forum “How difficult would it be to populate the item tracking line with the current expected stock when running the calculate inventory process in the physical inventory journal”. You may get a respose from teh developers that will assist you further.

Hello Rajeshj and SBWEAVER, That’s indeed the way to start the counting cycle…the creation of the item tracking lines automatically is something we have to adjust in the physical inventory journal ourselves :frowning: Thanks again for your input (both Rajeshj and SBWEAVER). Experiences of other users regarding this problem is also appreciated.

Hi Tel (You can call me Steve [:D]) Our end users that use lotted or serilaised inventory use hand held scanners whilst counting and do so from the item tracking lines to verify the structure of the numbers. However we are not talking about a hundred thousand stock lines here, only a thousand or so, and with the periodic counting by bin taking place the full stock check is generally pretty accurate.

Hi Steve (you can call me Henry :)), Hand held scanners is really something we have to work with in the feature. What do you mean with ‘do so from the item tracking lines to verify the structure of the numbers’. In our case I think we also working with a thousand or so but we don’t work with bins.

Hi Henry Well one customer has a tablet PC that they walk around with, they have already generated a physical inventory journal, they then go to the line they are counting (in the bin) and select the item tracking lines. We have modified this section so the cursor starts in the serial number field and goes to the next serial number field after scanning, populating the quantity with 1. They then scan the serial numbers. Investigating any differences that occur. On the full stock take they have individual counters count quantity, differences are investigated but they simply select the given serial numbers from teh ellipses at this stage as the spot check counting should have captured any issues. Ultimately make the system fit your process!

Sounds like a nice solution you have there. With the comments earlier made in this topic I will try to fit it in in our proces. Thanks again!

Henry, It’s even worse than you think. The way item tracking works is that the sum of the tracking lines must equal the quantity on the physical inventory line; this quantity is not the calculated or counted quantity, it is the amount of the adjustment. For example, suppose you have lots A, B, and C with calculated quantities of 10, 20, and 30 and counted quantities of 11, 22, and 29 respectively. Then the total calculated quantity is 60 and the counted quantity is 62. The system will create a physical journal line for a positive adjustment of 2. Therefore the item tracking lines must sum to 2. There is, however, no provision for entering calculated and counted quantities on the item tracking lines, you must compute this difference manually and enter it. So you need to enter tracking lines for lots A, B, and C of 1, 2, and -1 respectively. Unfortunately, the system will not let you enter negative numbers in the tracking lines; so you are stuck. There is really no good solution to this without modifying the system. I have modified report 790 (Calculate Inventory) to create a separate physical journal line for each unique lot/serial number and to display the lot number and serial number in the physical journal. A count list can then be printed from the physical journal that shows lot and serial number and the user can enter the counted quantities for each lot/serial number and have the system calculate the appropriate adjustment for each lot/serial number. The final piece is to automatically create the tracking lines prior to posting the journal. I also modified the physical inventory ledger to contain lot and serial number.

Hello Jack, Thank you for your reply. I wasn’t aware of not entering negative quantities. This doesn’t sound very OK…there goes my idea :(. The solution you mention is also very compatible with our proces. Before you’ve implemented your idea did you consider to adjust the code so you could enter negative entries?

Henry, Yes I did consider allowing negative entries to the item tracking lines and in fact started down this path. It got messy very quickly and I still didn’t like the user interface.

I can imagine that the things you have to do (lots of code to be changed) can be a bit messy :(. And I also agree with the fact that the interface is much simpler for end users to use. Are you willing to share your adjustments with me because I’m very interested in your solution?

My work was done as part of an add-on product that we offer and as such I can not release the code. I would, however, be happy to talk in general terms about the approach.

I understand… In general then, are these the steps to be taken? 1) Add the column Lot No. to table 83 (we don’t use Serial No.) 2) Change some code in report 790 I haven’t checked report 790 and the things to be changed yet but there are some other things which I’m curious about how you solved them: 1) We also have items with no item tracking. Are there two seperate ways to retrieve the lines in your report (items with item tracking and items with no item tracking). 2) When you post the lines do you totalise it per item or do you totalise it per lot? Thanks for your time!


Add the column Lot No. to table 83 (we don’t use Serial No.)

Table 83 already has a field for lot number (field 6501). It’s not used for data input; it is used during the posting process when the item journal line is split into multiple lines corresponding to the tracking lines. You can add this field to the physical journal form and populate it in report 790.


Change some code in report 790

As report 790 runs it maintains a buffer (temporary table) for each item that accumulates inventory by location, variant, dimensions, etc. This needs to by modified to add lot number as another field to accumulate by. After all ledger entries for the item have been processed then each record in the buffer is processed to create item journal lines; the lot number needs to be written to the item journal lines at this point


We also have items with no item tracking. Are there two seperate ways to retrieve the lines in your report (items with item tracking and items with no item tracking).

If the item is not lot tracked then the lot number is blank and nothing special really needs to be done.


When you post the lines do you totalise it per item or do you totalise it per lot?

The item journal lines are created by lot so there is no totaling required. All that is necessary is to create a single item tracking line for each item journal line that has a lot number associated with it. While you are modifying report 790 you may want to consider modifying it to use the SIFT technology. Prior to dimensions (i.e. before 3.00) this report used SIFT to calculate the inventory by location, variant, etc. With dimensions it is now possible to specify the count to be performed by any of the available dimensions. Therefore it became necessary to examine each item ledger entry to determine which dimension values are associated with the entry and if any of those dimensions are part of the count. If you do not use dimensions to perform your count then you can modify this report to use SIFT to speed up the processing. I have been able to cut the processing time for this report by a factor of 7 to 10 (the exact improvement depends on the nature of the data). Good luck!

Hello Jack, Thank you for the detailed and clear information. I now know what direction to go. I will start with it in a few weeks and will let you know what the result is. Hope you can give me a hand if things don’t go as planned so please stay subscribed to this topic :slight_smile: Other pitfalls I have to deal with? Greetings, Henry