How do i change the Avg Price

[Gmessys]

I wanna update prices of some components but while editing I am not getting any option to change the price. Could u please help me out in this.

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[Drachenkaetzchen]

The average price is a calculated value, you cannot change that value directly. It is calculated by the sum of all stock additions divided by the stock count. Won't fix.

[Boldie]

I think, we should discuss this point, otherwise no correct calculation for a "shopping" system can be made,

For eg. think of the following use case:

• Initial Checkin, price per item is 1€, 10 Parts.
• Someone needs the parts, 10 pieces, he will pay 10€. Ok.
• New order will have an increased price, we order 10 pieces for 1.20€, check it in.
• Someone needs the parts, will checkout 10 pieces and now, he will think he has to pay only 1.10€
• This will result in a loss if one uses the average price for his calculations, so the avareg price is quite useless.

[Drachenkaetzchen]

I disagree here in reopening the bug. Average price is conceptual completely different to a price which you'll need for a full-fledged shop system.

If such a feature is required, please file a feature request.

[Boldie]

Ok, a question, which is the intended use of the average price, because I did not unterstand the use case for it. It cannot be used to determine the stocked value correctly nor can it be used to calculate a price which someone has to pay if he removes stock because he wants to use the part.

How did you calculate your payments for members if they use parts?

[Drachenkaetzchen]

Example:

We buy 1 part for 10 USD, and buy 5 parts for 8 USD each. The average value for each part is 8.33 USD for each part, and this is the suggested price members have to pay, and that's what displayed in the "average value" field.

[Boldie]

Ok, in this case I agree, but now everything is sold out and you buy a new charge (5 pieces) of them for 12 USD it will cost you 60 USD, but Partkeepr will now show a avg price of 10 USD, so you will sell them for 50 USD only and your Hackspace has to pay the difference.

I have tried this use vase and wondered about the result, because my expected result would be the average price over the stocked items only. Everytime I restock an item, I would expect, the new average price is using only the items in stock and not the complete history.

[Drachenkaetzchen]

That's correct, those prices are only an indication of how much one should pay (or rather donate). Because it hasn't to be precise, it's not very sophisticated.

Need to think about that, reopening.

[Drachenkaetzchen]

Seems that the solution would be to calculate the average since the last stock of zero, but I'm not really sure about that.

[twelve12pm]

The most correct solution for cost basis would be FIFO "First In, First Out." It's an accounting term in addition to being a technical term and means exactly what you think it means. It tells you the cost basis of your parts as they move in and out of inventory and takes into account the fact that prices change over time. It is assumed that you sell the "oldest" parts first.

[PGCJLEngineer]

Actually we do this regularly. I manually login to the database and enter the required MYSQL commands to update the tables. Use the standard Partkeepr web interface to find out the Partkeepr ID of the required part. To edit the part, I generally do somethingt like the following: Example uses part No.141

In a terminal...

1. LOGIN COMMAND:= mysql -u root -h 192.168.0.99 -p --i-am-a-dummy
   Your database server will have a different ip address or maybe even be 'localhost'
   NB: The I-am-a-dummy bit prevents you making very silly MYSQL errors - google it!

2. Enter your database root password

3)At the mysql prompt type 'USE partkeepr;'
mysql> USE partkeepr;
Then use the following commands
4) mysql> SELECT id,name,averagePrice FROM Part WHERE id= 141;
Check that you have the correct part and the price is the one you need to change.
5) mysql> UPDATE Part SET averagePrice=99.00 WHERE id=141;
Double check you have entered the information correctly as you can easily destroy your entire database if you make any kind of mistake! You have been warned. If in doubt research and test on anther copy of the database.

On pressing ENTER, the database will be updated. You cannot UNDO this type of change!

I have discovered that you can pretty much manually adjust everything in the Partkeepr database using this method. However altering tables to add extra fields generally causes more trouble than it is worth so don't try major architectural changes to the database.

Because I found the original source code difficult to follow I created a whole bunch of my own PHP web pages on the database server to provide custom access to functions via my own web-pages. Easily done!

Hope this helps anyone to fix problems
Chris

[Drachenkaetzchen]

Note that averagePrice is a cache field, which gets updated whenever stock is added or removed (at least in theory)

[baradhili]

My suggestion - average price should be removed.. its misleading, and inaccurate... basically - if you are running a shop system of any kind - it should be a shop system, not an inventory system

[christianlupus]

Just my 50 ct. about the calculation of the prices:

I think it would be better to save the total value represented by the stock instead of an average price. The information is the same but the logic and the numeric is better (no rounding errors).
You can then add new parts by updating the number and the total price. Removing will allow you to give exact prices but with a averaging effect. If your stock gets to zero, your total value drops to zero as well.
I think this is just a better way to describe the problem without introducing mathematical issues (singularities). Although, this implies a redesign of the database.

[PGCJLEngineer]

Partkeepr has not been updated for quite a while. Whilst an excellent application which remains functional, I think you will probably need to apply any fixes yourself for the near future. The website says the last release was April 28, 2018. Fortunately it is relatively easy to write additional PHP pages to query and update a MYSQL database. See my previous post on 9-2-2018.
Basically you can use standard MYSQL commands to interrogate or update the database. It is well designed and relatively easy to understand. Personally I would just write some PYTHON scripts to do specific functions. But as PARTKEEPR is used here where I work, I have written extra PHP web-pages to provide the required extra functions. I you are a newbie at MYSQL it is worth learning how to use it from a command line first before jumping into MYSQL programming from a script.

Hope this helps

Chris

[PGCJLEngineer]

And if you are concerned about average prices for different batches, just give them different part numbers or just ignore partkeepr prices and maintain a separate database for financial stuff and link via part_id references.

[baradhili]

@PGCJLEngineer Christian and myself are taking on the challenging task of cleaning up the issue pile, others are starting to work on restarting development as the main developer is unwell and can't support the code right now. It would be good if you could find a way to contribute the scripts you wrote?

[PGCJLEngineer]

Sorry but I cannot divulge scripts written in the course of my job. However I can tell you what I did (which isn't really difficult). Mostly I just wrote MYSQL queries to generate bills of materials and generate some financial information.

[Satnet]

My suggestion - average price should be removed.. its misleading, and inaccurate... basically - if you are running a shop system of any kind - it should be a shop system, not an inventory system

It is indeed very misleading. Specially when you have a product consisting of many parts and you wanna se the actual price of the product by getting a list.