My problem is complex to me but maybe someone can help me come up with an easy automated idea.
Currently we have a way to export usage of products and UPCs from our POS system (excel file) at a little retail store.
Using this file daily, I would like to do something similar to power queries which is consolidate the same products (UPCs) and add up the quantities sold. In this file thats exported, i should add that the stock size of the item also are retained with the UPCs. That will help understand my next ask.
I can easily do this. However i need to take this a lot further. Let’s say we are selling items that come in a large stock sizes, product A comes in a box of 50 and Product B comes in a box of 100. If we sell 30 of product A and 120 of product B, i want to have a reorder written for 1 box of product B. And retain the partials for the next run. Therefore, we would have a table that would be dynamic in a way for this virtual inventory. The table after the order is written and export would show Product A having not met the threshold with 30 sold quantity and Product B showing 20 sold (since we reordered the other 100).
The next day - i export the same file and drop it in - the virtual quantities are added in with the new ones. So Product A we sold 120 and Product B we sold 80. It’s ran again, the order will give you product A (50 count) ordering 3 boxes (30 + 120 =150/50 is 3). Product B will have 1 box ordered. (20+80=100/100 is 1). So the new virtual would show both products as 0.
I dont know if it’s possible. I know doing the data work and replicating is possible from excel and power queries. But the retention of the virtual inventory and order writing might not be. But I'm hoping someone had a great idea!! SOS. Please help!
