Error in entering inventory - need to correct **purchase** history, not amount

Hello all!
I’ve recently begun going through the shop’s records, and came across a very interesting error. For one of our orders, someone entered the bottle cost as the millilitre cost.

Is there any way of going into the history and fixing the one specific day? I’ve included some photos as examples.

Thanks for reading!






This photo is the cost report for the entire year - clearly our reporting is a little skewed by our puchase of $30 million in (house) liquors.




This is the cost report for this month - still affecting our sales rate, even though the units purchased are long gone.

Prerty sure you cant change once work period closed you need to correct balances using the inventory screen

1 Like

@RickH is correct. The only hope to somewhat correct this is to alter the Physical Inventory to reflect what you actually have on-hand for that particular Unit, using End of Day Records.

1 Like

The actions you suggest taking would make this problem worse.

We have no problem with the amount of inventory, the amounts are all correct.
The problem I have is that the wrong cost was entered.

The person who did the records added, for example, 1000 mL (one bottle) of Myers. But instead of the cost being HK$0.148 per mL, the cost was HK$148 per mL. This made our “rum” cost HK$148,000 for a single bottle.

How about this… do a “reverse” purchase of the “bad” Unit …

If that doesn’t work, you will need to make edits directly to the DB.

The reverse buy didn’t quite help. Where do I reverse a transaction?

I got it back to positive, but Famous Grouse (my tester) is still looking like $250/mL.

I think I will need to crack int the database to fix the original error and the fix.

Ok, I tried all sorts of “reversal” methods using negative numbers, backwards Inventory Tx/Docs, editing Inventory Transactions within the Workperiod, etc. None of that appeared to work.

What does appear to work for me is this, so give it a try:

  • Close the Workperiod
  • go to Manage > Inventory > End of Day Records > [Last record]
  • find the item and set the Current Inventory for the “bad” item to zero (0), and cick Save
  • Open the Workperiod
  • purchase the item at the correct Unit and Price for what you currently truly have on-hand

I know we should be able to fix this by editing the DB, but I am reluctant to investigate this or show you how it is done if we can find another way to fix the problem. Let me know how it goes…

1 Like

Again, editing/resetting the current inventory will not fix the records, which is what I need to do.
This problem entry affects all following cost reports for the affected items. It would fix the issue from today forward, but the data for the six months in between your still be corrupted.
I will need to crack into the database, it looks like.
Is this something I can do directly, or will I need an auxiliary program?

@QMcKay Amanda is our client. I wasn’t sure the best way about solving this so recommended she post on the forum. If we change on the database directly, do we just need to alter the original erroneous transaction or have to go through many consumptions since then to adjust? Or do you think there is any other way to do it without changing database directly?

Every example of someone trying to change this almost always ended up in corrupted data after. Is it required to fix past data or would starting with fresh Cost etc from this point forward be ok? I can tell you how you can reset your costs, etc so from this point forward it would be correct.

EDIT: Nevermind I just read her comment through. So you need past data altered. We need to understand a few things before we attempt this and maybe it can put it in better light. Most of the metrics your wanting are not stored values they are generated. This being said its not going to be easy to fix this. If we alter past records manually we have to be 100% sure we did not cause any other relational damage to the rest of the database.

I will do some tests and see what I can do but its very unfortunate to be in a situation like this however it might be best to manually compensate for the past data and start fresh.

@markjw PS: If possible you could send me a backup of her database I could work with her real numbers and run some tests see if we can fix your actual problem.

2 Likes

@markjw UPDATE: I did some testing and we can edit the database manually to fix this but it will require a lot of work and you need to be careful because your going to need to calculate some things manually. I can share how to do this but I would prefer it be private because it is NOT something we should be doing it will mess up peoples database if they attempt this and get it wrong.

Mark if you want to PM me I can give you the details.

PS: I still think this is not the best approach. It would be better to just write off your past data and start fresh with accurate data. Meaning you would need to manually calculate your costs etc. PS We will have to do some manual cost calculations to fix the database anyway.

2 Likes

Invite me to the PM if you need any help.

2 Likes

I have the same problem, could you help me about that please?

Please start a new thread with details of your issue. Making us dig through a long post to understand your issue is not very nice. Resurrecting old threads is not a good practice too it confuses people.

Sorry about that.

Past month I wrongly purchased some products with wrong amount and it affected cost of products. So, when I checking profit & loss report for past month, some products shows result with negative profit. Editing amount from inventory transactions not possible and I would like to find a way to edit past inventory transaction amounts.

EDIT: I had some research on forum and found better solution. Now I am trying to use data export tool to edit wrong costs on excel.