Inventory Counting Journals for Physical Inventory

(0) Share

Report

Posted on by Community Member

I am curious how others are handling performing a complete physical inventory. We used multiple counting journals this year using the 'select' while creating the journal based on a location wildcard. We wanted multiple counting journals so at data entry time we could have multiple people working on the input instead of one large journal which would only allow one user to input counts. The problem we encountered is that if an item existed in the system in more than one location in the same warehouse it would only show up in one journal. So if jounrnal 1 had item ABC but item ABC also existed in another location that should have appeared in journal 2, item ABC would only be in journal 1. The only solution I know for next time at the moment is to create one huge counting journal.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Community Member on at

Like (0)

Report

RE: Inventory Counting Journals for Physical Inventory

We do inventory counting at location level using using .csv files.

the columns are Itemid , Qty , Item , InventDimId, Location (as str), PalletId .

We do it so often that we are even creating a form to handle the counting journal on a scanner gun

Julien

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Inventory Counting Journals for Physical Inventory

We commented out a checkt to inventItemLocation.CountingStarted so an item could be in multiple counting journals if it was in different locations. When doing a complete physical inventory we run our journals using the select based on wildcard of the locations. We do some cross-checks with SQL to make sure that a part/location combination is not found in more than one journal and that every part/location combination with inventory is in a journal. This way we can have data entry take place with multiple people working in different journals.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

RE: Inventory Counting Journals for Physical Inventory

Something I have suggested in another thread recently is to let multiple users work within the same tag counting journal. Most journals have the single user restriction for voucher draw reasons, but tag counting journals do not use vouchers. The other problem you face when multiple users key into the same journal is line numbers. Basically each user grabs the highest line number and adds one when they create their record, so the first to save gets it, and the rest get an error.

Both of these are simple one line code changes with no risk of any data issues. The work to accomplish this is trivial compared to all other solutions.

Beyond that, I generally recommend a small class to pre-populate the journal with a range or ranges (by running it multiple times for many individual ranges) of tag numbers in a default Missing status. This can streamline the keying process further, as can some simple "skip" or "hide" adjustments to fields you don't use.

I mostly work in AX 2012 and AX 2012 R2 these days, but if I can give myself access to older versions I'm willing to build some XPO's to demonstrate these and post them where they can be downloaded.

Was this reply helpful? Yes No
lispyj 173 on at

Like (0)

Report

RE: Inventory Counting Journals for Physical Inventory

In Excel, I used the Get External Data feature to import the Item Number, Item Name and Unit of Measure for all items in the item table. The connection is set to refresh automatically whenever a user opens the worksheet. On a different tab, I created columns for Tag #, ItemID, ItemName, Warehouse, Location and UnitID. I used a Vertical Lookup function in Excell to auto fill the Item Name and UnitID columns, once the ItemID is entered. The item name just provides a visual validation for the person doing data entry. This worksheet is a template, so anyone doing data entry, just makes a new sheet for themselves. The sheets are all merged together into one file (I use MS Access for this, but I am sure there are other ways), and the file is imported as tag journal lines. I establish the tag journal number in Dynamics, but don't generate any lines. The Import Definition is updated each time it is used to reference the correct journal number. From there, I generate a counting journal and finish out the post.

Was this reply helpful? Yes No
lally 8 on at

Like (0)

Report

RE: Inventory Counting Journals for Physical Inventory

Hi ,

I am also looking for the information .

Was this reply helpful? Yes No
sadiqmohammed 155 on at

Like (0)

Report

RE: Inventory Counting Journals for Physical Inventory

Hello LISPYJ -

could you please share the method in detail. we are looking for exact solution which you mentioned above. let me know how can you provide the complete steps of lookup data for the invent table

Was this reply helpful? Yes No
lispyj 173 on at

Like (0)

Report

Re: Re: Inventory Counting Journals for Physical Inventory

We enter tag counts into Excel, summarize and the use Dynamics Import/Export to import tag journals. We import one per warehouse. We had the same issues as the original poster. We needed multiple people to enter data and had multiple tags per warehouse for many items. I created a template with lookup data for the invent table that any number of users use to create their own count worksheet. It has worked well for us for the last three years.

Was this reply helpful? Yes No
diana g 689 on at

Like (0)

Report

Re: Inventory Counting Journals for Physical Inventory

Yeah unfortunately, the lowest level you can set stock count is the warehouse and not at the location level.

There's a couple of alternatives. The first is, as you say, create one big journal.

Or, if you're counting on paper then you can create the journal first just for the few locations you need. Print it. Then delete it and create the next one. Then you'll need to create them again when you're ready to enter the results. Not a great solution either.

It's probably easiest to create the huge journal for all locations, Export it to excel, work with the results in there and then import the counted values back into the journal. If you have AtlasXL then this would be very easy to do. If not, you can still use the standard tools but you'll have to setup the import.

Di Thompson

www.ThePPAXStore.com

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: Re: Inventory Counting Journals for Physical Inventory

Thanks.

I just tried it again to make sure it was working and here is the scenario:

Item A is in location RS-321-F1 and RS-330-D1. I create a counting journal specifying counting by 'Warehouse' and 'Location' . From the lines I do Create / OnHand. Using the select I enter location RS-321-F1 for the first journal. I set max lines to 999 and the counting by still displays site, warehouse, and location. I let it create the lines and item A is indeed in the counting lines. I repeat this process for a second journal using RS-330-D1 for the location. There is physical quantity in both locations by the way. I generate the lines and item A does not get created as a line in this second journal.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: Inventory Counting Journals for Physical Inventory

Hello Scott,
When you enter a new counting journal in DAX 2009 you should see the "Counting by" selection menu where you can check off both the warehouse and the location. This way you can specify both warehouse and location when entering your journals...
This way the item will show up in multiple journals for multiple locations....

Was this reply helpful? Yes No