Bulk update on cost price or buying price

Using my Dolibarr
1

Hi,

Is there a way bulk cost price update can be done? this would help achieve the right info about profitability for products when looking at margins info per product

Thanks for any response

2

I haven’t used this feature, but I think it is possible.

multi_action
multi_action1514×466 122 KB

first I select a section of services, then I select the action in the dropdown menu, I confirm and I get this 2. picture

adjustment_rate
adjustment_rate1517×599 133 KB

I haven’t tried this, but maybe it could work for you?

3

Thanks Jon,

Yes you are correct, however it works only on Selling or customer price, not cost price.
I wouldnt know if there could be same for cost price where it would be an option.

Not all business owners would increase cost price because selling price was increased, but some may do that knowing their cost has increased amd would want a clear reflection of their profitabiity

Thank you Jon

4

I see. I think that is possible using the API

product_prices
product_prices975×247 76.3 KB

5

Thank you Jon,

Would have tried this is i know how to use the API

I went to API explorer and stopped at the very first page.

Don’t know what the DOLAPIKEY is or where to get it, what user name and password to use, where to fix the above POST/products(id)/purchase_price

Furthermore, does it do bulk? meaning i can select a list of products and apply a increase or decrease in cost price by percentage and it affects the listed products(the cost price changes across the selected product items?)

6

You find your DOLAPIKEY here:

Home, Users & Groups, List of users.

Then you click your user, and in the right side of the screen you can find your API key. I do not use my “own” key. I have created multiple keys that I use for different tools that either feed data into my Dolibarr installation or extracts data out of my Dolibarr installation.

api_key_under_credentials
api_key_under_credentials988×267 24.7 KB

Yes and no the API does bulk. The P in API stands for Programming, so you either need to be a Programmer or find a Programmer that can Program a tool for you. What would needed to be done would be to Program something that asked your Dolibarr installation for either a list of products or just for all products, and then for each product it would use the API to tell your Dolibarr installation to adjust the cost price.

I just thought of a method that might work too, and I’ll go into details on that in a different comment.

7

Okay, so the other method I just thought of that might work for you.

Export, Modify, Import

  1. TAKE A BACKUP FIRST
  2. Export your products and/or services into a file
  3. Copy that file to a safe place. COPY not drag and drop, COPY.
  4. Open the exported file in your spreadsheet application, this might be an open source program like LibreOffice or it might be Excel from microsoft, or on a mac you might use Numbers.
  5. Modify and transform the document into a document that can be imported with the new information. This could be something like just finding the Cost price column and manually adjusting all cost prices. It could also be something like making a new column with the name New Cost price right next to the cost price column. Then you let the spreadsheet calculate a simple standard price adjustment, like * 1.04 for a 4% price increase. Save as CSV, because then the column contains real numbers, and not the formula for the calculation. Close the file and open the CSV. While watching the new price column, delete the old price column .- Do you still have the same numbers? Adjust the name of the new price column so it matches the old name.
  6. MAKE SURE YOU HAVE A BACKUP OF DOLIBARR.
  7. Import the file and hopefully watch it adjust your cost prices.
8

thank you once again Jon,

This two routes are very helpful, though the first seems best, but of course i am not a programmer and i dont even have one, hopefully Dolibarr team would do this fix in upcoming version(v19)

The excel thing is another good approach for the now, i have done this already, but an extra thing learnt from your feedback is saving it as CSV to keep the formula as numbers else after uploading that column would be zeros.(thanks for this info also)

Thanks i appreciate this contribution

1 Like
9

Hi,

This code was generated by a source i requested assistance from to solve the update cost price across products either by selecting an item or once across all product using percentage increase just as it has been done for selling

<?php
// Connect to the database
$db = new PDO("mysql:host=localhost;dbname=dolibarr", "username", "password");

// Define the update parameters
$mode = "percentage"; // or "fixed"
$value = 10; // the amount or percentage to update
$category = 1; // the category id to filter the products

// Prepare the SQL statement
$sql = "UPDATE products SET price = CASE
    WHEN '$mode' = 'fixed' THEN price + $value
    WHEN '$mode' = 'percentage' THEN price * (1 + $value / 100)
    ELSE price
    END
    WHERE category = $category";

// Execute the statement
$stmt = $db->prepare($sql);
$stmt->execute();

// Check the result
if ($stmt->rowCount() > 0) {
    echo "Updated " . $stmt->rowCount() . " products successfully.";
} else {
    echo "No products were updated.";
}
?>

Can anyone help with next steps? Dont know where to add this, neither know how to test it

Hope to get responses that can help us cross this hurdle.

thank you

10

Perhaps that source could submit a Pull Request?

11

Hi @Absmachin
We are all here to find answers!
This code looks like an AI generated generic code, if that is the case please remove it because it pollutes the search index and it certainly is not helpful.
Please have a look at the code that implements bulk update on selling price

If you need the feature i suggest you make a feature request or ask your source to implement it following above example.