I need some serious and quick help! We completed our year-end inventory by inputting items into a blank inventory file. During the process, we ran an additional Physical Inventory, which zeroed out all previous expected numbers using the Physical Inventory tool and calculating the full stock at 0 inventory.
We then completed the physical inventory and closed out each inventory. It was then that I noticed that certain items were still showing an expected value that was greater than 0, specifically items that had been scanned prior to running the 0 inventory.
Now, instead of using the counted values in my inventory, the system has inputted all the Delta values, basically undoing about 15 hours of inventory taking! I want to know if there's any way to, I don't know, force the system to use the counted numbers.
I understand that I've caused a glitch up by not running the 0 inventory before we started everything, but at the same time, shouldn't the system use the Counted numbers by default? Why would it use the Delta amount? And if I don't zero out inventory in the future, will it continue to use the Delta amount?
I would be greatly appreciative of some help here! Thanks!
*This post is locked for comments
I found that migrating up to SQLExpress12 uses the newer SQL_Latin1... over the older Latin1_General... so anytime a temporary table is built under the MASTER database, it's using the older collation. I had to update the collation of the master database using the tip here. Essentially, you have to stop the SQL server instance, run a command line collation upgrade command, and restart the instance. I tested it on my duplicate VM of the production server, and it solved the error.
Unlike other tables, the master database needs some gentler handling to shift it's collation setting.
No problem, glad I could help. Let me know if you run into anything screwy, and I'll see what I can do.
You are a lifesaver!! Seriously!!! That completely fixed everything and we're back up and running. I cannot even tell you how relieved and grateful I am for your assistance. Thank you SO much for taking the time to work with me on finding a solution to my problem. I promise I will not make the same mistakes again!
You are the best!
Oh, replace [the reference code] with the code from the PI you want to commit. No brackets, but leave the single quotes.
Ok, well, I was trying to avoid touching the database itself, but it looks like this is turning into more trouble than its worth. Note that this is simply going to overwrite the current quantities with those counted during the inventory. Any movement since the inventory will be in sales history, but will not be accounted for in the new quantities, so anything that has sold or otherwise moved since it was counted will probably need to be recounted. Also, if you are in a headquarters environment, the change will not automatically be uploaded, so they will have to request a full item count and perform a reconciliation at HQ. Backup the database before going any further, then write down the reference number of the PI's that you want to commit. Open up Store Operation administrator, connect to your database, open a new query, and run the following command:
SELECT item.itemlookupcode, item.description, item.quantity AS oldquantity, physicalinventoryentry.quantitycounted AS newquantity FROM physicalinventoryentry JOIN item ON physicalinventoryentry.itemid = item.id JOIN physicalinventory ON physicalinventoryentry.physicalinventoryid = physicalinventory.id WHERE physicalinventory.code = '[the reference code]'
This will list all of the items with their current quantities and the new quantities that they will be replaced with. Review this and make sure that everything is accurate and as it should be. There is no easy undo for this short of restoring a backup, so be careful and be aware.
Once you are satisfied, create a second new query window and run the following command:
UPDATE item SET item.quantity = physicalinventoryentry.quantitycounted FROM physicalinventoryentry JOIN item ON physicalinventoryentry.itemid = item.id JOIN physicalinventory ON physicalinventoryentry.physicalinventoryid = physicalinventory.id WHERE physicalinventory.code = '[the reference code]'
This will pull the trigger on the operation, replacing current quantities with those in the inventory.
Now, I have just discovered that rolling the database back to where it currently is (ie. the day before we closed the inventory files) actually removes everything uploaded from the handheld. So.... what can I do if the inventory files are already closed?
On the Movement History report, it shows one of the items as having a physical inventory adjustment of -4 copies on January 6th, 2014.
I have gone into the PI and changed the count date to January 7th and then Calculated again. It shows the count date as 1/7/14, but is still showing the same numbers in the Adjust column.
Oh, if you right click the date in the item movement history report and copy the value, then paste it into a text document, it should show you the time that isn't being displayed.
Yep. Unfortunately, the reports like to truncate the dates so that you can't see the time, but there should be a physical inventory adjustment at some point after the current count date you have entered. If you can change your count day/time to a time after this, then the quantities should commit correctly.
It's open! Should I just pick an item and run a Movement History report?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,302 Most Valuable Professional
nmaenpaa 101,156