web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
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

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 have the same question (0)
  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at
    RE: Help! Physical Inventory updating numbers with Delta value

    Hey Jav,

    When an item is entered into the physical inventory, it has a count date and time attached to it (you can right click the headers and go to show/hide columns to display this value). When you click calculate, RMS takes the entered quantity and applies any item movement that has occurred since the attached date in order to determine the new quantity. I suspect what happened is that when you performed a second physical inventory to zero out your values, RMS considered it to be item movement, and so it is applying the decrease in quantity to the entered values. I Luckily, it sounds like you performed the whole inventory in one swoop, so it should be easy enough to roll back these changes. I think the best way to do this is probably going to be a sql update statement in Store Ops administrator. Let me think about what the most thorough and comprehensive method would be, and I'll update you in a second.

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

    Actually, scratch my first suggestions. Lets avoid messing with the database if possible. I have a better idea. This is going to be a little involved, but I think it should fix the issue.

    Open Store Ops, go to Physical Inventory, and find the correct inventory that you committed. Click "Print". On the next screen, add a filter of "Qty Adjusted Not Equal 0". This should bring up a list of all items that had item movement from the zeroing inventory applied to them. Drag the column "Item" into the first column, then "Counted to date" into the second column. Right click on the headers and select "Show/Hide Columns".  Uncheck everything except for Item, Counted to Date, and count date. You should now have a list of upcs, counted quantities, and dates, in that order. At the top, click the Export, then select File, and Comma Separated, and click OK. Choose a place to save the file. Create a new, blank physical inventory, and click "Import". Find the file that you just created, and click next. Select comma for field delimiter, enter 2 for start import at line, and enter 1,2, and 3 next to itemlookupcode, Quantity, and Count Date, respectively. Click Next.  Select "Update existing items and add new items". Click Next. It should display a bunch of items to import. Click import. Right click on the headers, select "Show/Hide columns", and place a check next to count date and hit OK. Click the new "Set Dates" button, and enter a time immediately AFTER you committed the CORRECT inventory (not the one you used to zero items out). Click OK. Hit Calculate, and commit the counts as you would with a normal inventory. Your counts should now all be corrected.

    So, like I said, a little involved, but let me know if it works out.

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

    First, let me thank you for responding with an offer of help. I am in dire straits here!

    I have actually tried some things since I posted the above:

    -I rolled back the RMS program using Monday's backup, in order to re-open the inventory files (we had 4 in total)

    -on the Physical Inventory screen, I right-clicked to allow me to Show/Hide Columns. I selected Count Date, which gave me the option to change the count date. I changed it to after the zero inventory change. 3 of the 4 inventories now work and update with the counted amount. There were a handful of items that still had an expected quantity, but they updated the stock with the counted amount anyway, not the Delta.

    -unfortunately, the final PI, containing over 7000 SKUs will not oblige me in this manner. It is still using the Delta as the new stock number.

    -I have also tried copying that PI and committing it, with all 0s for the count. The Calculation on that one showed nothing in the expected qty column. However, nothing changed with the actual count after this adjustment

    One added potential complication is that we uploaded some stock into this file (and the others) from a handheld scanner. I have no way to recover that data once it has been uploaded, unfortunately.

    I look forward to your response! Thanks!

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

    Hm. Ok, well, that's progress. Let's try this. On the final PI, open it, calculate it, and right click the headers and click Show/Hide columns. Place checks next to Sold, Returned, Xfer In, Xfer Out, To Offline, From Offline, and Adjusted, and click OK. This should represent all of the Item Movement that RMS is using to calculate current quantities. Find where the values that are not 0 are, because these are what are causing RMS to update with quantities differing from those counted. Let me know what you find here, and we should be able to address the problem based on this.

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

    So, it got about halfway through the import and spat out this error:

    "Error #-2147217900

    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation

    (Source: Microsoft OLE DB Provider for SQL Server)

    (SQL State: 42000)

    (NativeError: 468)

    No Help file available

    UPDATE[##ST_Validation_1]

    SET Import Status = 3

    , ItemID = Item.ID

    FROM Item INNER JOIN [##ST_Validation_1] ON Item.ItemLookupCode = [##ST_Validation_1] .ItemLookupCode"

    This was followed by Runtime error '5', and the whole SO Manager shutting down.

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

    The only column with anything other than 0 is the Adjust column which has a variety of numbers. So, for example, we counted an item at 1 copy, the Adjust column says -4, the Expected Qty says 4, and the Delta is -3.

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

    Ok, so it is still detecting some movement from a physical inventory being committed. Is the PI still open at this point, or has it been committed? If it's open, we can just find a date after the movement by going through the item movement history and enter that as the count date. If it's closed, I think I'm ready to just say screw it and brute force it with a sql statement.

  • Community Member Profile Picture
    Community Member 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?

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 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.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 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.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans