Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Help! Physical Invento...
Microsoft Dynamics RMS (Archived)

Help! Physical Inventory updating numbers with Delta value

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Help! Physical Inventory updating numbers with Delta value

    No problem, glad I could help. Let me know if you run into anything screwy, and I'll see what I can do.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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!

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Help! Physical Inventory updating numbers with Delta value

    Oh, replace [the reference code] with the code from the PI you want to commit. No brackets, but leave the single quotes.

  • Verified answer
    Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Help! Physical Inventory updating numbers with Delta value

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Help! Physical Inventory updating numbers with Delta value

    It's open! Should I just pick an item and run a Movement History report?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans